The cross tab functionality in Crystal Reports is a great
way to summarize data. Cross tabs let you pivot data so that a field's value is
displayed as columns in a table. Cross tabs are particularly useful when
creating any date driven reports that need to see summarized data. This report
displays each employee for the territory as the rows and the fiscal year's
quarter as the column. Before we create the cross tab we need to create a
formula that will tell us the fiscal quarter for the
SalesOrderHeader.OrderDate. We then will use this formula for the columns in
the cross tab.
1.
In the Field Explorer window right click on the Formula Fields and
select New… from the pop-up menu.
2.
The Formula Name dialog should appear. Enter FiscalQuarter for the name
and click the Use Editor Button.
3.
Enter the following formula:
Listing 1
'Q' + totext(DatePart("q", {SalesOrderHeader.OrderDate}), 0) + '/' +
totext(Year({SalesOrderHeader.OrderDate}), 0, '')This formula creates a string based on the
SalesOrderHeader.OrderDate. If the order date was 4-14-2009 then the formula
would return 'Q2/2009.'
4.
Click the Save and close button.
The cross tab
also wants to use the employee's name as the row; however, we need to use the
name formatted as Last Name, First Name. We must create another formula to
accomplish this.
5.
In the Field Explorer window, right click on the Formula Fields and
select New… from the pop-up menu.
6.
The Formula Name dialog should appear. Enter EmployeeName for the name
and click the User Editor button.
7.
Enter the following formula:
Listing 2
{Contact.LastName} + ", " + {Contact.FirstName}8.
Click the Save and close button.
9.
Now you can create the cross tab using these formulas.
10. Right
click on the white space in Group Header #1 section. Select InsertàCross-Tab… from the pop-up menu.
11. Drop
the cross tab in the Group Header #1 section on the left side of the report. The
Cross-Tab Expert dialog should appear.
Figure 7
12. Click
on the FiscalQuarter field from the list of Available Fields.
13. Click
the > button next to the Columns list.
14. Click
on the EmployeeName field from the list of Available Fields.
15. Click
the > button next to the Rows list.
16. Now
you need to specify which field to summarize. For this example we will
summarize the SalesOrderHeader.SubTotal field. Click on this field in the list
of Available Fields.
17. Click
the > button next to the Summarized Fields list. By default, this uses the
summary operation on this field which is what we want. You could change the
summary operation to average, minimum, maximum, count, and a host of others. To
see all available options click on the SalesOrderHeader.SubTotal field in the
Summarized Fields list and click the Change Summary button.
Figure 8
18. Click
the OK button. You should now see the cross tab in the Group Header #1 section.
