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.
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.
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.
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.
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.
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.
Your PivotChart is now finished and should look like this:
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.
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: