Cross-Tab in Crystal reports

Introduction

Cross-tabs are special objects, designed in a spreadsheet style format (such as excel), you can place in your Crystal Reports. The cross tab object refers to rows and columns on the grid as groups of data in order to generate a summary data. This provides the user with an advanced analyzing data tool supported by an easy to read and use report format.

Before starting with the explanation, here are some general facts:

- You cannot create a cross-tab without a summarized field.

- All columns in a cross-tab must be the same width.

- You can pivot cross-tabs (swap the position of the rows and columns) .

- Cross-tabs doesn’t support RTL.

To illustrate the need of such an object, consider the following example:
A marketing analyst wants to generate a report that will show how many products (in units) were sold in each US state and what was the total cost.

The simple (but not easy) way to generate such a report is to generate a report that is initially grouped by US state and within grouped by product type as appears in Table1.



It should be noted that this kind of report may cause a difficulty to the analyst in comparing between some totals. For instance, comparing between the total income from selling Mountain bikes and the total income from selling Kids bikes.

A much simple view of the totals could be achieved by filling a grid using cross tab object as appears in Table2.


It should be noticed that the display in Table2 is much easier to read and use and in fact, the comparison between the total income from selling Mountain bikes and Kids bikes is immediate (9850$ Vs. 15360$).

Cross-Tab object wizard

The wizard can be divided into two main sections- Data section and Design section. The data section is handled under the tab named "Cross-Tab" and the design section is handled in tabs named "Style" and "Customize Style".

The "Cross-Tab" tab is used to define the database fields or formulas that make up the rows and columns of the cross-tab. The "Style tab" lets you choose a predefined formatting style for the grid on the cross-tab. And, the "Customize Style" tab displays a large number of custom formatting options to precisely control the appearance of the cross-tab.

How do we choose the data to be displayed in the cross-tab?

The "Cross-Tab" tab contains several data cubes:

- The "Available Fields" cube displays a list of the available report fields (for display).

- The "Columns" cube displays the list of fields that should be presented as columns.

- The "Rows" cube displays the list of fields that should be presented as rows.

- The "Summarized Fields" cube displays the fields to be summarized in each cell.

The summarized fields in the previous example were "Total Income" and "Total sold units". It should be stated that a summarized field can also be placed in the row total or column total of the cross tab object.

The choice of the fields could be performed by "Drag & Drop" a field from one cube to another or by using arrows. The summarized type of each summarized field could be changed by clicking "Change Summary" button.

How the data source of the cross-tab report should be look like?

In order to display properly that data on the Cross tab report, each cell on the cross tab object should be represented in a different row. A General structure of each row on the data source should be looked like:

For example, we can consider the data source for the previous example:

This structure of the data source allows the cross tab object to build a dynamically rows and columns that are based only on the data source rows.
The cross tab object search for a set of distinct row-values and distinct column-values and based on those sets builds the relevant table in the report.

Example of a dynamically cross-tab report

An international shipping company delivers in each day commodity between countries. For control management purposes, the company wants to generate a monthly report that will display the number of deliveries that were taken in each month in each route. A route is a path from a specific country to a specific country.

1. The data source for this report is in the form:

2. The Cross-Tab object wizard:

- Rows cube: "Source Country".

- Columns cube: "Target Country".

- Summarized Fields cube: "Sum of Total Monthly Deliveries".

3. The Cross-Tab object design:

Summary

Cross-tab object is a neccessary tool that displays data in a spreadsheet format and allows us to generate a summary data for further analyzing.

I hope that this post will help you to get started with this powerful crystal report's feature.

Ehud