Thursday, April 19, 2012

How to do variance reporting in ESSBASE

Variance reporting property determines how ESSBASE calculates the difference between Actual and Budget data members,
The dimension should be tagged as Account then only variance reporting property becomes visible.
The member formula to calculate the variance reporting between Actual and Budget should consists either of @VAR or @VARPER
There are two types of variances
  •        Expense Reporting
  •        Non Expense Reporting.

The below example shows how to create Expense Reporting.
As I mentioned above this property is visible for members in Account dimension only, by default it is false make it true, then the member will be tagged as Expense Reporting and create a member formula for a member in Scenario dimension  as @VAR("Actual","Budget") then the result will be as shown below.
run the default calc script or calc all script , the result will as be as shown below.
For Spent, variance reporting is Expense so the margin is different of Budget and Actual.
For Receive, variance reporting is Non Expense so the margin is different of Actual and Budget.