ۥ-x@ -+6w)444444 55<L5L5L5L5 X5 b5L5x5B5555555555555555465455 Crystal Reports Technical Document Subject: Cross Tab Reports Date: September 27, 1993 Versions: 2.0(Std), 2.0(Pro), VB (Please ignore the steps that say this is what the report should look like, the screen images were not included with this document.) Creating crosstab reports: A crosstab report is a report that cross tabulates and summarizes data in a row and column format similar to a spreadsheet. For example, assume the following data: Product Salesrep Amount 1001 01 150 1001 02 100 1001 03 125 1001 04 200 1002 01 175 1002 03 200 1002 04 100 1003 01 250 1003 02 225 1003 03 75 1003 04 250 A crosstab report could present the data in the following format: Product 1001 Product 1002 Product 1003 Sales Rep 01 150 175 250 Sales Rep 02 100 225 Sales Rep 03 125 200 75 Sales Rep 04 200 100 250 Total for product 575 475 800 To set up such a crosstab report, you use the Crystal Reports formula capabilities. 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. {file name} w**+ +++  PRln}Y[suwQS NP*qs+ - O S U  0*0*0*0* 0* h 0* h  hP %jl 46~  OQv^``b46w y -!/!!!q"s"""####1$3$T$f$m$s$u$&&n'p'#(%(j(l((())***+++0* 0* h0*0*0*0*0*S+ + ++ 0* h!-TECHDOC;B 4$)MO)+4` )"+ ++BTimes New Roman Symbol&Arial5Courier New 1Courier`)`) 55`)`)"h%% uD:\WINDOWS\WORD\TDTEMPLT.DOTCross Tab ReportsCross Tab Reportscrosstab; cross; tab; xtab%See CROSS.ZIP and VBCROSS.ZIP on BBS.Duncan Vincent Armitage Allan Moore