Creating a manual cross tab report

Crystal has a very nice cross tab expert that is excellent for many assignments. However, there are times when you want a particular feature that is not available through the expert. For example, supose you wanted cells with a value greater than a certain amount to be shaded, or a different color. Or suppose you wanted the order of the columns to change depending a value selected at run time. The way handle this is to create a 'manual cross tab'.

1) Group the report on the field(s) that will control the row element of the cross tab.

2) Suppress the detail section. Since a cross tab, by definition is a summary report (no detail records shown) you have no need for this section.

3) Suppress either the group header or the group footer section(s). I like to suppress the group header but that is just because I am used to it.

4) Use the text object icon to create the column headings. Place these in the page header.

5) Place the field holding the row name in the group footer section. For example, if you grouped on department, place the field with the department name in the group footer.

6) Create a formula field for each column. For example, if I wanted to count the number of employees in each department who had less than 5 years of tenure, 5 to 10, 11 to 20 or 20 plus I would create the following formulas:

//col1kount
if {@tenure}<5 then 1 else 0

//col2kount
if {@tenure} in 5 to 10 then 1 else 0

//col3kount
if {@tenure} in 11 to 20 then 1 else 0

//col4kount
if {@tenure}>20 then 1 else 0

We are creating indicator fields.

If I wanted to sum the salaries of employees in each of the four groups I would create the following:

//col1sum
if {@tenure}<5 then {table.salary} else 0

//col2sum
if {@tenure} in 5 to 10 then {table.salary} else 0

//col3sum
if {@tenure} in 11 to 20 then {table.salary} else 0

//col4sum
if {@tenure}>20 then {table.salary} else 0

7) Now place each of the field in the (suppress) detail section. Position them under the correct column headings.

8) Use normal Crystal procedures to create subtotals of each of the fields. Create grand totals as well. The subtotals will automatically go into the group footer.

9) (optional) you can now click on each of the subtotals, right click and pick 'format' and apply conditional formatting as required.

Howard Hammerman, Ph.D.
Hammerman Associates, Inc.
http://www.hammerman.com
800-783-2269
Hammerman Associates, Inc. provide Crystal Reports training,
consulting, course material, utilities and software. Consultants are available throughout North America for short or long-term assignments.