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.
In This Article
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
| Property |
|---|
| 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
Some common joins include:
| Business Object | Relation |
|---|---|
| InvoiceSummary | DateDimension.TheDate = InvocieSummary.InvoiceDate |
| NetContactData | DateDimension.TheDate = NetContactData.JoinDate |
Properties
| Property | |
|---|---|
| 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
Some common joins include:
| Business Object | Relation |
|---|---|
| EngagementScoreMonthlyData | EngagementScoreMonthlyData.Score Month = MonthRef.Month Number |
| CsDonationsDetail | CsDonationsDetail.Fiscal Month = MonthRef.Month Number |
Properties
| Property |
|---|
| 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
Some common joins include:
| Business Object | Relation |
|---|---|
| EngagementScoreYearlyData | YearRef.Year = EngagementScoreYearlyData.ScoreYear |
| CsDonations | YearRef.Year = CsDonations.Fiscal Year |
Properties
| Property |
|---|
| FiscalYearBegin |
| FiscalYearEnd |
| Year |
| YearChar |
| YearShort |
| YearShortChar |