A complex pivot report contains a tablix with added groups.

What You Will Learn

This tutorial guides you through the steps to create a complex Pivot report by adding column groups to a tablix.

• Add Groups
• Bind Data to the Groups in Tablix Data Region
• Manage Data
• Improve the Layout of Tablix Data Region
• Customize the Appearance of the Pivot Report
• Preview and Save Report

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

Add Groups

After creating the simple Pivot report, let us add two groups to the MediaType column group - an adjacent group and a child group.

1. To add an adjacent group,

2. Right-click the MediaType column group area to view options in the context menu.
3. Go to Column Group and Select Adjacent-After.
   

   To add a child group,

4. Right-click the MediaType column group area to view options in the context menu.
5. Go to Column Group and select Child.
   Now, the tablix looks similar to the following.
   

Bind Data to the Tablix Data Region

1. To add fields to the newly added groups,

2. Drag and drop the Country field on the child group area.
3. Drag and drop the SaleDate field on the group adjacent to MediaType column group.

   To obtain the month value of sale date,

4. Select the SaleDate group.
5. Go to the Properties pane and change the Value to =MonthName(Month(Fields!SaleDate.Value)).

   To display the month wise aggregate price,

6. Drag and drop the Price field onto the body of the tablix.
7. Select the body area containing Price.
8. Go to Properties pane and change the Value to =Sum(Fields!Price.Value). Now the tablix looks similar to the following.
   

Manage Data

Since the data in a tablix expands across both rows (vertically) and columns (horizontally), you need to add groups and filters to manage such a large amount of data.

1. To manage how the added group data is rendered across rows and columns,

2. From the Explorer , select the Tablix data region.
3. From the left pane of the designer, click the Group Editor icon  to display row groups and column groups.

   To limit the horizontal data in tablix, let us add filter to MediaType column group so that it displays only two types of media - VHS and DVD.

4. Select MediaType (Tablix1_ColumnGroup1) column group and go to the Properties pane.
5. In the Filters property, click Show Items, and then select Add Item.
6. Click  to display the filter properties.
7. Click the radio button next to Filter Expression to display the fields and select the MediaType field.
8. Click the drop down next to Operator and select 'In'.
9. Click Show Items to open Filter Values.
10. Click Add Items and enter text 'VHS'.
11. Click Add Items again and enter 'DVD'.

    To display the countries once for every MediaType column group,

12. Select Country (Tablix1_ColumnGroup2) column group and go to the Properties pane.
13. In the Group Expressions property, click Show Items and then select Add Item.
14. Click the radio button to display the fields and select the Country field.

    To display the months once for SaleDate column group,

15. Select SaleDate (Tablix1_ColumnGroup3) column group and go to the Properties pane.
16. In the Group Expressions property, click Show Items and then select Add Item.
17. Enter the expression =Month(Fields!SaleDate.Value).

    To display month in the order of occurrence,

18. Go to Sort Expressions property, click Show Items, and then click Add Item. 
19. Enter the expression =Fields!SaleDate.Value. Let the sort order be ascending (default).

Improve the Layout of Tablix Data Region

1. To manage the text and labels that appear in cells,

2. Right-click the cell with text 'Media Type' to view options in the context menu.
3. Select Split Cells. Now we have a new cell adjacent to cell with 'Media Type'.
4. In the new cell, enter the text 'Months' to give label to the SaleDate column group.

   To merge cells with adjacent empty cells,

5. Select the SaleDate column group and the empty cell below it, right-click, go to Cells and select Merge Cells.
6. Similarly, merge the corner area (top left area) of tablix with the empty cell below it.
7. In the above merged cell, change the text to 'Prices by Media Type and Months'
   

Customize the Appearance of the Pivot Report

1. To customize the body area and group area of the tablix, set the following properties.

   Property	Value
   Body area (=Sum([Price]) ) below SaleDate column group
   BORDER - Style	Solid
   TEXT - Text Align	Center
   Column group area (=[SaleDate]) and Child group (=[Country])
   BORDER - Style	Solid
   TEXT - Text Align	Center

2. To customize the Months label, set the TEXT - Text Align property to Center. Note that background color and the font weight properties are already applied, same as the Media Type label.

Preview and Save Report

1. Click Preview  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.