Working With PivotCharts

Background

PivotCharts and PivotTables are similar in that they both allow you to manipulate how your data is presented and analyzed. PivotTables can be the foundation of PivotCharts. If you are not familiar with PivotTables, see Working with PivotTables first. PivotCharts are a useful way to visualize data that appears in PivotTables.

Let's take a look at how to add a PivotChart (and PivotTable) to a Excel report for OfficeWriter.

Setup

Icon

This example will use the AdventureWorks sample database shipped with SQL Server Reporting Services. We assume you already know how to create a basic report in Excel using Officewriter Designer. If you don't know how to do this, first read Quick Start.

This query returns employee pay rate and department data from the AdventureWorks database. This is the data that will be used in the report.

Below is a screenshot of an Excel template with data markers that will be populated with the data from the above query.

Creating a PivotChart

On a sheet, different than the sheet with the data markers, select the cell where you would like to place the PivotTable for the PivotChart. A PivotChart is powered by an underlying PivotTable, so you will need to create a PivotTable at the same time as the PivotChart.
# Go to Insert > Pivot Chart on the Excel ribbon.
\\\
# This will insert a blank PivotChart and PivotTable into the worksheet. You may notice that the labels in the Pivot Fields box have changed to reflect that we are designing for a PivotChart (e.g. Row labels >> Axis labels)

# Drag the Department pivot field into the Report Filter section. Drag Title into Axis Fields, Gender into Legend Fields, and Base Rate into Values. When you are done you should have a PivotChart that looks like this:
!setup_pivotchart.png!
# The default PivotChart type is a basic column chart. To change the chart type, select the PivotChart and then go to Change Chart Type in the PivotChart tools ribbon (which is available when a PivotChart is selected).
!changecharttype.png!
# Let's change the chart type to 3D Clustered Column.
!switchcharttype.png!
\
Now that you have a basic PivotChart in your report, you will need to make some changes to the field settings and PivotTable options before the template is finished.

Field Settings

Just as we did for the PivotTable in Working with PivotTables, we will want to change the Values computation from Count to something more useful.

Right click on Count of Base Rate in the Values section in the Pivot Fields menu and switch the calculation to Average.
\

Refreshing the Data

We mentioned earlier that PivotCharts are powered by underlying PivotTables. To make sure that the new data is pulled into the PivotTable (and therefore the PivotChart), the data source needs to refresh when the workbook opens.

Right click the PivotTable or PivotChart and select PivotTable Options from the drop-down.
# Go to the Data tab
# Check off Refresh data when opening the file
# To avoid pulling the data markers into the filters of the final report, select None from the Number of items to retain per field dropdown.
!pivottable_options.png!
# Click OK when you are finished.

Your PivotChart is now finished and should look like this:
!final_template_chart.png!

Viewing the PivotChart

Publish, and View your report.

Icon

When using the OfficeWriter Designer View functionality, you may be prompted to re-deploy your report even if you have not made changes since the report was last deployed or retrieved. This is because when the report is opened or deployed, Excel considers this a workbook open event, so the PivotTable refreshes.

Although there are no visible changes to the report, Excel treats the refreshed PivotTable as a change to the file. Any time Excel detects changes to the file, it will prompt you to redeploy the report before viewing in the Designer.

Look at the sheet containing your PivotChart and PivotTable. You should have the Titles across the bottom of the graph but it is not very pretty. It contains all the data.

Open the drop-down list of departments and select one to filter the PivotChart display.

Now you get a clear graphical picture of the data that visually presents the wage differences between males and females by position across departments.

You'll also notice that the PivotTable has been filtered too. Since the PivotChart displays the data that is in the PivotTable, when you change the view in the PivotChart, the PivotTable updates accordingly as well.

Viewing the Report in Reporting Services Report Manager

In your browser, type the path to the Report Manager (Usually http://<YourReportServer>/Reports). Navigate to the report and view it.

To see the report as you designed it with OfficeWriter:

From the export option drop-down list, choose Excel designed by Officewriter.
# You will be prompted to save or open. If your report contains a PivotTable/PivotChart AND you are using Internet Explorer 8 or earlier, you MUST select Save. After it saves to disk, then you may select Open to view it.
\This is because of an issue with how Internet Explorer caches temporary internet files. If you are using any other browser or Internet Explorer 9 or later, this issue does not occur and you can open the file directly.

Space Shortcuts

Page tree