Create a pivot table

Create a pivot table in a report, add dimensions and measures, and set additional properties as needed.

  1. Open a new or existing report in Report Designer.
  2. If you are creating a new report, specify the data schema to be used for the report.
  3. From the Tool Box view, drag and drop a Pivot Table to the report structure under the Page Root.
    A Hierarchy Value (representing a dimension) and Fact are added as children of the Pivot Table, with a Measure as a child of the Fact, as in Figure 1.
    Figure: New Pivot Table Report Structure

    This figure shows an example of a Report Structure when a new Pivot Table is added.
  4. If required, add additional dimensions by dragging the Pivot Table Hierarchy Value object from the Tool Box and dropping it into the Pivot Table.
  5. Select each Hierarchy Value and edit its properties:
  6. If required, add additional Facts and Measures by dragging the objects from the Tool Box and dropping them into the Report Structure. Measures are used to aggregate numeric values and must be placed under Facts.
  7. Select each Measure and edit its properties:
    • Value - Column name for the measure.
    • Numeric Column- If this check box is selected, the measure is Numeric. If this check box is not selected, the measure is a String.
    • Title - Title for the measure.
    • Format - The output format of the measure. Only required if the dimension is Numeric.
  8. Set additional properties for the Pivot Table as required:
  9. In the Structure View, arrange the dimensions and measures so that the values are triggered at the desired point in the data stream. See Figure 2 for an example.
    Figure: Pivot Table Dimensions in the Structure View

    This figure shows an example of a Report Structure where the dimensions are arranged such that the userid Dimension is under the Group userid trigger and the orderid Dimension is under the Group orderid trigger.

    For further details, see Organizing pivot table elements in the Structure view.

  10. Save the report.
Note: To create a dynamic pivot table, first define the static pivot table and then use the Pivot library APIs to dynamically configure it at runtime based on user-supplied information. An example can be found in the DynamicPivotTable report in the OrderReport demo (see Sample pivot table reports).