Working with PivotTable

[ ]

To create a pivot table report:

  1. Create a dataset as the data source for the pivot table. Below, we use 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
  1. Create a table report according to the instruction in Creating Tabular Report, as shown below. The table will be the data source for the pivot table.


  1. In Microsoft Excel, from the Insert menu, select Name and then Define.
  2. Define a name as “sales”. The range of the name starts with the first cell of the header title and ends at the last cell of table data row as shown below.


  1. Click OK to finish.
  2. Create a new sheet for the pivot table.
  3. From the Data menu, select PivotTable and PivotChart Report to add a pivot table. A dialog is displayed.
  4. Select Microsoft Office Excel list or database as a data source and pivot table as report type.
  5. Click Next to continue.


  1. In the dialog box, enter “sales”, the name you defined above.
  2. Click Next to continue.


  1. Click Finish.


  1. Design the pivot table in Excel.


The designed pivot table is shown below.


  1. Right-click the pivot table and select Table Options.
  2. Make sure that Refresh on open is selected.


  1. Save the report and publish it to Report Server.
  2. Export the report from Report Server. The result is shown below.