Mastering Business Analysis with Crystal Reports 9 (Wordware Applications Library)
|
Cross-Tab Inventory Report
See the companion files (www.wordware.com/files/crystal) for an example of this report. Create this report with the Cross-Tab Report Creation Wizard. This report is created with the xtreme.mdb database.
| Table(s) | Fields |
|---|---|
| Product | Product Name |
| Product_Type | Product Type Name |
| Purchases | Units in Stock Units on Order |
Use the Smart Linking feature to link these tables. You'll want to make sure they're linked as follows:
-
Product.Product ID = Purchases.Product ID
-
Product.Product Type ID = Product_Type.Product Type ID
When you get to the Cross-Tab screen, place the Product Type Name field in the Rows list box, the Product Name field in the Columns list box, and the Units in Stock and Units on Order fields in the Summary Fields list box. Your screen should look like Figure P2-1.
Once you've defined the fields in the Cross-Tab screen, press the Next button. Then perform the following steps:
-
In the Chart screen, select the No Chart option button.
Once you've done this, press the Next button.
-
In the Record Selection screen, add the Product Type Name and the Product Class fields (both from the Product table) to the Filter Fields list box.
-
In the Record Selection screen, select the Product Type Name (located in the Filter Fields list box). Use the drop-down field below the Filter Fields list box and select the following: is not like.
-
In the second drop-down field, type in the following: Xtreme*.
Your screen should look like Figure P2-2.
Figure P2-2: Defining a filter for the Product Type Name field -
In the Record Selection screen, select the Product Class (in the Filter Fields list box). Use the drop-down field below the Filter Fields list box and select the following: is equal to.
-
In the second drop-down field, select Accessory.
Your screen should look like Figure P2-3.
Figure P2-3: Defining a filter for the Product Class field Once you've done these steps, press the Next button.
-
In the Grid Style screen, select the Silver Sage 1 option.
Once you've done this, press the Finish button. Your report displays, as shown in Figure P2-4. (The Silver Sage 1 option places a pale green background behind the text.)
Figure P2-4: The start of a cross-tab inventory report -
From the menu bar, select Insert, Group.
Create a group using the Product Type Name.
-
Right-click in the Group Header section.
An options menu displays.
-
Select Insert Section Below from the options menu.
A Group Header b displays.
-
Left-click in an area above the text "Total" (which is part of your cross-tab).
The entire cross-tab is selected.
-
Drag the cross-tab into the Group Header b section.
-
Right-click in the Group Header a section.
An options menu displays again.
-
From the options menu, select Suppress (No Drill Down).
-
Stretch the Product Type Name headers so that the entire name displays.
Figure P2-5 displays what your screen should look like.
Figure P2-5: Formatting the cross-tab report -
Right-click the Sum of Purchases.Units in Stock object associated with the Product Name.
If you're not sure which object is the Units in Stock sum, select the Design tab. The names of your objects are displayed. This field has a white background.
Once you right-click on this object, an options menu displays.
-
Select Format Field from this options menu.
The Format Editor displays.
-
In the Format Editor, select the Border tab.
In the Border tab, check the Background check box (located in the Color section), and select the color Yellow from the drop-down field next to the Background check box, as shown in Figure P2-6.
Figure P2-6: Defining a color for a report object -
Press the OK button on the Format Editor.
If you preview your report, you'll notice that all Units in Stock sums display with a yellow background.
-
Right-click the Sum of Purchases.Units in Order object associated with the Product Type.
This field has a green background.
-
With this object selected, select Format, Highlighting Expert from the menu bar.
The Highlighting Expert displays, as shown in Figure P2-7.
Figure P2-7: The Highlighting Expert -
In the Highlighting Expert, press the New button.
This creates a new highlighting item.
-
In the Item Editor (located on the right side of the screen) define the following:
-
The Value of section should read: this field is less than or equal to 0.00
-
Change the Background color to red
Figure P2-8 displays what your defined Highlighting Expert should look like. This will highlight any values that meet the criteria you've just defined. In this example, should a Units in Stock number be less than or equal to 0.00, the number will display in red.
Figure P2-8: Defining the Highlighting Expert -
-
Press the OK button in the Highlighting Expert.
You're returned to the Report Design area.
To complete this report, you'll just need to perform a couple of formatting tasks. First, create a text object, and place this object within the Report Header section. This object will be your title. Type the following into this text object: Inventory Report. You'll probably want to stretch this object as necessary and also increase the size to about 18 and add a bold font.
Next, move the Print Date field into the Report Header section. You can place this field directly below your title. Finally, go to File, Printer Setup and change the type of paper this report uses to Legal. Once you've done that, preview your finished report. Figure P2-9 displays the result of this finished report.
|