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

Manual Crosstabs Revisited by Roadkill150
Posted: 14 Mar 02

This is the Method that Crystal Support would employ for those who needed to create a manual crosstab. A lengthy process to say the least.

To set up a crosstab report:

We'll set up a crosstab report using sample data that was installed when you installed Crystal
Reports Professional Edition (unless you elected not to install Samples and Examples).

NOTE: This example contains many steps because it covers some sophisticated
reporting capabilities. We're convinced, however, that you'll find the report to be very
easy to set up.

To set up our example crosstab report using our example data:

1.    Select File|New, and select xtab.dbf from the C:\CRW directory (or the directory you specified
at installation).

2.    When the Report Editor appears, we'll begin by creating some simple formulas. We'll use the
formulas to limit the data that appears in each column on the report.

3.    Select Insert|Formula Field. The Insert Formula dialog box appears.

4.    Enter the name Product 1001 for the first formula and Click OK when finished. The Formula Editor appears.

5.    Enter the following formula in the Formula Text box. (See Formulas -- an overview if you need
assistance.)

    if {xtab.PRODUCT} = "1001" then
        {xtab.AMOUNT}
    else
        0

This formula will be used to create the Product 1001 column of the report. It prints the value from the AMOUNT field in that column only if the value "1001" appears in the PRODUCT field. Thus, the values that will appear in the Product 1001 column will be either amounts relating to sales of Product 1001 or zero (0). By later suppressing all zero values, we will
be left with a column that contains only sales data on Product 1001. We'll copy this formula and modify it to create the product 1002 and Product 1003 columns as well.

6.    Highlight the entire formula and press Ctrl+Ins to copy it to the Windows clipboard.

7.    Click the Accept button and place the formula field box in the Details section of your report.

8.    Select Insert|Formula Field again. The Insert Formula dialog box appears.

9.    This time, enter the name Product 1002 for your formula name and Click OK when finished. The Formula Editor appears.

10.    Paste the original formula in the Formula Text box using the Shift+Ins keyboard combination.

11.    Change the value 1001 in the formula to 1002, Click the Accept button, and place the formula field box in the Details section of your report, just to the right of the Product 1002 field box.

12.    Select Insert|Formula Field a third time, enter the name Product 1003 for your formula name, paste the original
formula in the Formula Text box, and change the value 1001 to 1003.

13.    Click the Accept button when finished, and place the formula field just to the right of the other two field boxes. The Report Editor should look like this:

If you print your report to the print window, you'll see that the values you want are in their appropriate columns, but the data isn't lined up the way you want it in your final report. In order to get the data lined up properly, we're going to subtotal the data in each of the columns so that a subtotal prints whenever the salesrep changes. By setting up a subtotal on each change of salesrep, we will be duplicating the data that prints in the Details section. When we later hide the Details section, however, we will eliminate the duplication.

14.    In order to see what we're doing, select Edit|Show Field Names. This displays the field or formula name for each field in its field box.

15.    Select the @Product 1001 field box, Click the right mouse button, and select Insert Subtotal from the pop-up menu. The Insert Subtotal dialog box appears with the value xtab.PRODUCT in the top scroll box.

