The Account dimension
You use Model Manager to manage the Account dimension, including adding members and editing the member properties that define the basic characteristics of the account. In addition, an account also has a calculation definition that determines its behavior.
These properties are largely self-explanatory except for Time Conversion, which controls the aggregation behavior of accounts across the Time dimension.
Account type
The Account type property determines whether
The values for Account type are: Expense, Revenue, Asset, Liability/Equity, and Statistical. Defining an account as one of the types does not change the value stored in the account.
Note: The signs of the account values are only affected during data import; hence the account type must be defined before importing data.
To specify the account type for a member, in the Member Properties section, select from the drop-down list.
Tip: By default, all new accounts are set up as expense accounts. To change the default, select Options.
Debit/Credit
The Debit/Credit property is associated with the Account type property.
- By default, if Account type is Expense or Asset, this property is set to Debit.
- If Account type is Revenue or Liability/Equity, Debit/Credit is set to Credit.
- Choosing Statistical for Account type sets Debit/Credit to Not Set.
To override these default settings, select the Debit/Credit property after setting the Account type.
Note: Correctly setting the Debit/Credit property is important for adjustments.
Time conversion
The Time conversion property determines how each account aggregates data across different periodicities, such as from monthly values to quarterly values. (Aggregation across other dimensions is not affected by this setting.)
Tip: By default, the time conversion method is assigned automatically based on the Account Type selected. To change the default, select Options.
Time conversion calculations are implemented as MDX directly in the cube and execute each time the cube is queried.
The default time conversion method of an account type can be overridden by the user.
Account type | Default debit/credit | Default time conversion |
---|---|---|
Revenue | Credit | Sum |
Expense | Debit | Sum |
Asset | Debit | Last (including empty) |
Liability/Equity | Credit | Last (including empty) |
Statistical | Not Set | Sum |
Time conversion methods
Time conversion uses one of the following methods:
- Sum: Adds the leaf/detail member values together to derive the values for higher levels. Sum is the most commonly used time conversion method and is the default.
- Last (excluding empty): Uses the last value in the last non-empty leaf member to derive the values for higher levels.
- Last (including empty): Uses the value in the last leaf member to derive the values for higher levels.
- First: Uses the first value in the leaf member to derive the values for higher levels.
-
Average (excluding empty): Averages the values of the leaf member to derive the parent member value. Empty cells are excluded.
Note: Empty cells are not the same as cells with the value of zero.
- Average (including empty): Averages the values of the leaf member to derive the parent member value. Empty cells are included and given the value of zero.
- Formula: Calculates the values for higher levels, using the specified formula.
Examples of Time conversion methods
The following table shows examples of how the various Time Conversion Methods calculate results:
Account |
Jan |
Feb |
Mar |
Qtr 1 |
Time conversion method |
Gross Sales |
100 |
250 |
375 |
725 |
Sum |
Headcount A |
12 |
16 |
- |
- |
Last (including empty) |
Headcount B |
13 |
22 |
- |
22 |
Last (excluding empty) |
Opening Balance |
100 |
110 |
130 |
100 |
First |
Product A Sales |
— |
100 |
250 |
175 |
Average (excluding empty) |
Product B Sales |
— |
225 |
150 |
125 |
Average (including empty) |
Gross Sales per Employee |
8.33 |
15.62 |
18.75 |
36.25 |
Formula (Gross Sales/Headcount) |
Numeric format
Each account has a default numeric format defined. This format is applied during data entry and is also the default format for reporting, though it can be overridden. Numeric formats are defined in the same way as in a spreadsheet.
Example: #,##0.0 applied to the value 12345.67 causes it to appear as 12,345.7.
The numeric formats available are as follows:
Format | 1234.5678 | Description |
---|---|---|
#,### | 1,235 | Displays significant digits, with a thousands separator. A zero value appears as blank. |
#,###.00 | 1,234.57 | Displays significant digits, with a thousands separator and always includes two decimal places (even if the value is without decimals). |
#,##0 | 1,235 | Displays significant digits, rounded to the nearest thousand. A zero value appears as 0. |
#,###, | 1 | Displays significant digits, rounded to the nearest thousand. The second comma causes the number to scale by a thousand; more examples: 91234.5678 is formatted as 91; 901234.5678 is formatted as 901. |
0 | 1235 | Displays significant digits, rounded, and without a thousands separator. |
0% | 123457% | Displays percentage, rounded to full percentage. |
0.00 | 1234.57 | Displays significant digits, without a thousands separator, and always includes two decimal places. |
0.00% | 123456.76% | Displays percentage, rounded to two decimals. |
General | 1234.5678 | No numeric formatting |
Tip: To change the default numeric format, select Options.
Currency conversion method
This property is present if the model contains a dimension of type Currency. The options are Current (the default) and Historical.