INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Report Development

Creating a manual cross tab report by HowardHammerman
Posted: 27 Oct 01

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.

Back to Business Objects: Crystal Reports 4 Other topics FAQ Index
Back to Business Objects: Crystal Reports 4 Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close