ASO Procedural Calcs Part 1

I've shown before that using ASO Allocations is generally favorable to using ASO Procedural Calcs. But when are you forced into using a Procedural Calc?

Writing a BSO calc script to subtract Forecasted Cost of Goods Sold from Actual Sales is relatively simple.

• "Margin" = "Actual"->"Sales" - "Forecast"->"COGS";

But this is where we hit a snag with ASO Allocations.

Within an ASO Allocation statement we can use the Amount and AmountContext fields. We can use those to set the source of the data I'm going to allocated. The Amount field allows us to do things like this:

• Amount "([Sales] - [COGS])"

as well as some simple arithmetic. We can use the AmountContext field to fine tune Amount field. We can add something like this:

• AmountContext "([Actual])"

or like this:

• AmountContext "([Actual],[Jan])"

What we cannot do is cross dimensions within the Amount field like this:

• Amount "([Sales],[Actual] - [COGS],[Forecast])"

>>ERROR - 1200679 - The amount expression is invalid. Only arithmetic operations and members from a single dimension are allowed.

This is the point at which we need to switch over to using a Procedural Calc. Here's an example:

1:  Execute Calculation on Database Sample.Basic With  
2:  LOCAL SCRIPT FILE "simple_calc.csc"  
3:  POV "CrossJoin({[Jan]:[Dec]},  
4:  CrossJoin({[Forecast]},  
5:  CrossJoin({Descendants([Total Markets],Levels([Market],0))},  
6:  {Descendants([Total Products],Levels([Product],0))})))"  
7:  SOURCEREGION "CrossJoin({[System Margin],[Sales],[COGS]},  
8:  CrossJoin({[Jan]:[Dec]}, {[Actual],[Forecast]}))";  
9:    
10:  /* Contents of simple.calc.csc */  
11:  ([System Margin]) := (([Actual],[Sales]) - ([Forecast],[COGS]));  
12:    

If you're translating many BSO calc scripts to ASO calcs then chances are good that you'll run into one that forces you to use a Procedural Calc. Why the developers chose to implement Procedural Calcs and Allocations in such a different manner doesn't make a lot of sense to me but my hope is that they will extend the functionality in them.