A PivotTable is an interactive table that summarizes data to present it in a meaningful way. You can rotate a PivotTable's rows and columns to see different summaries of the source data, or drill down to show details. By displaying different views of data, PivotTable reports allow you to easily compare data, see patterns and relationships, and analyze trends.
Without OfficeWriter, SQL Server Reporting Services cannot export a report to Excel format while maintaining a PivotTable. Report users have to to manually set up PivotTables every time they they export a PivotTable report from Reporting Services to Excel.
With OfficeWriter, you design your PivotTable only once at report design-time. Each time you run the report, OfficeWriter will export the report to Excel and plug the data into the PivotTable. In this section we will explore using PivotTables in a report created with OfficeWriter Designer.
The following example uses the AdventureWorks sample database that ships with SQL Server Reporting Services 2005. It is assumed that you already know how to set up a report in Excel using OfficeWriter Designer. If you do not know how to do this, see Create Your First Excel Report.
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 PivotTable 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 our EmployeeID field, we may use a zero or any other number. If the field is a character field, such as the Name field, we need to use a character placeholder. For our example, we will use the word 'none' for Name and a zero for EmployeeID.
When you insert placeholder data, never use a real value. For example, 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 PivotTable. Right-click on the PivotTable and select Table Options. 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 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 PivotTable. You should find the EmployeeID numbers across the top and all the vendor names along the left column. Each cell will contain the sum for each vendor/employee combination.
Click on the down arrow next to the Vendor Name label. ClearShow All and just select a few vendors.
Do the same for the EmployeeID field.
Now look at your results.
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: