How To Create A Crosstab Report In Crystal Reports

This is an excerpt from the book Crystal Reports Encyclopedia. Click to read more chapter excerpts.

Tutorial 10-5. Customizing the Column Grouping

Rather than formatting the cross-tab object based upon the data within the report, you can also let the reader decide how to customize the cross-tab. By integrating parameters within the cross-tab, the user gets to choose how the cross-tab is designed.

This tutorial customizes the cross-tab by modifying the grouping column. The report first asks the user whether they want to group by year or by quarter. The cross-tab changes the column data based upon the user�s response.

Open the CrossTab.rpt sample report that was installed with Crystal Reports. Save it as CrossTab Using Parameters.rpt.
The first step is to create the parameter that prompts the user for how they want the cross-tab printed. Create a new parameter by going to the Field Explorer window, right-clicking on the Parameter Fields item and selecting New.
For the parameter name, enter Cross-Tab Grouping.
For the List of Values, leave it at the default value of Static.
Use the Insert button to create two new parameters3: �Group by Quarter� and �Group by Year�.
For the Options listed at the bottom, set Allow Custom Values to False.
Now that the parameter is created, we need to create a formula that uses that parameter to set the date format and then we�ll use this formula in the cross-tab object.
Make sure you are on the Design tab and right-click on the top left corner of the cross-tab object and select Cross-Tab Expert from the pop-up menu.
Click the New Formula button and name the formula Date Grouping.
Enter the following formula using Crystal syntax.

if {?Cross-Tab Grouping} = "Group By Quarter" then
CStr({Orders.Order Date}, "yyyy") & " - Q" & CStr(DatePart("q", {Orders.Order Date}), 0)
else
CSTR({Orders.Order Date}, "yyyy")

Click the Save and Close button to save the formula and check for errors.
Since the formula uses a parameter, you are prompted to set the parameter value. Choose the Group By Quarter option.
On the Cross-Tab Expert dialog box, select the Date Grouping formula in the Available Fields window and click on the right-arrow button for the Columns list.
Within the Columns list, we want to remove the Orders.Order Date field that was previously there. Select on that field and click the left-arrow button to remove it from the list. The only field that should be left in the Columns list is the Date Grouping formula.
Click the Ok button to close the Cross-Tab Expert dialog box.
Click on the Preview tab to look at the report. In an earlier step you already selected Group by Quarter for the parameter value. So the report should look similar to the Figure 10-ae.

Figure 10-ae. Cross-Tab with grouping by quarter.

If your report looks fine, then press the F5 key to refresh it and prompt for a new parameter value. Select Group By Year and your report should look similar to the following:

Figure 10-af. Cross-Tab with grouping by year.

In this example we used parameters to customize the cross-tab. This let the user decide how the Order Date should be grouped. You can use parameters within formulas to let your users customize cross-tab reports in a variety of ways.

To read all my books online, click here for the Crystal Reports ebooks.