Cross-tabs calculated before/after group selection

Friday 13 November 2015 @ 10:32 pm

I just learned a neat trick for cross-tabs that allows you to decide if the cross-tab is calculated in the first pass or in the second pass. A cross-tab that uses only database fields or most formula fields will be calculated in the first pass. But if you use a specific type of formula in the cross-tab, then the cross-tab will be calculated on the second pass.

[ crickets ]

To see if this will help you I guess we have to answer the two obvious questions:

1) What difference does it make if a cross-tab happens in the first or second pass?
2) How do you create the special formula?

The answer to the first question has to do with Group Selection. Say you have 100 customers in your report but you only want to report on those who have spent more than $1000 in total. You would create a subtotal for each customer and use that subtotal in the Select Expert (Group) as a criteria. A filter that involves a subtotal has to be applied as Group Selection.

So, lets assume that using Group Selection we reduced the report form 100 customers to 30. Now you add a cross-tab to the report without using a special formula. This cross-tab will still include all 100 customers even though the report only shows the 30 customers that are over $1000. This is because a normal cross-tab is calculated before Group Selection – in the first pass. But if you use a special formula in the cross-tab, then it will be calculated after Group Selection – in the second pass. This cross-tab will be based on the 30 customers.

So how do we create a special formula? All you have to do is start the formula with “WhilePrintingRecords”. So lets say that the cross-tab mentioned above has a column for each Ship Date. You could write a formula that says:

WhilePrintingRecords;
{Orders.ShipDate}

If you use this in place of the original Ship Date field in the cross-tab, that pushes the Cross-tab to the second pass. You can do this with any field used by the cross-tab. Also, any formula that involves a grand total or subtotal will have the same effect when used as part of the cross-tab, even without the phrase WhilePrintingRecords.

This blog article includes a diagram of the passes and even shows the two places where cross-tabs can be calculated.









Leave a Reply

Recrystallize Pro