A cross tabulation is a densely populated report or matrix (a rectangular table of elements or entries). Cross tabs resemble spread sheets, and if you are working with data that requires multiple cells that make up a grid consisting of rows and columns, a cross tab is the best tool for the job.  

The development tips in this article are primarily intended for developers without much experience with Crystal Reports.  This article assumes the reader has a basic knowledge of Crystal Reports data access.

Since cross tabs summarize data both vertically and horizontally, this provides users with a spreadsheet like interface that many users are already accustom to. Along with providing users with a known interface, cross tabs save space, allow for custom formatting of each cell, and allow for data expansion. These are features you would not normally have when using a typical grouping report.

Many times beginning Crystal Reports authors overlook the powerful feature of cross tabs; in this article we discuss the basics of creating and formatting cross tabulations in Crystal Reports 2008. We cover three major areas with cross tabs in this article: Getting data into the cross tab, formatting, and some more advanced cross tab features. The following examples are created from the SQL Server 2000 Northwinds Trading Company sample database. The sample report referenced throughout this article pulls data from the Orders, Order Details, Customer and Products tables. The report is intended to be a representation of a typical yearly product order summary. It groups products by customer, and displays the yearly total number of each product that the customer ordered. The examples below use Crystal Reports 2008, but have also been tested with Crystal Reports XI.

1.       Click on insert and select the cross tab item.

2.       Place the cross tab in the report header for this example.

3.       Right click on the cross tab and select Cross-Tab Expert.

4.       Expand the “Customers” table under the Available Fields, select the “ContactName” field, and add it to the rows collection.

5.       Expand the “Products” table under the Available fields, select the “ProductName” field, and add it to the rows collection.

6.       Expand the “Orders” table under the Available fields, select the “OrderDate field, and add it to the Columns collection.

7.       Expand the “Orders_Details” table under the Available fields, select the “UnitPrice” field, and add it to the summarized fields.

Figure 1: The Cross-Tab Expert

 

Let’s go into more detail about what we just did. First, we added 2 fields to the rows “collection”. The first field that was added was “Customers.ContactName”. For each contact name in the returned data set, a row will be printed in the cross tab. The second field added was the “Products.ProductName” field. By adding a second row to the collection, a “sub row” will be inserted for each product that the customer ordered.  The next field that was added was the “Orders.OrderDate”, to the columns collection. This field will print a column for each unique value. We will go more into options for this group later in the article. The last field that was added was the “Orders_Details.UnitPrice" to the Summarized Fields collection. This collection is where your data is summarized.

After you have completed those steps you can preview the cross tab, but this is not exactly what we may have expected to see.

Figure 2: Customer Product Total Cross Tab Default

 

The first thing we need to work on is the grouping. The requirements we specified for this report were to display the yearly totals for each product that a customer ordered.

1.       For grouping the orders, right click on the Cross Tab and select “Cross Tab Expert”.

2.       Click on the “Orders.OrderDate” field in the Columns Collection.

3.       Click on the “Group Options…” button.

4.       Select “for each year” from the "column will be printed" box.

Figure 3: Columns Group Options

While we are in the section expert, we need to change how the “Order_Detais.UnitPrice” is being summarized. The default is “Count” and we need a “Sum”

1.       Ensure you are in the “Cross Tab Expert”.

2.       Click on the “Order_Details.UnitPrice” field under the Summarized fields collection.

3.       Click the “Change Summary…” button.

4.       Select “Sum” in the "calculate this summary" box.

Figure 4: Change Summarized Fields menu

 

At this point you should have a report that looks something like this:

Figure 5:  Customer Product Total Cross Tab Fixed Group Options

 

 

 

Crystal Reports 2008 provides a number of formatting options for cross tabs. Since I lack the talent for color coordination or a taste for design, I usually choose a style included with Crystal Reports for my cross tabs. The “Style” tab is found on the Cross-Tab expert and provides 17 styles to choose from.

Figure 6: Cross Tab Expert Style

 

