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 Quick Start.
This query returns company purchase data from the AdventureWorks database, sorted by Vendor ID and Employee ID. This is the data that will be used in this report.
SELECT PurchaseOrderHeader.EmployeeID, PurchaseOrderHeader.TotalDue, PurchaseOrderHeader.VendorID, Vendor.Name FROM AdventureWorks.Purchasing.PurchaseOrderHeader JOIN AdventureWorks.Purchasing.Vendor ON Vendor.VendorID = PurchaseOrderHeader.VendorID ORDER BY PurchaseOrderHeader.VendorID, EmployeeID
Below is a screenshot of an Excel template with data markers that will be populated with the data from the above query.
After running the report, the imported data looks like this:
You've added a PivotTable to your report, but there are a few settings that need to be changed before we're done.
You are now done setting up the PivotTable in your report template.
Deploy, and View your report.
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.
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. Clear Show 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:
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.