<!wp_title:11. 1 Introducing Cross-Tabs>
<!wp_category:Crystal Reports.NET 2003>
<!wp_category: Chapter 11 - Cross-Tab Reports
Cross-tab reports are a powerful way to create summaries of data in a spreadsheet style format. They generate summary data in a grid where the rows and columns represent groups of data. This provides the user with a report format that is easy to read and uses a small footprint.
Ask a programmer how he or she feels about cross-tab reports and you will probably get a variety of answers - both good and bad. I think that programmers can be put into three general categories about their experiences with cross-tab reports. Some programmers have tried using cross-tab reports and found them to be too confusing. They shrug them off as being unworthy of the effort to learn. Other programmers have successfully used cross-tab reports, but found a variety of problems in getting the data that they wanted. This group uses cross-tab reports only when absolutely necessary. And last, but not least, is the programmer who has successfully mastered the cross-tab report and found it to be a great way of producing reports that quickly summarize groups of data. They gladly use cross-tab reports whenever appropriate.The goal of this chapter is to take you from being a beginner in writing cross-tab reports to the level of an expert.
<!wp_title:11. 2 Understanding Cross-Tabs>
<!wp_category:Crystal Reports.NET 2003>
<!wp_category: Chapter 11 - Cross-Tab Reports
Understanding Cross-Tab Reports
Cross-tab reports are a way of reformatting a report that groups data into a grid format. This grid format is very similar to the way a spreadsheet represents data. It lets the user visually analyze the data in a way that makes it easy to compare values in one group against the values in another group. Let's look at the grouping report in Figure 11-1 and then we'll see it reformatted as a cross-tab report.
<!wp_pic:
Figure 11-1. Grouping by Product and Quarter.
This report has two grouping fields. The outermost group is by Product Type and the innermost group is the Order Date grouped quarterly. The group header for the Order Date is the first date in the period. The detail records show you the Employee Id, Order Date, and Quantity. There are two sub-totals of the quantity. The first occurs on the change of quarter and the next is on the change of product type.
This is a pretty standard grouping report and it shares a common problem with other grouping reports: the sub-total amounts are spread out across multiple pages. This makes it hard to compare numbers because they aren't consolidated into a single page.[1] A user reading this report will find that they are continuously flipping pages to see how the sales of one product compare to the sales of another product.
Re-writing this report as a cross-tab report eliminates this problem. Figure 11-2 shows the same report in cross-tab format.
<!wp_pic:
Figure 11-2. Cross-tab report by Product and Quarter.
The cross-tab is much easier on the eyes. The outermost group field, Product Type, is represented on each row of the grid. The innermost group field, Order Date grouped by quarter, and makes each quarter a separate column. These columns span horizontally along the page. Although there are many detail records in the original report, these are ignored when generating the rows and columns. Only the values of the grouping fields are listed. The cross-tab report took two grouping fields and made them the X-axis and Y-axis of the grid.
The data inside the grid corresponds to the subtotals on the grouping report. The first row is for the Competition product type. It shows values of 5, 7 and a total of 12. When you look at the grouping report in Figure 11-1 you see that these match the subtotals for the Competition product type. Each row in the cross-tab report shows the same subtotals that are displayed in the grouping report for the product type groups. Thus, the cross-tab report took the sub-totals of a grouping report and formatted them as a grid. All the data is summarized into a very compact space and it doesn't span many pages like the grouping report would.
Note
It might help to think of a cross-tab report as taking a multi-group report and just copying the group footers into a grid.
The benefits of using a cross-tab report can be offset by the drawbacks. As powerful as the cross-tab report is for summarizing data, it has many limitations. These limitations are discussed throughout this chapter, but let’s look at two obvious ones first.
The first limitation is that the original grouping report has a lot of data on it that isn't shown on the cross-tab. For example, the cross-tab report doesn't show the fields for Employee Id or the Shipping Date. In fact, it doesn't have any detail records shown. Although these fields are very important, the cross-tab report can't show individual detail records. This is because a cross-tab report can only show summary calculations.
The second limitation is that you can only print numbers in the summary fields. No text values are allowed. This is because each cell must calculate a summary function and summary functions can only return numbers. If you attempt to put a text field in the cell, then the report will default to printing a count of the text fields.
Given the benefits and drawbacks of cross-tab reports, you have to consider your alternatives before using the cross-tab report. The standard grouping report is great for showing as much information as necessary and having control over the format. But the data could span many pages and this makes it hard to do analysis with. The cross-tab report gives you the ability to quick analyze summary data, but you have to give up looking at the detail records that make up the data.
Tip
If you have a report that needs to benefit from both types of reports, a solution that is to combine the two reports. Create a grouping report that prints all the necessary detail information. Then add a cross-tab object to the report header. This lets a user see a summary of the critical information on the first page of the report and then dive into the details printed on the remaining pages.
<!wp_title:11. 3 Creating a Cross-Tab Object>
<!wp_category:Crystal Reports.NET 2003>
<!wp_category: Chapter 11 - Cross-Tab Reports
Creating a Cross-Tab Object
The name "Cross-Tab Report" is a little misleading. It makes it sound like the whole report only shows the cross-tab grid and that no other data is printed. This isn't true. A cross-tab report refers to a report that has a cross-tab object in one of its sections. This object is similar to the other report objects on a report. It has properties that let you modify its fields and how it's formatted.
There are two ways to add a cross-tab object to your report. The first way is to add a new report to your application from the Project menu and choose the Cross-Tab Expert from the Crystal Report Gallery dialog box. This is shown in Figure 11-3.
<!wp_pic:
Figure 11-3. Choosing the Cross-Tab Expert from the Gallery.
You can also add a cross-tab object to an existing report by right-clicking on the report and selecting Insert | Cross-Tab. Both methods of adding a cross-tab object to your report gives you the dialog box shown in Figure 11-4.
<!wp_pic:
Figure 11-4. The Cross-Tab Expert dialog box.
There are three primary input areas on this dialog box: Rows, Columns, and Summarized Fields. Below these three sections is the list of available fields. Add fields from this list into the appropriate sections above it. Do this by either dragging and dropping the fields or by selecting a field and clicking the one of the Add buttons: Add Row, Add Column, or Add Summarized Field. If you want to see an example of the data that a field contains, click on the Browse button.
All three windows require you to add a minimum of one field to each before the cross-tab is functional. For example, you can't specify fields for the Rows and Summary Fields windows and not put a field in the Columns window. You can also add multiple fields to each window to make the cross-tab print additional data.
<!wp_title:11. 4 Summary Functions>
<!wp_category:Crystal Reports.NET 2003>
<!wp_category: Chapter 11 - Cross-Tab Reports
When you add a field to the Summarized Fields window, by default it assigns one of two different summary functions to the field. If the field is numeric, then the Sum() function is used. If the field is text, then the Count() function is used. You can change the default summary function after it has been added to the Summary Fields window by clicking on the Change Summary button.
When changing the summary function for a numeric field, all the summary functions in Crystal Reports are available. Summarizing on a text field is more restrictive. Remember that text fields aren't allowed to be printed in a cross-tab cell. Instead you have to choose from a list of text compatible summary functions. Not every function works with a text field. For example, it isn't possible to calculate the average value of a text field. However, you can determine the 5th largest item of all available items and print that. The text compatible functions that you can choose from are in Table 11-1.
Table 11-1. Summary functions available for text fields.
Function NameCount()
DistinctCount()
Nth Largest()
Nth Smallest()
Nth Most Frequent()
Minimum()
Maximum()
Mode()
Summary functions are typically based on fields from a data source. But there are times when you need to summarize a custom formula field. You can select a formula that already exists or you can create a new formula from the Cross-tab Expert dialog box. When you click the New Formula button, it brings up the Formula Editor dialog box. This is the same dialog box discussed in Chapter 7. After you save and close this dialog box, that formula is added to the list of available fields at the bottom of the dialog box. You can then drag and drop the formula field into the Summary Fields window. Although this formula was created via the Cross-tab Expert, it will now be listed along with all the other formulas in your report. Thus, it can be placed on your report just like any other report object.
Caution
Crosstab summary functions can't perform many tasks that you take for granted with other types of reports. For example, it is a common error to attempt to devise formulas for a cross-tab report that calculates a value depending upon the value of another cell in the cross-tab or the sum of a group of cells in another row or column. You expect this to be possible because it is easy to do for a standard report. But a cross-tab report can't do this because it is built around the premise that each summary value is calculated independently of the other cells. Cross-tab summary fields are calculated during the report's first-pass and there is no mechanism to reference the value of any other field in the cross-tab. You can try to get creative and write a formula to emulate these tasks, but you will find that each formula relies upon having information about the other fields in the cross-tab. Thus, it won't succeed. The tasks that a cross-tab report can't perform are as follows: calculating second-pass formulas, calculating running sums, calculating percentages of the subtotal/grand total and sorting rows according to the row totals.
Once you are finished adding the fields, click the OK button. The cross-tab object is added to your report and you are put back at the report designer.
Adding additional summary fields to the cross-tab object results in the values being placed in the same cell. Each is stacked vertically on top of the other. The first field added is placed at the top.
<!wp_title:11. 5 Sub-Groups>
<!wp_category:Crystal Reports.NET 2003>
<!wp_category: Chapter 11 - Cross-Tab Reports
Adding additional grouping fields for either the rows or columns creates a sub-group format. This is very similar to a standard report that uses multiple groups. The first field becomes the outermost group and the remaining fields are grouped based upon the subset of data. If you preview the report and find that the fields are not in the proper order, you can rearrange them by opening the Cross-tab Expert again and using the mouse to drag and drop the fields to the correct position in the list.
Figure 11-5 shows a cross-tab object in design mode that has two grouping fields in the row and two summary fields.
<!wp_pic:
Figure 11-5. The cross-tab object using multiple fields.
The large, left-most block in the cross-tab object represents the outer-most group. The innermost group is represented by the two thinner blocks just to the right of it. Both groups have a subtotal field associated with them. This is similar to a standard multi-group report because when it prints there will be a group footer showing the subtotal for the group. The two summary fields are visually represented by the multiple fields filled with the number five. The top-most field is the first summary field that was added to the Summary Fields window.Running the report generates the output shown in Figure 11-6.
<!wp_pic:
Figure 11-6. The output of the cross-tab report using multiple fields.
There is no way to make a cross-tab object print the summary fields side by side. Each value is always stacked one on top of the other. You can get around this limitation by creating a duplicate cross-tab object, modifying its summary function, and then aligning it to be on top of the other cross-tab object. This gives the illusion of a single cross-tab object with multiple summary columns.
Follow these instructions to generate this type of output. Create a cross-tab object that is formatted exactly the way you want it. Make sure that the column widths are wide enough to support printing two fields side by side. Get this right the first time because if you later have to go back and reformat the cross-tab it will be much harder. Copy and paste the cross-tab object to the same section of the report. Modify the summary function to be the new calculation. Suppress all the fields except the summary fields and turn off all the grid lines. Left justify the summary field in one of the cross-tab objects and right justify the other summary field. This prevents the data from overlapping. Align the two cross-tab objects so that one is on top of the other. If all the steps were followed properly, you should not be able to tell that there are two objects on your report. You are now ready to print the report. A sample report is shown in Figure 11-7.
<!wp_pic:
Figure 11-7. Output of printing summaries next to each other.
<!wp_title:11. 6 Placing the Cross-Tab Object>
<!wp_category:Crystal Reports.NET 2003>
<!wp_category: Chapter 11 - Cross-Tab Reports
Placing the Cross-Tab Object
If you look back at the cross-tab report example from earlier in the chapter, you might realize that the cross-tab object as it appears in the report designer doesn't look like the cross-tab grid as it appears when printed on the report. This is because the report designer shows the cross-tab object as being a template for showing you what fields are used and how the cross-tab grid will be formatted. When the report is run the cross-tab object expands vertically and horizontally so that it can print as many columns and rows necessary to show each group. If you expect your report to have a lot of columns then make sure it is placed along the left side of the report to account for all the columns.
Tip
When you put a cross-tab object in a section, it will grow as large as necessary to print all the data. If you place other report objects below the cross-tab component, and in the same section, the cross-tab grid will probably overwrite them when it expands during the print process. Fix this by creating a second section in that area and place the lower data in the new section. This will let it appear just below the cross-tab grid without any overlapping.
The cross-tab object can only be placed in a certain sections: the Report Header/Footer and the Group Header/Footer. It can't be placed in the detail section because it can't print detail records. It also can't be placed in the Page Header because it would be duplicated on each page without any of its data changing. This would create redundant information that wastes space.