We are going to skip using one of the included styles and try to make the cross tab presentable ourselves. I find that total columns look more presentable on the right of the cross tab.

1.       Right click on the cross tab and select “Cross-Tab Expert”.

2.       Select the “Customize Style” tab.

3.        Ensure the “Grand Total” field is selected in the Columns collection.

4.       Un-check the “Row Totals on left” check box.

Figure 7: Customize Style

The next formatting option that we will complete is making the totals more visible. We will accomplish this by making the background color gray.

1.       Ensure you are still in the “Customize Style” tab of the Cross-Tab Expert.

2.       Ensure the “Grand Total” field is selected in the Columns collection.

3.       Select  the color gray from the Background Color box.

Repeat the steps above the “Grand Total” field in the Rows collection. Next we will make it easier to distinguish the customer groups.

1.       Ensure you are still in the “Customize Style” tab of the Cross-Tab Expert.

2.       Select the “Customers.ContactName” field from the Rows collection.

3.       Select a navy blue color from the Background Color box.

Figure 8: Report with formatted totals

The cross tab is still hard to read. The black text on the navy background is not very readable. One of the nice features about Crystal Reports cross tabs, is that you are able to format the contents of each cell.

1.       Click on the “Row #1 Name” cell and change the text color to gray.

2.       Click on the products “Total” cell and change the text color to gray.

3.       Click on the “Order_Details.UnitPrice” cell and change the text color to gray.

The next issue with our current cross tab is sizing. Some of the cells are getting cut off, most noticeably the products tab. You can expand and contract individual cells to fit all elements on the screen correctly.

Figure 8: Formatted Cross Tab

Other common formatting options

·         Grid lines (Cross-Tab Expert>Customize Style>Format Grid Lines)

·         Suppressing Empty Columns and Rows (Cross-Tab Expert>Customize Style)

·         Group Date by Quarter (Cross-Tab Expert>Cross-Tab>Group Options>Column will be printed)

·         Group Ordering (Cross-Tab Expert>Cross-Tab>Group Options)

Summary as a percentage

I have found it very useful to include the summary percentage in cross tab reports. What we would like to accomplish is to show what percentage the total product cost is for each product for each customer.

1.       Right click on the cross tab and select “Cross-Tab Expert”.

2.       Select the “Order_Details.UnitPrice” field and add it to the “Fields to Summarize” collection.

3.       Click on the newly added "Order_Details.Unitprice” item and click “Change Summary” button.

4.       Ensure that “Sum” is selected for Calculate this summary.

5.       Check the “Show as percentage of” check box is checked.

Figure 9:  Summary as percentage

Vertical text

To save space on cross tabs you have the option to make the text display vertically instead of horizontally. In this example we will change the headers of the cross tab to display vertically. Web disclaimer - currently text will not render vertically if using the Crystal Reports Web XI or lower viewer control.

1.       Right click on the “Column #1 Name” field and select “format field”.

2.       Select the “Common” tab.

3.       Set the value to 90 in the “Text Rotation box".

Figure 10: Vertical Text

Repeat the steps above for the “Total” header field.

Text Highlighting

The data presented in the cross tab we have created is very clean and easy to read, but sometimes it’s nice to be able to highlight certain areas of interest in the report. Let's add a requirement to highlight product totals that are over $30.00.

1.       In “Row #2”, right click on the “Order_Details.UnitPrice” field and select “Hightlighting Expert”.

2.       Click the “New” button.

3.       Set the “Value Of” to “this field”, “Is greater than” and “$30.00”.

4.       Set the Font color to red.

Figure 11: Highlighting Expert

Figure 12: Report with Vertical Text and Highlighting

The tips in this article are just the beginning when it comes to developing cross tabs for Crystal Reports. Formatting cross tabs can be tricky, and getting the correct data to summarize how you intend it to can be a frustrating task. Using the tips in this article as a starting point and with a little practice, you will be able to put the cross tab craziness to rest.