Settings & Other Business Objects

The following Settings and other miscellaneous business objects are commonly used in queries. Review the descriptions to see how each can be joined with other business objects and what properties they include.

CsGeneralLookupTables

Contains all the lookup data created in iMIS General Lookup tables. There are likely multiple rows per lookup table in this BO, one row for each lookup option.

The code field may contain duplicate values so ensure that you filter on table_name to get the right lookup value.

Common joins

Some common joins are any field that uses a General Lookup table. The table will generally store the code value of the lookup. Use CsGeneralLookupTables.Code to link to your user defined field.

Properties

• Code
• Description
• SubstituteValue
• TableName
• Ncode

DateDimension

The Date Dimension business object contains a row for every day from 1/1/2000 to 12/31/2050 in order to more easily create reports and queries that require dates. Use this business object to report on fiscal year information. Use TheDate property to filter the report on specific dates. For an example, see Finding invoice amounts for each month.

📘

Note

The DateDimension business object contains several fiscal properties (e.g., FiscalMonth, FiscalYear, FiscalYearBegin). These properties are determined by the fiscal month defined in the First month of fiscal year (number) setting (Settings > Finance > General).

Common joins

• DateDimension.TheDate = InvocieSummary.InvoiceDate
• DateDimension.TheDate = NetContactData.JoinDate

Properties

• DayOfCalenderYear
• FiscalYearEnd
• DayOfMonth
• FiscalYearMonth
• DayOfWeek
• LastDayOfMonth
• FiscalMonth
• NextWorkingDay
• FiscalYear
• TheDate
• FiscalYearBegin

MonthRef

Contains a row for every calendar month in order to more easily create reports and queries that require dates.

Common joins

• EngagementScoreMonthlyData.Score Month = MonthRef.Month Number
• CsDonationsDetail.Fiscal Month = MonthRef.Month Number

Properties

• MonthName
• MonthNameShort
• MonthNameUpper
• MonthNameUpperShort
• MonthNumber

YearRef

Contains a row for every year from 2000 to 2050 in order to more easily create reports and queries that require dates.

Common joins

• YearRef.Year = EngagementScoreYearlyData.ScoreYear
• YearRef.Year = CsDonations.Fiscal Year

Properties

• FiscalYearBegin
• FiscalYearEnd
• Year
• YearChar
• YearShort
• YearShortChar