About a week ago, I showed a simple demo on my blog of SQL Server PowerPivot for Excel, or just PowerPivot for short. I used SQL Azure as my backend and just did a mapping to a few Northwind tables and then built a pivot table using the Order Details table.
Today I will take that example one small step further and write a custom TSQL query and build a more advanced pivot table against that data source. In a future post I will build a PowerPivot example against a true OLAP Cube, but that will have to wait for the new year. :)
Here is a TSQL query that shows an Order and joins to get the Product Name and Category as well as the Employee and Customer names. We also do a DATEPART() to get the order year off the Order Date. In addition we also calculate the Total Price. This will allow us to do many different sorts and groupings in a pivot table. The TSQL is here:
od.orderid, o.orderdate,datepart(MONTH,o.orderdate) as OrderMonth,
datepart(YEAR,o.orderdate) As OrderYear,
emp.FirstName + ' ' + emp.LastName as EmployeeName,cust.CompanyName,
((od.UnitPrice*od.Quantity) * (1-Discount)) As TotalPrice
from dbo.[Order Details] od
inner join dbo.Products p
inner join dbo.Categories cat
inner join orders o
inner join dbo.Employees emp
inner join dbo.Customers cust
Now we have to connect to our SQL Azure database in PowerPivot. To do this, follow the instructions I placed on my blog last week to connect. After you connect, instead of choosing tables to connect to, choose the query option. Put in the TSQL from above and give your query a friendly name like Steve1 in PowerPivot (or rather NorthwindOrderDetails since we will be using this in our pivot table). Now you can use this query and its data to build a pivot table.
Building the PivotTable is pretty easy. Let’s use the TotalSales as our main value and Category Name and Product Name as our row labels. That means that we will see Category and be able to drill down into Product and get a sum for that category or product for each City (the field we choose for our column.) In addition we added a filter for Country that will allow us to see a drop down of each Country and we can filter for as many countries as we want. Lastly, we can add a “vertical slice” of Order Year that will add an additional filter in a graphical style. This filter is applied after your main filter (Country.)
I did this part in under a minute. This gives the user an extremely powerful pivot table and the ability to do some pretty sophisticated drill downs and filters. In addition, it is pretty easy to add/remove even more data (Customers and Employees for example) to this pivot table.