16.    Click the scroll arrow on that scroll box, and select SALESREP from the Database Fields section of the scroll list. Leave the value in the second scroll box set at "in ascending order", and Click OK when finished. Crystal Reports creates a group section (#1: SALESREP -- A) and places a subtotal field box in that section in the Product 1001 column.

17.    Select the @Product 1002 field box, Click the right mouse button, and select Insert Subtotal from the pop-up menu. The Insert Subtotal dialog box appears, and this time the value in the scroll box is Group #1: xtab2.SALESREP -- A (all subtotals that you enter in the Group 1 footer section will now use the SALESREP field as the sort and group by field). Click OK to accept it.

18.    Repeat Step 17 with the @Product 1003 field box. When finished, the Report Editor should look like this:

19.     To total the data in each column, we'll use the Insert|Grand Total command. Select the @Product 1001 field box again, Click the right mouse button, and this time, select Insert|Grand Total from the pop-up menu. The Insert Grand Total dialog box appears with the word sum in the scroll box. Since we want to create a grand total (sum) of all the data, Click OK to accept the dialog box as it is. The program creates a Grand Total section and places the grand total field box in that section at the bottom of the Product 1001 column.

20.    Repeat Step 19 with the Product 1002 and Product 1003 field boxes as well. When finished, the Report Editor should look like this:


21.    Now we need to hide the Group Header Section, because it is not needed, and to hide the Details section to get rid of the duplicate data (remember, the subtotals duplicate the data in this section). Click the gray part of the Group Header section for #1: SALESREP -- A (the section right above the Details section), Click the right mouse button, and select hide from the pop-up menu. Crystal Reports hides that section of the report.

22.    Do the same thing with the Details section of the report. Since you have fields in that section, the section remains on screen but it becomes grayed-out to indicate that it is hidden.

23.    Select Print|Print to Window (or Click the Print to Window button) and your report prints. It's
looking good except for the zero value in the Product 1002 column.

24.    Close the print window, Double-Click the of @Product 1002 field box in the group footer section (#1: SALESREP -- A), and the Field Format dialog box appear.

25.    Click the Format Number button at the bottom of the dialog box, activate the Suppress if Zero checkbox in the Format Number dialog box when it appears, and Click OK when finished Click OK in the Field Format dialog box to return to the Report Editor.

26.    Print your report to a print window again and this time you can see that the data is in the desired format with blanks, not zeros, for missing data.

27.    Close the print window and we'll label our data.

28.    Since the Grand Total data represents the grand total for each column, we'll label that data Total for product:

29.    Select Insert|Text Field and type Total for product: in the Enter text box. Click Accept when finished and place the text field box to the left of the other field boxes in the Grand Total section.

30.    With the cursor inside the Total for product field box, Click the right mouse button, select Change Font, and change the font to Bold Italic when the Font dialog box appears.

31.    We also want to change the font for the column titles, so select the Product 1001, Product 1002, and Product 1003 text field boxes using the Shift+Click key combination.

32.    With the cursor inside any of the these field boxes, Click the right mouse button, select Change Font, and change the font to Bold Italic.

This is a good time for saving the report so select File|Save and save the report as xtab.rpt.

All that remains is putting in the labels for each of the sales reps so the amount per sales rep per product is clearly identified. We'll do this using stacking formulas.

33.    Select Insert|Formula Field, enter Sales Rep 01 in the Insert Formula dialog box, and Click OK when finished.

34.    Enter the following formula in the Formula text box:

    if {xtab.SALESREP} = "01" then
        "Sales Rep 01"
    else
        ""

The @Sales Rep 01 formula says that if the value in the SALESREP field is "01", print the label "Sales Rep 01". If the value is anything other than "01", enter nothing (as signified by the empty string ""). We're going to create a similar field for each of the other sales reps as well and stack them one on top of the other. When the value "01" appears in the SALESREP field, the @Sales Rep 01 formula will print out a label to identify it and the other three formulas won't print anything. When the value "02" appears in the SALESREP field, the @Sales Rep 02 formula prints the label, and so forth.

35.    We want to copy the formula so we can reuse it later, so highlight the entire formula and use the Ctrl+Ins key combination to copy it to the Clipboard.

36.    Click Accept when you're finished, and place the formula field box in the group footer section (#1: SALESREP -- A), immediately above the Total For Product: text field box.

37.    Change the font for the @Sales Rep 01 field box to Bold Italic.

38.    To create the next formula, select Insert|Formula Field, name the formula Sales Rep 02, and Click OK to call up the Formula Editor.

39.    Paste the @Sales Rep 01 formula in the Formula text box, and change both instances of "01" to "02". Click Accept when finished, place the formula field box directly on top of the @Sales Rep 02 formula field box, and change the font to Bold Italic.

40.    Repeat Steps 38 and 39 to create, stack, and change the font for the formulas @Sales Rep 03 and @Sales Rep 04. The Report Editor should look like this when you're finished:

Your crosstab report is complete --print it to a window and review your work.

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