![]() | ![]() | ![]() |
Beginning with SAS® Financial Management release 4.4, a change was made to how the results of reporting formulas are handled. Specifically, the results of reporting formulas do not automatically aggregate, or roll-up, in the dimension hierarchies.
For example, assume you have the following hierarchical structure:
Parent A
Child B
Child C
such that the value of Parent A is calculated as the sum of its' Child members B and C. Parent A has no children other than A and B.
If data loaded into SAS Financial Management results in the following values:
Child B 100
Child C 200
then the value of Parent A would be automatically calculated by SAS Financial Management as the roll-up, or aggregation, of its' children, and the total value that would be calculated for Parent A would be 300, as shown below:
Parent A 300
Child B 100
Child C 200
However, if the values for children B and C are calculated by reporting formulas, those results would not automatically roll-up, or aggregate, at the parent member A, as shown below:
Parent A 0
Child B 100
Child C 200
In the above example, we can assume that the value of 100 for Child B was calculated by a reporting formula that specifies (for example)
Revenue for B = Units Sold for B * Price for BIf 10 units of B were sold at a price of $10 each, the total revenue for B would be 100. We will assume a similar calculation was done to determine the Revenue for C, resulting in a total of $200.
If we want to see the totals for B and C roll up to the parent member A, we will need to add a formula for the member A to calculate that total.
But, we need to be careful with our choice of formula type, as different formula types have different orders of execution and might generate different results (please refer to SAS Note 31200 for more information on order of execution and SAS Financial Management formula types). More specifically, since modeling and driver formulas are evaluated prior to reporting formulas, a modeling or driving formula should not be defined to contain the results (output) of a reporting formula.
One way to define the formula is on the Analysis member. For example, for Analysis member Budget, add a reporting formula that is defined as:
sum(["ACCOUNT"="B"],["ACCOUNT"="C"])(assuming, in this case, that A, B and C are all members of the account dimension).
To be sure that this does not populate undesired members of the hierarchy, add scoping so that the formula is only evaluated for the Parent member A, as follows:
ACCOUNT: A - MemberOnce this reporting formula is added to the properties of member A, you will see the following results when you view budget information for members A, B and C:
Please note that you can NOT add the formula
sum(["ACCOUNT"="B"],["ACCOUNT"="C"])directly to the properties of Account A in the Account dimension. This formula will need to be added to members of other dimensions and then scoped so that it is only evaluated for Account dimension member A (as in the above example).
Also, please note that results of modeling and driver formulas do aggregate, or roll up, in the hierarchy.
For more information on using formulas in SAS Financial Management, please refer to the SAS Financial Management Formula Guide.
For additional information on scoping formulas in SAS Financial Management, please refer to SAS Note 21090.
For information on SAS Financial Management model and query guidelines, please refer to SAS Note 21089.
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Financial Management | Microsoft Windows 2000 Advanced Server | 4.4 | 9.1 TS1M3 SP4 | ||
| Microsoft Windows 2000 Datacenter Server | 4.4 | 9.1 TS1M3 SP4 | ||||
| Microsoft Windows 2000 Server | 4.4 | 9.1 TS1M3 SP4 | ||||
| Microsoft Windows 2000 Professional | 4.4 | 9.1 TS1M3 SP4 | ||||
| Microsoft Windows Server 2003 Datacenter Edition | 4.4 | 9.1 TS1M3 SP4 | ||||
| Microsoft Windows Server 2003 Enterprise Edition | 4.4 | 9.1 TS1M3 SP4 | ||||
| Microsoft Windows Server 2003 Standard Edition | 4.4 | 9.1 TS1M3 SP4 | ||||
| Microsoft Windows XP Professional | 4.4 | 9.1 TS1M3 SP4 | ||||
| Windows Vista | 4.4 | 9.1 TS1M3 SP4 | ||||
| 64-bit Enabled AIX | 4.4 | 9.1 TS1M3 SP4 | ||||
| 64-bit Enabled Solaris | 4.4 | 9.1 TS1M3 SP4 | ||||
| Type: | Usage Note |
| Priority: |
| Date Modified: | 2010-09-12 00:48:41 |
| Date Created: | 2010-08-18 15:04:46 |


