AbstractMany 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.

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.

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:

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.

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.

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”.

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.

Title:
Cross Tab Total in Time Format
Name:
Venkatesh
Date:
6/18/2010 2:33:52 AM
Comment: Hi, how to get time summary in total field

Title:
Trainee
Name:
Mouhsine
Date:
6/11/2010 8:40:07 AM
Comment: Hello all, I have some difficulties with CR 2008. My current problem is to add a row in my crosstable with evolution rate between TurnOver of year N and year N+1.I now that is possible by Calculated Member in Cross Tab but I don't know how. Can you help me ?

Title:
Summary Differences In Cross Tabs
Name:
Phasor
Date:
9/25/2009 9:20:40 AM
Comment: I need to create a cross tab report where the summary is the difference between the maximum and minimum values. Is there a built in summary field for this and if not how do I go about entering a custom formula in the summarizied field.

I have a main report that used a cross-tab in one of the sub reports in the main report. The cross-tab expanded horizontally so I checked the Repeat On Horizontal Pages property of my text objects so that they will be displayed also on the horizontal pages. I run the report in my web application and was displayed properly including the text objects in the horizontal pages. However, when I exported the report into pdf, the horizontal pages was not included.

Is there a way to include the horizontal pages in a pdf if the cross-tab is in a subreport?

Note: Tried the cross tab in the main report and when exported to pdf, it included the horizontal pages.

Hope to get a quick response.

Thanks

Title:
how to read the summary field in cross tab
Name:
Maignanamurthy
Date:
9/22/2009 9:32:58 AM
Comment: i have created one cross tab report in vb.netthis report contains one summary field value alsoit works finePROBLEM ISi want to re-caluclate the summary field.for that purpose i want to get the summary field value in Cross Tab Report.tell mehow to read or get the summary filed value in cross tab report.

Title:
Get the value of summary Field in cross tab
Name:
Maignanamurthy
Date:
9/22/2009 9:23:48 AM
Comment: i want to re calculate the cross tab summary field value.how to get the summary field value in crystal report.

ITS URGENT

Title:
Repeat labels for rows
Name:
keith
Date:
6/25/2009 12:38:33 PM
Comment: For Crystal 11...In your example above, is there any way to make Alejandra Camino to print on every row in that section?

Title:
Dynamic Connection on CrossTab
Name:
Saga
Date:
5/6/2009 2:59:21 AM
Comment: this article is good but can anyone help me on

How to Get Data from SQL and display to Crosstab through source code without the use of WIZARD (The Cross-Tab Expert).

I had done this connection using Unbound Text Fields.is there a version on Crosstab? please Help.

I have more than 3 summarized field and would like to label them each.Can anyone let me know how we do this.

ThanksMano

Title:
to caluculate weighted avg
Name:
Anitha
Date:
2/4/2009 4:01:47 AM
Comment: Hi,I have one summary field to caluculate Weighted Avg.I have caluculated by using the inbuilt function Weighted Avg.But i have one query here ,if the denominator is '0' that time it is showing as blank.but i need there also 0.how can i solve this.please help me on this

Title:
Sticky Thickets
Name:
Kevin Perez
Date:
1/15/2009 3:07:38 PM
Comment: I'm trying to create a crosstab that always has the same set of columns.

I don't have any record for one of the columns, but want that column to show in the crosstab, regardless.

Basically there is an option for the @personnelcost to be displayed as average. Go to Summarized Fields (Cross Tab Expert) and change summary (by default=sum) to average.

I dont think you can devide the personnelcost by the total orders which is another field.It can be done if you're using Group Expert but it's not work in Cross Tab. It's impossible unless the formula have been defined in formula field.

Title:
Here is a tough one
Name:
Sanjay
Date:
11/26/2008 12:20:34 PM
Comment: I am using a cross tab in Crystal 11 and already have a field insterted that I am taking the average of (in this case Personnel cost). I would like to take that average returned and devide it by the total orders which is another field in the cross tab but i can't seem to get the average of the average / cost. Any thoughts?

Title:
Outstanding
Name:
Rajesh
Date:
11/4/2008 8:57:25 AM
Comment: This article is very good and it helps me lot. I am new to Ccrystal report. I need some info @ tabular report.Can i link column to subreport like link report.

Title:
Thanks for the information..Additional Question
Name:
John Heck
Date:
10/23/2008 10:00:57 PM
Comment: I really enjoyed your article and was wondering if you could tell me what I am trying to do is possible and if so how. I am trying to create a cross tab report that uses Sales Reps, Company, and Employee down the left side and Sales Reps across the top with the intersection being a commission rate. What I am trying to do is show all the sales reps that share a commission with other sales reps for each employee for each company.

Regards,John Heck

Title:
Mr
Name:
Muhammad Yaseen Khan
Date:
8/24/2008 4:06:44 PM
Comment: Sir i am very happy to see your tutorial. beleive me its very nice . i just started crystal reporting but after your tutorial i am feeling that i can easily make some other Cross tab reports because you explain very nicely. i am thankfull to you. and hope you will continue share your expirence in the form of such tutorial.Thank you

With regards

Yaseen Khan

Title:
Excellent Article for Beginners
Name:
Md. Mushtaque
Date:
8/17/2008 2:01:19 AM
Comment: Sir,I am glad to see such article on Cross-Tab Report. It is really excellent for Beginners. If you could suggest something on Text data to be displayed and calculated as number and again displayed as text in a particular format.Such as if there is a field of Hours having data as 2:30,2:45,2:15. How to display these data and show the total hours as 7:30 where as I got the value as 7.50 Hours but as per the hours format it is not exactly correct and acceptable.If you could suggest something on it, it will be really appreciated and this could solve lots of problems in Management of Time and Money.

Thanks again for your Article. Please put some articles on advanced and lively features.