Providing software solutions since 1976

• Log in
• Create profile
• Welcome
  [Log out]
• Edit profile

• Print  |
• Feedback  |
• Share

  • E-mail

  
  
  

  E-mail This Page

  Your Name:*
  Your E-mail:*
  Recipient E-mail:*
  Send a copy to me
  Personal Message:
  
  

  * indicates a required field

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 B

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"])

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 - Member

Please note that you can NOT add the formula

sum(["ACCOUNT"="B"],["ACCOUNT"="C"])

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.

Operating System and Release Information