[]
V6.1
(Showing Draft Content)

Simple Pivot Report

A simple Pivot report contains a Tablix data region. This report uses the default row and column groups to display data.

What You Will Learn

This tutorial guides you through the steps to create a simple Pivot report using Tablix data region.

Note: This tutorial uses a sample data set - CustomerOrders.

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


Simple Pivot Report Example

Create Report Layout

Drag and drop Tablix data region Tablix data region onto the design area of the report.

Tablix data region on design area

The Tablix Wizard assists in designing the Tablix. You can bind the data, and use some quick layout options to help you set styles, organize data, and add totals for the data values.

Bind Data to Tablix Data Region

Let us now populate the Tablix data region with data. We want the titles to be displayed along the rows, different media types along the columns, and the total price corresponding to these.


Tablix Data region with Bound Data

  1. On the Tablix Wizard, click the drop down next to the CustomerOrders data set to view fields in the added data set.

  2. Drag and drop the Title field onto the Rows area (row group) which forms the Tablix row group.

  3. Drag and drop the MediaType field on the Columns area (column group) which forms the Tablix column group.

  4. Similarly, drag and drop the Price field onto the Values area, which is the body of the tablix.

    Note that the Price field changes to the aggregate price, with following expression: Sum(Price)

  5. Click OK.

    The Tablix data region should now look as shown below:

    Tablix Data region with Bound Data

Add Labels to Tablix Data Region

Let us add a static row to display labels (static text) for the column group,

  1. Right-click the column group area to view the options in the context menu.

  2. Go to Row and select Outside Group - Above.

    Adding Row group to the Tablix data region

    Now the tablix in the design area looks as shown.

    Tablix Data Region with a Row Group

To merge the cells in the tablix corner area (top left),

  1. Select the cells in the tablix corner area using mouse drag or simply using Ctrl+Click combination.

  2. Right-click the selected cells to view the options in the context menu, go to Cells, and select Merge Cells.

    Merge Tablix Cells

To provide labels to the columns,

  1. Enter text 'Prices by Media Type'.

  2. Select the text box above the MediaType column group and enter the text 'Media Type'.

    Now the tablix has column labels as shown.

    Tablix with column labels

Manage Data

In Tablix data region, you need to manage how a grouped data is rendered across the rows and columns. This is done by using Group Expressions as explained below.

  1. From the Explorer Report Explorer Icon, select the Tablix data region.

  2. Click the Group Editor icon Group Editor Icon to display row groups and column groups.

To display the titles once for every row group:

  1. Select Tablix1_Title1_Group row group and go to the Properties pane.

  2. In the Group Expressions property, click Show Items and then select Add Item. You will see an item is already added for the Title field.

    Group Expression property

To display the media types once for every column group:

  1. Select Tablix1_MediaType1_Group column group and go to the Properties pane.

  2. In the Group Expressions property, click Show Items and then select Add Item. You will see an item is already added for the MediaType field.

    Group Expression property

Add Page Header

  1. Go to the Report tab on top of the designer and select Add Header option.

    Add Page Header

  2. Drag and drop a TextBox control Textbox Control in the page header area.

  3. Click inside the text box and enter the text 'Customer Orders Price List'.

Customize the Appearance of the Pivot Report

Customized Appearance of the Pivot Report - Example

Note: You may need to resize and reposition the controls on the report to accommodate data, and for a cleaner look.

  1. Set the BACKGROUND - Color property for the page header and header row of the table to '#f1f1f1'.

  2. Align the text in the textboxes 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.

  3. Set the font size for the text in the page header to '14pt' using the TEXT - Font Size property.

  4. Specify the border properties for the textboxes in the table using the BORDER - Width, Border - Style, and BORDER - Color properties. Set the border values to '0.25pt', 'Solid', and '#f1f1f1', respectively.

  5. Change the TEXT - Format property for the Price field to 'Currency'. This property displays the price values in currency format.

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 top left side of the designer.

  3. Click Save to open the Save dialog box. Enter the report name and click Save Report.