Tutorial 13-5. Customizing the Column Grouping with Parameters

Rather than formatting the cross-tab object based upon the data within the report, you can use parameters to let the reader choose how to customize the cross-tab.

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.

1. Open the CrossTab.rpt sample report that was installed with Crystal Reports. Save it as CrossTab Using Parameters.rpt.
2. 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.
3. For the parameter name, enter Cross-Tab Grouping.
4. For the List of Values, leave it at the default value of Static.
5. Use the Insert button to create two new parameters: Group by Quarter and Group by Year.
6. 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 we’ll use this formula in the cross-tab object.

7. 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.
8. Click the New Formula button and name the formula Date Grouping.
9. 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")

This formula checks what the user entered for the parameter value. If it is “Group By Quarter”, the date is formatted to show the year and the quarter. If not, just the year is shown.

10. Click the Save and Close button to save the formula and check for errors.
11. 11. Since the formula uses a parameter, you are prompted to set the parameter value. Choose the Group By Quarter option.
11. 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.
12. Within the Columns list, we want to remove the Orders.Order Date field that was previously there. Select 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.
13. Click the OK button to close the Cross-Tab Expert dialog box.
14. Click on the Preview tab to look at the report. In an earlier step, you already selected Group by Quarter for the parameter value. The report should look similar to the following figure.

15. 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:

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