PowerPivot Walkthrough Using SharePoint Lists

Before we can just walk through using PowerPivot and SharePoint, we need to make sure things are in order. Some prerequisites

• PowerPivot has been installed and properly configured on your farm.
• Download PowerPivot for Excel 2010, SQL Server 2008 R2 version (SQL 2012 version WILL NOT WORK!) for free at http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=7609.

I am using the Northwind sample data provided by Chris Beckett, you can download it here. If you don’t want to load up the Northwind sample, target a list or two of data you want to work with. Having two which are related (using a lookup) is preferred.

First thing, open Excel and create a new workbook. Click PowerPivot tab then PowerPivot Window.

You’ll get a nice empty window. Now, you would think there would be a simple button that says Import SharePoint List. But there isn’t. Actually, what we’re going to use is the list data feed option.

If PowerPivot is installed on your farm correctly, you can simply go to your SharePoint list, click the List tab in the ribbon, then click the Export to Data Feed button.

This will prompt you to open or download a file. Save the file locally, then open it. When you open it, PowerPivot will ask where you want to import the feed. Select Book1, or another book name if you’re using an existing book.

That will then send you to the Table Import Wizard in PowerPivot.

• On the first page, name it something meaningful, like the list name you’re working with.
• Click Next.
• On the next page you can preview and filter your data, if you don’t want to pull it all in, you can filter out data. Only filter out data that your users will absolutely not want (like from 5 years ago).
• Click Finish.

That’ll take a little time, but it’ll load up your list into PowerPivot. Continue the same steps for all lists you want to import.

Once you have your lists in, we need to setup a relationship between them. In the PowerPivot window, click the Design tab, then click Manage Relationships.

You should get an empty window with a button Create. Go ahead and click Create. In my two tables, Customers and Orders, there is a standard SharePoint lookup between the two. The Orders list has a lookup into the Customer list. When the data is imported into PowerPivot, it saves the ID of the Customer record, so the field doesn’t have the name of the Customer, like SharePoint does.

In the Create Relationship window, first specify the daughter list and field, and then specify the parent list and field.

Click Create, then click Close on the Manage Relationships window.

That does it! You have SharePoint lists in PowerPivot. Up until this point, we’ve been very specific with SharePoint lists. If you are working with different data sources (SQL Database, Excel file, etc.) you can import them and then follow along for the rest of this post.

Now let’s get some of this data into Excel. Click the Excel button in the PowerPivot Window Title bar.

In the PowerPivot ribbon, click the PivotTable button, and select PivotTable.

Select Existing Worksheet when prompted. Click OK.

Your Excel window now has a whole lot of more going on.

The right pane is all PowerPivot related, and creates the report and slicers.

From here, I want to create a report that lists all Customers with total number of orders, with a slicer for selecting order date’s month, quarter and year; and a slicer for country, and a slicer for region.

In the top window on the right, the PowerPivot Field List, check off all the fields you want to display. I suggest showing only one for now. I am going to select CompanyName. You’ll see that the data is populated and the field was added to the Row Labels at the bottom.

To create slicers, now we can drag the fields down into the either the Slicers Vertical or Slicers Horizontal boxes. Let’s start with the Customer fields. So I am going to drag Country and Region down into the Vertical box. Your new slicers will appear on the left.

Go ahead and try them. Click a value in either slicer box. When I click Canada, two things happen. The Region slicer and the list of Customers both filter. Pretty cool eh?

Click the Clear Filter at any point to reset the filter.

To get the date slicer in there, we need to manipulate the data a little. If you were to drag the Order Date field into the Horizontal box, it would load up every date. That doesn’t help. So now we’ll go back to PowerPivot and create our Month, Quarter and Year values. To go back to PowerPivot, click the PowerPivot Window button in the ribbon.

On my Orders list, I am going to scroll all the way to the right and click Add Column. In the box where the value would normally be, we can enter in a formula. So I am going to enter =MONTH([OrderDate]), which will get me the month value. Right click the column header to rename to OrderMonth. Getting the Quarter is a little more difficult, we’re going to use the formula =INT((([OrderMonth]-1)/3)+1). And I’ll throw in another one for the Year, using =YEAR([OrderDate]).

Now let’s jump back into Excel. Excel will automatically prompt you to Refresh the data, go ahead and click Refresh.

And now let’s drag down the three new fields into the Horizontal box. As you do, the slicers will appear above our list.

And finally, lets add the count of orders, drag the ID field of the Orders list to the Values box. Then click on the field in the box and select Summarize By > Count.

You can rename the field as well by selecting Edit Measure from the previous menu.

Now you can use any combination of slicers to analyze your data.

And now let’s add a chart!

In Excel, move to the right a little, and click the cell where you want the chart to start, top left.

Click the PivotTable button in the ribbon, then click PivotChart. Select Existing Worksheet, click OK.

I want to see total sales based on country and selected time frame. Drag Country into the Axis Fields box, drag the ID of the Orders list into the Values box, and again change it from Sum to Count. We now have a little bar chart.

By default, none of the slicers affect this chart. I now want the date slicers to filter the data in this chart. To add slicers to a chart, right click the slicer and select PivotTable Connections. You’ll see two pivot tables (real unique), but since there’s only two here, check off the 2^nd one.

Do the same for each date slicer. Now when you filter your dates, your customer list and the chart will update.

And let’s do the same steps above, but I’m going to throw in one more chart to view order totals based on year, by selected country.

Now when I select a Country or Region, the company list and By Year chart updates. Selecting a Date updates Company and By Country chart.

Now that we have it ready to go, browse to a PowerPivot Gallery on SharePoint and upload this document. Once uploaded, you can now browse and interact with your PowerPivot within SharePoint.