Example - Add Aggregate Data to a Crosstab

You are a report author at The Sample Outdoors Company, which sells sporting equipment. You are requested to create a report showing sales by order method to determine which methods are generating the most revenue and the highest sales volume.

Procedure

  1. Open IBM® Cognos® Report Studio with the GO Data Warehouse (query) package.
  2. In the Welcome dialog box, click Create a new report or template.
  3. In the New dialog box, click Crosstab and click OK.
  4. From the Source tab source tab:
    • Expand Sales and Marketing (query) and Sales (query).
    • Expand Product and double-click Product line to add it as rows.
    • Expand Order method and double-click Order method to add it as columns.
    • Click Product type and drag it just to the right of Product line.

      Product type is now nested in Product line.

    • Expand Sales fact and drag Quantity to the Measures drop zone to add it as a measure.
    • Drag Revenue to the right of Quantity to add it as a second measure.
      Tip: Drop Revenue into the crosstab when you see a vertical bar between the Product type column and the Order method column.
  5. Click any part of the crosstab, and then click the select ancestor button select ancestor icon in the title bar of the Properties pane.
  6. Click Crosstab.
    Tip: You can also click the container selector (three orange dots) of the crosstab to select it.
  7. In the Properties pane, double-click the Font property.
  8. In the Size box, click 8pt and click OK.
  9. Click one of the measures.
  10. Click the summarize button aggregate icon and click Maximum.

Results

When you run the report, you can see that for camping equipment, Cooking Gear generated the highest sales volume for the Special order method and Tents generated the most revenue. Tents ordered by the Web order method generated the largest revenue.
Figure 1. Crosstab showing quantity and revenue by order method by product line and product typethe final crosstab showing quantity and revenue by order method by product line and product type
Parent topic: Crosstabs