I learned a nifty trick from a customer this week.  They had a bar chart with names underneath each bar.  After each name was a number in parentheses, which was the quantity for that bar.

I was surprised that CR allowed a formula with a subtotal in it to be used as the ‘on change of’ in a chart.  Then I tried it myself and confirmed that it works. So lets take a simple example from the Xtreme sample data that comes with CR.  I created a simple report that was grouped by customer.  I then added a subtotal that showed the number of orders for each customer.

Before I added the chart I created a formula called ‘Chart Label’ like this:

{Customer.Customer Name} & ' ('
&  Totext (Count ({Orders.Order ID}, {Customer.Customer Name}),0) & ')'

Then I added a bar chart.  I used the ‘advanced’ option so I could choose the ‘Chart Label’ formula as my ‘on change of ‘ field.  For the ‘show values’ field used Order ID and set the summary to ‘count’.  The chart then showed 6 customer bars and the label under each bar was the customer name, followed by the subtotal in parentheses.

The trick is that Crystal won’t let us group on a formula that references a subtotal. And if you try to change a chart’s ‘on change of’ field to a formula that uses a subtotal, you may not see it in the list. But the steps above gets the chart to work as described.

And thanks to Kendra Patterson at WRB Communications for sharing this technique.