A complex pivot report contains a tablix with added groups.
This tutorial guides you through the steps to create a complex Pivot report by adding column groups to a tablix.
Note: You need to complete Create a Simple Pivot Report before performing following steps.
After you complete this tutorial, you will have a report that looks similar to the following.
After creating the simple Pivot report, let us add two groups to the MediaType column group - an adjacent group and a child group.
To add an adjacent group,
To add a child group,
To add fields to the newly added groups,
To obtain the month value of sale date,
{MonthName(Month(SaleDate))}
To display the month wise aggregate price,
{Sum(Price)}
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.
To manage how the added group data is rendered across rows and columns,
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.
To display the month names once for SaleDate column group,
To display only the first three months of the year, that is, January, February, and March.
To sort the month names in the increasing order of month numbers,
{Month(SaleDate)}
To manage the text and labels that appear in cells,
To merge cells with adjacent empty cells,
Note: You may need to resize and reposition the controls on the report to accommodate data, and for a cleaner look.
Customize the body area and group area of the Tablix data region using the TEXT - Text Align (Left, Right, Center, and Justify.) and TEXT - Vertical Text Align (Top, Middle, or Bottom) properties. By default, these properties are set to General and Top.
Note that background color and the font weight properties are already applied, same as the Media Type label.