Customizing the formulas

The Actual vs Budget and Periodic/YTD Actual vs Budget templates rely on calculated members. The calculations are defined to show variances both with and without the impact of translation exchange rates. The Fx Var, Net Var and Net Var% calculations are cross-dimensional calculations referencing both the Version and Exchange Rate dimensions.

These calculations have been added to the Version dimension. All calculations are associated with the predefined Business Edition cube (relational schema) name and the Alias which is BusEd.

The calculation syntax uses unique names. You can find an element's unique name by double-clicking it in Edit Calculation. Or, drag the element from List Designer to a cell within the definition view of the report. Fx Var is a currency translation variance that shows the impact of actual and budget currency exchange rates on actual results.

/* FxVar = financial variance between Actual @ Actual Rate and Actual @ Budget Rate */ FinVar ( ( [7.0.1] , [8.0.1] ) , ( [7.0.1] , [8.0.2] ) )

Net Var is a net variance that removes the impact of translation exchange rates and shows the variance between actual results and budget amounts.

/* Net Var = financial variance between Actual version @ Budget Rate and Budget version @ Budget Rate */ FinVar ( ( [7.0.1] , [8.0.2] ) , ( [7.0.2] , [8.0.2] ) )

Net Var% is a net variance percentage that shows the percentage variance between actual results and budget amounts.

/* Net Var% = financial variance percentage between Actual version @ Budget Rate and Budget version @ Budget Rate */ FinVarPercent ( ( [7.0.1] , [8.0.2] ) , ( [7.0.2] , [8.0.2] ) )

The currency translation variance, Fx Var, and the net variance, Net Var, added together equals the difference between Actual @ Actual Rate and Budget @ Budget Rate.

The Periodic/YTD report uses formulas to calculate the variance between periods. These formulas have been added to the Activity 1 dimension to support changing member values in all other dimension. These calculated members are also associated with the Business Edition cube (relational schema) name.

PeriodPreviousYear determines the prior period based on the current selected member of the period dimension.

/* Previous Period using No Member and month list*/ ( [11.200.0] , [1*2].CurrentMember.Lag(12) )

PeriodPreviousYearVariance calculates the financial variance between the current selected period and the same period in the prior year. It uses the results of the PeriodPreviousYear formula to determine the prior period.

/* Variance of Current Period vs Previous Year Period using No Member and month list*/ FinVar ( ( [11.200.0] , [1*2].CurrentMember), ( [11.200.0] , [1*2].CurrentMember.lag(12)) )

PeriodPreviousYearVariance% calculates the financial variance percent between the current selected period and the same period in the prior year. It uses the results of the PeriodPreviousYear formula to determine the prior period.

/* Variance in percent of Current Period vs Previous Year Period using No Member and month list*/ FinVarPercent ( ( [11.200.0] , [1*2].CurrentMember), ( [11.200.0] , [1*2].CurrentMember.lag(12)) )