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

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 2nd 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.

PowerPivot in SharePoint

Advertisements

25 thoughts on “PowerPivot Walkthrough Using SharePoint Lists

  1. Pingback: Using PowerPivot to Analyze SharePoint List Data | Sean Brady's SQL Server Blog

  2. Pingback: More iPad support for SharePoint: Dashboards, PowerPivot, & Excel! | David Lozzi's Blog

  3. Pingback: [EN] SharePointSocial News Lagniappe | week 15.12 | SharePointSocial

  4. Vikas

    Hi
    I have been trying to link the list with Power pivot, however as soon as click on on finish or preview and filter button, i am getting “The remote server returned an error: (500) Internal Server Error.” message.
    I have full control on the list.
    Appreciate if you could help.

    -Vikas

    Reply
    1. David Lozzi Post author

      Hi Vikas,

      Are you using FBA for the site by any chance? There are some headaches around FBA and some of Microsoft’s Office connectivity.

      Where’s the error occurring exactly? How did you connect to the lists?

      Reply
      1. David Lozzi Post author

        Hi Shari,

        Are you using FBA for the site by any chance? There are some headaches around FBA and some of Microsoft’s Office connectivity.

        Where’s the error occurring exactly? How did you connect to the lists?

        Thanks,

      2. Shari Oswald

        It turned out that there is a Service account for PowerPivot that has to be granted contribute access to the Site Collection and specifically the lists. For those of you still struggling with the 500 error, this may be your solution.

  5. erkindunya

    Hi David,
    When I try to refresh using PowerPivot unattended account to refresh the data from SharePoint list it fails “Errors in the high-level relational engine. The following exception occurred while the managed IDbConnection interface was being used: Access to the path ‘\\SharePointsite\DavWWWRoot\sites\siteQ\reportsuite\Feed\SHAREPOINTLIST.atomsvc’ is denied.;Access to the path ‘\\SharePointsite\DavWWWRoot\sites\siteQ\reportsuite\Feed\SHAREPOINTLIST.atomsvc’ is denied. Access to the path ‘\\SharePointsite\DavWWWRoot\sites\siteQ\reportsuite\Feed\SHAREPOINTLIST.atomsvc’ is denied.. A connection could not be made to the data source with the DataSourceID of ’82f49f37-49aa-4d64-a15c-90ebbc01859d’, Name of ‘DataFeed SHAREPOINTLIST’. An error occurred while processing the ‘SHAREPOINTLIST’ table. The operation has been cancelled. “.

    Thank you in advance!

    Reply
    1. David Lozzi Post author

      Where are you specifying the unattended account? I’d bet since you’re refreshing against SharePoint, which requires authentication, you should be using an actual account. My walk through doesn’t specify unattended and I’m drawing a blank where that setting is.

      Reply
      1. erkindunya

        I am specifying/ configured unattended account on Secure Store Services App.Could you please explain bit more about “you should be using an actual account”. Many thanks for your help in advance!

    1. David Lozzi Post author

      Good question. I want to say “Sure, why not” but I think I need to do a little testing. Unfortunately my current machine is running Excel 2013, which introduces a lot of new features for PowerPivot. What version are you on? I can find an instance of Excel 2010 if needed.

      Reply
  6. erkindunya

    Hi David,
    Thanks you very much for your reply, you said “you should be using an actual account” we configured the SharePoint farm as Claim based authentication, and there is an unattended account to refresh the reports it works fine on SQL Connected report but not work on SharePoint list feed, even I move the feed on feed library and gave permission to the “unattended account user, what should I do to fix, my account has access on unattended account and any sites.
    Many thanks!

    Reply
  7. Dan A

    Hi David,

    I am a huge fan of your blog and have used it a ton! I am having an issue though and just can’t seem to get this to work correctly, so I was hoping you could provide some help.

    I have followed all of the steps but when I try and use the fields from both lists i.e. Company Name & Order Number, my pivot table is listing every single Order Number for each customer when I would expect it to only show order numbers for that particular customer. What am I doing wrong?

    Thanks Again!
    Dan

    Reply
    1. David Lozzi Post author

      It sounds like the relationship between the two lists isn’t setup correctly. Or, you have to select a customer in the slicer to filter your results. Did you try that?

      Reply
  8. tb

    thank you so much so sharing

    I have created a work book and published it to PowerPivot gallery, when I add new items to the list is doesn’t show in the PowerPivot chart even after refresh, I have configured the PowerPivot application settings but still not working, do u have any ideas ,

    Reply
  9. Marcelo F

    Hi David, Thanks for this great post.
    When i export the the data feed, powerpivot try to load for a long time (+20min), than show me the error “The remote server returned an error: (503) Server Unavailable”
    I am using Windows Authentication
    Appreciate if you could help.

    BR Marcelo Fonseca

    Reply
    1. David Lozzi Post author

      Hi Marcelo,

      I’m not sure what could cause this. How large of a list are you accessing (number of items)? Try another list, maybe a small quick one? Also, check out the ULS logs, possibly an error is being reported.

      Reply
      1. Marcelo Vitor Fonseca

        Hi David, Thanks for you replay.
        I thought could be a larger list, but i am having the same error with a list with 3 columns and 4 items.
        I Checked the ULS Logs, but i could not find any error related to the list i am exporting, data feed or PowerPivot.
        Strange it is that when i test the connection of the Data Feed in PowerPivot, i have no problem, but he still cant import the list.
        I already recheck all my PowerPivot configuration and seems to be everything OK. I Think this could be some security problem or something related to the data feed feature.

      2. David Lozzi Post author

        Check your version of PowerPivot installed. I think I mention in the post that the newer version doesn’t work with SQL 2008, and the PowerPivot website defaults you to download the 2012 version.

        You may need to update the diagnostic logging settings to verbose to capture the correct messages in the ULS logs.

        Otherwise, I’m not sure what else it could be without digging in.

      3. Marcelo Vitor Fonseca

        In Fact, i was using the SQL 2012 version, but already change to 2008 R2, and the error persist. The only difference it is that my Office version its 32x.
        I will update my diagnostic logging
        BR

  10. Vinit Singh

    Is it possible to make PowerView work with Forms FBA authentication. I have an environment set up to work properly with windows authentication but the moment i use Forms claims on that web application, it fails. Is there a

    Reply
  11. ddalexander1

    Thank you for this tutorial. My question is, “Once PowerPivot is loaded to the gallery for end-users to slice and dice, can the end user access the underlying Data or can access to that be restricted with only summary data available to view.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s