Simple Pivot Report

A simple pivot report contains a Tablix data region to display the summarized information. This summarized information might contain totals, averages, or other statistics grouped in a meaningful way.

This tutorial guides you through the steps to create a simple pivot using the Tablix data region with the default row and column groups.

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

Bind Data to Report

1. Create a new report. By default, you have a blank RDL Report.

2. Connect to a data source.

   1. Go to the Data tab and click Add.

   2. In the DataSource dialog, provide the following URL as Endpoint for the 'Remote JSON' provider:

   https://demodata.grapecity.com/northwind/odata/v1/
   

3. Add a dataset.
   We will be fetching data from multiple data tables.

   1. Click Add Data Set for the added data source.

   2. In the DataSet dialog, enter the path in Uri/path: OrderDetails.
      

   3. In the Parameter property, click Add Items and enter the Key/Value pair for it.

      • Key: $expand , Value: Order,Product

   4. In the Calculated Fields property, click Add Items and enter the Key/Value pairs for it.

      • Key: OrderYear , Value: =Year(Fields.Item("Order.OrderDate").Value)
      • Key: OrderQuarter , Value: =Quarter(Fields.Item("Order.OrderDate").Value)

   See Databinding for information on connecting to data sources and adding datasets.

Create Report Layout

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

   

   Note that a Tablix Wizard automatically appears when you drop the data region onto the 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 product names to be displayed along the rows, order years along with the columns, and the total price corresponding to these.

1. On the Tablix Wizard, drag and drop the Product.ProductName field onto the Rows area which forms the Tablix row group.

2. Drag and drop the OrderYear field on the Columns area which forms the Tablix column group.

3. Similarly, drag and drop the UnitPrice field onto the Values area, which is the body of the tablix.
   Note that the 'UnitPrice' field changes to =Count([UnitPrice]), which we will update later.

4. Click OK to save the changes.

   

   A table with its columns bound to the fields is created.

Alternatively, you can first drag-drop the Tablix data region onto the design area of the report and then populate the data by dragging and dropping the fields onto the row and column groups, and the body area.

To display the aggregate price for the products,

1. Select the body area containing UnitPrice field.

2. Go to Properties pane and select the radio button next to Value property.

3. Select Expression... from the drop-down and enter the following expression in the Expression Editor dialog, =Sum(Fields!UnitPrice.Value * Fields!Quantity.Value).

   The Tablix data region should now look as shown.
   

To display the Totals for the column in the Tablix data region,

1. Click the Open Tablix Wizard icon corresponding to the Tablix data region.

2. In the Tablix Wizard, check the Totals for Columns option to display the total price for the order years.

   The Tablix data region should now look as shown.

To provide labels to the column headers,

1. Select the textbox containing 'Product Product Name' and modify it to Product Name.

2. Similarly, select the textbox containing Total and enter Value as Total Price for the Year.

   The Tablix data region should now look as shown.

Add Page Header

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

   

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

3. Click inside the text box and enter the text 'Orders by Year'.

Customize the Appearance of the Pivot Report

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 for the Page Header using the Background - Color property (#f1f1f1).

2. Set the text alignments for the textboxes in the tablix using TEXT - Text Align (Left, Right, Center, etc.) and TEXT - Vertical Text Align (Top, Middle, or Bottom) properties.

3. Set the font for the textboxes in the page header area and body area to 'Song' using the Text - Font Family property.

4. Set the color for the text in Page Header using the TEXT - Color property to set the font color (#3da7a8) and font size using the TEXT - Font Size property (16pt).

5. Set the display format to currency in the price fields - select the textboxes containing the price fields and set TEXT - Format to 'Currency'.

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.