This post is an update to an older post where I suggested a method for getting cross-tabs to reflect group selection criteria.  I just discovered a flaw in that approach, and so here is a better way.  You can download a sample report that illustrates the issue and a screen shot of the report output below:

This report has a group selection formula that limits the results to customers with a subtotal of over 50K. There are three customers that meet this criteria out of the 77 customers in the report data. To get an accurate grand total I added a running total (in blue) to the report footer.

A normal cross-tab added to this report would show all 77 of the customers that met the RECORD selection criteria. But we want just the three customers that meet the GROUP selection criteria. So I added two modified cross-tabs to the report, one using my original solution and one using the new solution. You will notice that the grand total of the Original solution (in Red) doesn’t match the the running total in blue. It is off by the value of a 4th customer who is being included in the cross-tab incorrectly.

This customer doesn’t meet the criteria and is not shown in the final report output. But if you were looking at the report in preview mode you would see that this customer is the first customer listed in the group tree. If we then changed the group to Descending Order, then a different customer would be first in the tree, and that customer would show up (incorrectly) in the cross-tab.   I have no idea why the first group is treated differently.

But if you look at the New Approach, this extra customer is shown with a zero amount and the total of the cross-tab (Green) matches the running total in blue. We could eliminate this extra row completely by using the “Suppress Blank Rows” option in the cross-tab properties.

So what is the new approach?  We still create a formula for the Amount field, but we incorporate the Group Selection criteria into the formula, something like this:

if Sum ({Orders.Order Amount}, {Customer.Customer Name}) > 50000
then {Orders.Order Amount}
else 0

One note on using these types of formulas in the cross-tab.  You can add these formulas while in the cross-tab expert, but if you go to change any cross-tab properties, the formula will be replaced.  To get around this you can temporarily comment out the formula and just use the raw database field.  Then you can make any cross-tab changes.  When you are done you can put the formula back the way it was.