Web Designer
Get Started / Tutorial 5: Create Pivot Report / Tutorial 5.2: Create a Complex Pivot Report
In This Topic
    Tutorial 5.2: Create a Complex Pivot Report
    In This Topic

    A complex pivot report contains a tablix with additional adjacent rows/columns or parent or child row/column groups to represent summary values of the numerical data. This tutorial guides you through the steps to create a complex pivot report by adding column and row groups to the tablix created in Tutorial 5.1: Create a Simple Pivot Report.

    After you complete this tutorial, you will have a report that looks similar to the following.

    An Example for Complex Pivot Report
    (open the gif in new tab to view it full-size)

    Add Row and Column Groups

    Let us add row and column groups to the existing simple pivot report. We will add a child group {Quarter(OrderDate)} to the {Year(OrderDate)} column group to display total prices for each quarter of a year. Similarly, we will add a parent group 'ShipCountry' to ShipName row group to show countries and group the ship names according to the country.

    To add a child group {Quarter(OrderDate)},

    1. Right-click the {Year(OrderDate)} column group area to view options in the context menu.
    2. Go to Column Group and select Child.
      Complex Pivot Report
    3. With the child group selected, go to the Properties pane > Tablix Member.
    4. In the Group Expressions property, click Show Items and enter the field value:
      {Quarter(OrderDate)}.
    5. Now, switch the tab to TextBox properties and enter Value:
      Q:{Quarter(OrderDate)}.

    To add a parent group {ShipCountry},

    1. Right-click the {ShipName} row group area to add a parent group from the context menu.
    2. Go to Row Group and select Parent.
      Complex Pivot Report
    3. With the parent group selected, go to the Properties pane > Tablix Member.
    4. In the Group Expressions property, go to Show Items and enter the field value {ShipCountry}.    
    5. Now, switch the tab to TextBox properties and enter Value as {ShipCountry}.
    6. Provide a label 'Ship Country' to the column displaying the ship country.

    Add Totals to the Groups

    We will add a totals row to display the sum of freight charges for each country and a subtotal column to display the total freight charges for each quarter of the order year.       

    1. Click Dragging the Tablix data region onto the design areaon the top-right corner of the tablix data region to open the Tablix Wizard.
    2. On the Tablix Wizard, under the Totals area in the Layout Design, check the Totals for Row Groups and Sub Totals for Column Groups.
      Tablix Totals
    3. Click OK to save the changes.
    4. You will observe that your tablix would now look like the following.
      Tablix Totals
      Note that on adding totals, the labels for the data fields in the Tablix data region are renamed, which we will rename in the next steps.

    Customize the Appearance of the Pivot Report

     Tablix Totals

    Preview and Save Report

    1. Click Preview Preview Button to view your report in the final output.
    2. Exit the preview mode by clicking Back on the left side of the designer.
    3. Click Save to open the Save dialog box. Enter the report name and click Save Report.
    See Also