Twice in the past 2 weeks I have had to take two different summary fields in a cross-tab and calculate a ratio of one to the other.  The most common example would be when one summary is “Sales” and the other is “Cost of Goods” and you want to calculate a Gross Profit Percent from them.  This type of calculation got much easier with CR 2008, because you can now add calculations that draw values from other cells in the cross-tab.  The functions that do this is can get pretty complex but this example is not that bad.

In our example the row and column fields can be anything.  All we need are two different summary fields:

Sum of Sales
Sum of Cost of Goods

And for this example the summary fields will be arranged vertically, with the Sales on the top and the Cost of Goods below it.  The steps are:

1) Right click on the Cost of Goods Summary in any cell and select

Embedded Summary > Insert Embedded Summary

This should add a third summary row in each cell of the cross-tab. The will all say “Edit this formula”.

2) Right click on that phrase and select

Embedded Summary > Edit Calculation Formula

3) Paste in the following formula:

Local NumberVar  Sales :=
GridValueAt (CurrentRowIndex, CurrentColumnIndex , CurrentSummaryIndex -2 );

Local CurrencyVar COGS :=
GridValueAt (CurrentRowIndex, CurrentColumnIndex , CurrentSummaryIndex -1 );

if Sales = 0 then 0 else
(Sales - Cogs) % Sales

Here is how the above formula works.  The first two rows read the cross-tab value from 2 rows up (Sum of Sales) and hold that value in a local variable called Sales.  That way I can refer to that value several times simply without having to repeat the entire function each time.  The next two rows do the same thing with Sum of COGS which is pulled from 1 row up.  The last two lines do the actual calculation, including a check to make sure that Sales are not zero.  That prevents the ‘divide by zero” error.   (Note, if your Sales and and COGS fields are currency fields the formula will give you an error, so change the variables to CurrencyVar).

4) Save the formula and the third summary row in every cell should calculate the ratio based on the two numbers immediately above.  The cross-tab will perform the calculation in all group levels of the cross-tab including the grand total rows and columns.

If you need help deploying this formula or need something slightly different, let me know and we can schedule a session to get you what you need.