.
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.
Without SoftArtisans OfficeWriter, Microsoft SQL Server Reporting Services does not support embedding a PivotChart in a report that can be exported from its Report Manager. Therefore, everytime you export your report from Reporting Services to an Excel format, you need to recreate the PivotChart. If you have many reports, keeping track of how you originally set up the PivotChart can be an intimidating task.
Using OfficeWriter Reporting Services Integration, you can put a PivotChart in your report definition once, and the PivotChart will be refreshed with new data each time the report is executedPivotCharts are a useful way to visualize data that appears in PivotTables.
Let's take a look at how you can integrate PivotCharts into your OfficeWriter Designer reports. to add a PivotChart (and PivotTable) to a Excel report for OfficeWriter.
Setup
| Note |
|---|
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 |
...
Our sample will use a simple query to compare average salary among job titles, gender, and departments, using a chart.
Step 1. Add Data Markers to Your Workbook
- Open Microsoft Excel and create a new blank workbook.
# Click Add Query on the OfficeWriter toolbar.
# Follow steps 2-11 of Create a Database Query.
# In the Add Tables dialog box, find the Employee table in the list and select Add. Click Close.
# In Microsoft Query, add the following AdventureWorks tables to the query. All of these tables are part of the HumanResources schema: Employee, EmployeePayHistory, EmployeeDepartmentHistory, and Department.
# Add the following columns to the query: Employee.Gender, Employee.Title, Employee.EmployeeID, EmployeePayHistory.Rate, and Department.GroupName
# In the grid displaying the data, double-click on the Rate column header. In the Column Heading textbox, enter BaseRate, and then click OK. Your query should look like this:
!xlw_Chart1.jpg!
# From the File menu, select *Return to OfficeWriter Designer*.
# Using the Insert Field button located on the OfficeWriter toolbar, place all the fields in the worksheet along with a header for each.
!xlw_Chart2.jpg!
# Publish the report.
# Click View on the OfficeWriter toolbar to see the populated report.
# Click Close Report View to return to the report template.
Step 2. Create a PivotChart
- With your mouse, highlight all the data markers and headers that you inserted in the worksheet.
# From Excel's Data menu, select PivotTable and PivotChart Report to open the PivotTable and PivotChart Wizard.
# Select Microsoft Office Excel list or database from the top section, PivotChart from the bottom section, and click Next.
# Since you already highlighted the header and field rows of your report, the Step 2 screen should already contain the cell range to use. Click Next.
# Select New Worksheet and click Finish. You should now have an empty PivotChart in your report as shown.
!xlw_Chart3.jpg!
# Right-click in the upper left part of the gray area where it says *Drop Data Items Here*, and select Chart Type.
# Select the chart type Clustered column with a 3-D visual effect.
!xlw_Chart4.jpg!
# From the PivotTable Field List, drag the Title field to the area labeled Drop Category Fields Here.
# Drag the Gender field to the Drop Series Fields Here area.
# Drag the Department field to Drop Page Fields Here.
# Drag the BaseRate field to Drop Data Items Here. Your PivotChart should look like this:
!xlw_Chart5.jpg!
For our example, we want to see the average salary, or base rate. However, the default for the PivotChart made the base rate a count, so we must change that.
- Right-click the label Count of BaseRate and select Format PivotChart Field.
# Change Count to Average in the list box and click Ok. It should now say Average of BaseRate.
Step 3. Create Data Placeholders
When the report is executed, the data markers that you created in Step 1 will be replaced with values from the database. To ensure that the PivotChart is constructed properly, you must replace the data markers with placeholder data - any data that will be of the same format as the real output data. For example, if the field is numeric, such as the EmployeeID field, we may use a zero or any other number. If the field is a character field, such as the Title field, we need to use a character placeholder. For our example, we will use the word 'none' for the Title and Gender fields.
- When Excel created your PivotChart, it also created a PivotTable corresponding to the PivotChart. Find the worksheet containing the PivotTable. It should look like this:
!xlw_Chart6.jpg!
# Under the Title label, replace =%%Query1.Title with none.
# Under the Gender label, replace =%%Query1.Gender with 0.
!xlw_Chart7.jpg!
| Note |
|---|
When you insert placeholder data, never use a real value. E.g. if you are displaying a city name, don't use 'Boston' for the placeholder data. The results returned for 'Boston', in that case, may not behave as expected. The same holds true for numeric data. Try to find a value that will never actually be in the query's result set. |
Refreshing the Data
There is one more thing to do before trying our PivotChart. Right-click on the pivot table and select Table Options from the list. Near the bottom of the left column, make sure Refresh on Open is checked. If you do not check this, when the report is viewed, your PivotChart and PivotTable will be empty.
Viewing the PivotChart
Publish, and View your report. Notice that on the first sheet, your data columns display the correct data.
Now look at the sheet containing your PivotChartThis query returns employee pay rate and department data from the AdventureWorks database. This is the data that will be used in the report.
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT Gender, Title, Employee.EmployeeID, Rate, Department.GroupName
FROM AdventureWorks.HumanResources.Employee,
AdventureWorks.HumanResources.Department,
AdventureWorks.HumanResources.EmployeeDepartmentHistory,
AdventureWorks.HumanResources.EmployeePayHistory
WHERE Employee.EmployeeID = EmployeePayHistory.EmployeeID AND
Employee.EmployeeID = EmployeeDepartmentHistory.EmployeeID AND
Department.DepartmentID = EmployeeDepartmentHistory.DepartmentID
|
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.
| Note |
|---|
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.
Now, go to the worksheet containing the chart's PivotTable. What do you see? The selections on the PivotTable match those of the chart. If you selected a department on the PivotChart, that same department was selected on the PivotTable. The PivotChart and the PivotTable work hand-in-hand.
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
...
To see the report as you designed it with OfficeWriter:
- From the Select a format export option drop-down list, choose Excel (.xls) designed by Officewriter.
# Click Export.
# You will be prompted to save or open. If your report contains a chart or a PivotTablePivotTable/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.