Crystal Reports crosstab

Question

I am new to crystal reports so this might be novice to you.

I have created a cross tab with row grand total to the right. How can I add an extra column to display Average of the rows. I am using Crystal reports 2008

Here is a rough example of the cross tab- How do I insert the Average column? Thanks

        New York   Chicago     Boston     Total       Average   

Gross Sales

Answer 1

Crystal doesn't allow you to add additional columns to crosstabs at the same level of grouping, and it repeats any summarised values at all levels of summarisation. However, the following should achieve what you want:

1. Create a dummy formula, set to a constant value (eg. " ").
2. In the Cross-Tab Expert, add your dummy formula to the list of column items as the first item.
3. In the Cross-Tab Expert, you should already have sum of gross sales in the list of Summarized Fields. Drag the gross sales figure from the list of Available Fields into the Summarized Fields, so that sum of gross sales appears twice in the Summarized Fields, highlight the second sum and click on the Change Summary... button to change the summary operation from Sum to Average. Click on the OK button in the Edit Summary dialog to return to the Cross-Tab Expert, where Sum and Avg of gross sales will be listed in the Summarized Fields, then click on the OK button to confirm these changes.
4. You will now have a Crosstab with an additional summary column and two summarised values in each summary cell. Select all of the average values in the Group Columns using Ctrl-Click, right-click and select Format Objects... and check the Suppress option. Click on OK - those values should now be greyed out.
5. Select all of the total (but not the average) values in the grand Total column using Ctrl-Click, right-click and select Format Objects... and check the Suppress option. Click on OK - those values should now be greyed out.
6. Edit the text box for the grand Total column and change it to say Average.
7. Preview the report - you should now have a column of Average gross sales to the right of your Total Gross Sales.

Crosstabs are really not Crystal's strongest point.