Tutorial 13-4. Customizing Data Within the Cross-Tab

Formulas can also be used to customize the summary data that the cross-tab report is based on. For example, a formula can be used to dynamically change the value that gets summarized or it can change the text that is displayed within the cross-tab.

In this tutorial we are going to use formulas to change the data that the cross-tab is based on. If you look at the original report in Figure 13-11, you’ll see that there are many similar products. For example, there are two Active Outdoors gloves, two Influx gloves, two Triumph helmets, etc. Let’s assume that these products are similar enough to each other that we want the cross-tab report to merge each group into a single row. That way, we will only see a single row for Active gloves, a single row for Influx gloves, etc. To do this, we will base the row heading on a formula that uses the Product Name field rather than just using the Product Name by itself.

1. Open the CrossTab.rpt sample report that was installed with Crystal Reports. Save it as CrossTab Product Name Summarized.rpt.
2. 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.
3. In the bottom left corner of the Cross-Tab Expert dialog box, click on the New Formula button.
4. Name the formula Product Name Summary and click the OK button. This opens the Formula Workshop dialog box. Enter the following formula:

if Left ({Product.Product Name}, 6) = "Active" then
"Active Outdoors";
if Left ({Product.Product Name}, 5)= "Flux" then
"Flux";
if Left ({Product.Product Name}, 7) = "Triumph" then
"Triumph";

5. Click the Save and Close button to save your changes and check that there are no errors. If there are errors, make sure you entered the formula exactly as it is listed here. You also want to make sure that you have Crystal Syntax selected as the current programming language.

After saving the formula, it will be listed in the Available Fields window. We want to put it into the Rows list and remove the Product Name field from that list. We are effectively swapping the Product Name field with the new Product Name Summary formula.

6. Click the Product Name Summary formula in the Available Fields window to select it. Click on the right arrow button for the Rows list. This inserts the summary formula at the bottom of the Rows list.
7. Click on the Product Name field in the Rows list and click the left arrow button to remove it from the Rows list.

You have now swapped the data field for the summary formula. The Cross-Tab Expert should look like the following figure.

When the cross-tab report is run, it will use the summary formula to create the data rows. Since certain rows now have the same value for the Product Name, they will be merged into a single row.

8. Click on the Preview tab to see the report.

You can see that the number of rows within the cross-tab has been reduced. The products that had similar names are now summarized into a single row for each product.