A PivotTable is an interactive table that summarizes data to present it in a meaningful way. SQL Server Reporting Services cannot export a report to MS Excel format while maintaining a PivotTable. Report users have to manually setup PivotTables every time they export a PivotTable report from Reporting Services to Ms Excel. With Aspose.Cells for Reporting Services, Users may design PivotTable only once at report design time. Each time Users run the report, Aspose.Cells for Reporting Services will export the report to MS Excel and refresh the data into the PivotTable.

 

To create a PivotTable report, follow the steps below:

 

1. Create a dataset as the data source for the PivotTable.

In the sample, we make use of the “AdventureWorks” sample database that ships with SQL Server Reporting Services 2005 and create a dataset named “sales”.

 

The sql for the dataset is as follows:

 

SELECT PC.Name AS ProdCat,

                  PS.Name AS SubCat,

                  DATEPART(yy, SOH.OrderDate) AS OrderYear,

                  'Q' + DATENAME(qq, SOH.OrderDate) AS OrderQtr,

         SUM(SOD.UnitPrice * SOD.OrderQty) AS Sales

FROM    Production.ProductSubcategory PS INNER JOIN

         Sales.SalesOrderHeader SOH INNER JOIN

         Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN

         Production.Product P ON SOD.ProductID = P.ProductID ON PS.ProductSubcategoryID = P.ProductSubcategoryID INNER JOIN

         Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID

WHERE   (SOH.OrderDate BETWEEN '1/1/2002' AND '12/31/2003')

GROUP BY DATEPART(yy, SOH.OrderDate), PC.Name, PS.Name, 'Q' + DATENAME(qq, SOH.OrderDate), PS.ProductSubcategoryID

 

Please refer to Section “DataSources and Queries” to learn more about how to create a data source and dataset in Aspose.Cells.Report.Designer.

 

2. Create a table report according to the instruction in Section “Creating Tabular Report”, as shown in the following. The table will be the data source for Pivot table.

 

 

3. From MS Excel menu, Select Insert->Name->Define to define a name called “sales”. The range of the name starts with the first cell of header title and ends at the last cell of table data row as shown in the following. Click OK button to finish.

 

 

4. Create a new Sheet for PivotTable and Select Data->PivotTable and PivotChart Report… from MS Excel menu to add a PivotTable. In the dialog box, select Microsoft Office Excel list or database as a data source and PivotTable as report type. Click Next button to continue.

 

 

5. In the dialog box, input “sales” that is created in step 3. Click Next button to continue.

 

 

6. Click Finish button to continue.

 

 

7. Design the PivotTable in the Excel.

 

 

8. The final PivotTable is shown in the following.

 

 

9. Right-click on the PivotTable and select Table Options. Make sure that Refresh on open is checked.

 

 

10. Save the report and publish it to Report Server.
11. Export the report from Report Server, the result is shown in the following.