Consider an outline with the following dimensions:
Company, containing CompanyA, CompanyB, and other children.
Department, containing numbered departments such as 101, 102, 103.
Account, in which Account 5740 is a rent expense account and SQFT is a statistical account used to record square footage for each department.
Scenario, in which the Actual member is where data is posted, and the Allocation member is where allocations and custom calculations are stored. The Scenario member is a parent that aggregates the child members Actual and Allocation.
Year, a time dimension organized by months and quarters.
Geography, a dimension organized by states and cities.
AmountType, in which Debit is the target, and Credit is the offset.
Project, a dimension containing projects such as Proj1, Proj2.
The POV is an MDX set expression indicating where the custom calculation should be executed. It is specified as follows:
CrossJoin( { ( [Company], [101], [Jan], [Scenario] ) }, Descendants( Geography, Geography.Levels(0)) )
The DebitMember is an MDX member expression indicating a debit member to which positive result values should be written. It is specified as [BeginningBalance_Debit].
The CreditMember is an MDX member expression indicating a credit member to which negative and offsetting result values should be written. It is specified as [BeginningBalance_Credit].
Note: | The offset is written to the debit member in the case that the sum of all result values is negative. |
The offset is an MDX tuple expression indicating where offsetting entries should be made. It is specified as ([Account_NA], [Project_NA]).
The offset expression is combined with Target and POV to determine the location where offsetting entries are made. If dimensions overlap, the order for resolving the offset location is the offset, the target, and the POV, in that order.
The target is an MDX tuple expression indicating where to write the results of the custom calculation. It is specified as(Allocation).
The target expression is combined with POV, and the left side of each line in the custom calculation script, to determine the location where results are written. If dimensions overlap, the order for resolving the target location is the left side of the equations, the target, and the POV, in that order. In this example, results are written to the Allocation member, because the target overrides the Scenario member specified in the POV.
The following is an example of a custom calculation script:
(AccountA,Proj1) := 100; ([AccountB], [Proj1]) := ([AccountB], [Proj1]) * 1.1; (AccountC,Proj1) := ((AccountB,Proj1,2007) + (AccountB, Proj1)) / 2; (AccountA,Proj2) := ((AccountD,Proj1) + (AccountB,Proj2)) / 2;
For each combination in the POV,
The calculation script is executed in the context of the current POV combination.
One offset value is written to the target location.
Note: | Each formula (line in the calculation script) is executed simultaneously, rather than sequentially. Therefore, you cannot use the result of one formula in a subsequent formula. |
To define the source region, examine the custom calculation script and determine which members are referenced on the right sides of equations. At a minimum, the source region should include all members from the right sides of the assignment statements in the custom calculation script.
Define the source region as a single MDX set. If the members on the right sides of the equations are from more than one dimension, you can use CrossJoin to create the set from two sets. CrossJoin only accepts two sets, so you may have to use nested CrossJoins.
The source region for the above custom calculation script is:
Crossjoin( {[AccountB], [AccountD]}, Crossjoin( {[Proj1], [Proj2]}, {[2007]} ) )
It is not necessary to include any members in the source region that are not assigned in the script. For example, if you added to the source region an [AccountC], which is not used in the script, then it would be ignored, and could cause a slight detriment to performance.
It is not necessary to account for numbers in the source region. For example, the following assignment in a custom calculation script requires nothing to be added to source region: ([Bud Var]):=10.