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 executed.
Let's take a look at how you can integrate PivotCharts into your OfficeWriter Designer reports. This example will use the AdventureWorks sample database shipped with SQL Server Reporting Services 2005. 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 Create Your First Excel Report.
Our sample will use a simple query to compare average salary among job titles, gender, and departments, using a chart.
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.
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 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. |
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.
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 PivotChart. 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. 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.
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: