When working with SharePoint, you occasionally need to work with related data.  Since SharePoint isn’t setup for true relational data, that means we’re typically working with lookup columns from a child to a parent list.  This is all fine well and good, but I had a client ask me for some special filtering and output with this related data.  After reviewing my options, I found that the quickest way to do this (in under 15 minutes) is to use Microsoft’s Power Pivot add-in for Excel.  No SharePoint Designer required!  If you have Excel 2010, you will need to download and install the add-in.  If you use Excel 2013, the add-in is already installed, you just have to enable it.

Requirements

The goal we’re trying to achieve is to produce an easily repeatable output set of contacts whose parent account meets certain criteria for end users.  For example, the desired output is a list of all contacts whose parent account is in the United States, are Active, and are a Customer.  So we don’t need to filter the Contacts, just the Accounts and get all the related records.

Environment

For my demonstration, SharePoint lists are being used to hold CRM-style data.  There are 2 lists:

• Accounts (parent)
• Contacts (child with lookup to parent)

The Accounts list stores the following standard info:

The Contacts list has a lookup column that uses the title column from the Accounts list:

Solution

Your options are many:

• List Filter web parts (OOTB if you have Enterprise, or 3rd-party like from Roxority, Amrein, Kwizcom, Bamboo, etc.)
  • Doesn’t work exactly for me since unless your source is merged
• Use custom dataview web parts
  • Viable but requires building datasources and customizing dataview web parts
  • Not easy for business users to understand, high learning curve
• Access
  • Another good option, but once you load your SharePoint lists, it has a 2-way sync, and that’s not always ideal if you want to minimize the possibility of content being incorrectly updated
  • Not everyone understands how to build Access queries
• PowerPivot
  • My favorite option
  • The sync from SharePoint lists are one-way, so risk to content is not an issue
  • Requires no help from IT or your SharePoint support folks (woohoo)

If you’re watching the Microsoft Business Intelligence (BI) space at all recently, you know that they are making major pushes to make powerful reporting available to the end users with powerful tools in Excel combined with SharePoint (look up Power BI).  So in this spirit, let’s use one of these tools, Power Pivot. 

What Is It?

From Microsoft’s website:

“Power Pivot is a powerful data mashup and data exploration tool based on xVelocity in-memory technologies providing unmatched analytical performance to process billions of rows at the speed of thought.”

It allows you to build dimensional related datasources like a SQL Server Analysis cube but in Excel, and allows you to work with millions rows of data in seconds.  To complete the following steps, follow the above links to either download it for 2010 or enable it in Excel 2013.

Enough Talk, Let’s Dive In…

So we have our two lists, cool.  Let’s get going:

1. In SharePoint go to your parent list Accounts.  Click the Lists tab, then Export to Excel. 
   
   
   It will prompt you to open or save a file called owssvr.iqy file.  Let’s open it, so click Open.  If you get a security warning, click Enable.  You should be looking at your data with column headers in Excel.
2. In Excel, click on DESIGN tab of TABLE TOOLS, and on the far left in Table Name, let’s give it a more friendly name than Table_owssvr.  Call it Accounts.  Right-click on the worksheet and name it Accounts as well.
3. Minimize your Excel file, and go back to SharePoint.  Do the same thing to export this list to Excel as well.  Click Open on the file prompt, and again Enable on the security warning.  In Excel you will then get a Import Data prompt asking what to do.  Change the Where to New worksheet.
   
   
4. The Contacts list will now be in Sheet 1 or similar next to Accounts.  Rename the table and worksheet as in step #2 to Contacts.  We have both SharePoint lists in Excel, still linked back to SharePoint for future data refresh.  So now we need to relate them and render the related filtered records.  We do that building a data model in PowerPivot.
5. Click on the POWERPIVOT tab, and click Add to Data Model from the ribbon.  This adds the worksheet to the data model as a “linked table”.  This will open a new window.
   
   
6. Click back to your other worksheet, and click Add to Data Model for it as well.  Now you have both tables in the data model and we can relate them.
   
   
7. This brings in all columns from the lists but we don’t need them all, so let’s hide a few so they want show in the future pivot tables we’re going to create.  For each column you want to hide, right-click it and choose Hide from client tools.  Once done it will gray them out. 
8. To relate the tables, click the Diagram View button.
   
   
9. This looks similar to an Access query.  To relate our tables, just drag the related field from Contacts over to the Accounts field.  In my case, it’s Lookup Account field in Contacts points to the Account Name field in Accounts.
   
   
10. Click the Data View button on the ribbon to return to the data model sheet view.  To be able to filter the Contacts using Accounts fields, we need to show them on the Contacts datasheet.  So first identify the few columns from Accounts you want to use as a filter.  Here we will use Industry, Territory and Relationship Type.  To the far right where it says Add Column, click in the first cell, and type =RELATED(Accounts[Industry]).  As you type you get some Intellisense-like behavior where you can pick.  It will then auto-fill all rows with the corresponding values under the column of CalculatedColumn1.  Right click on the name, click Rename column, and type Acct Industry.  Be careful here in case columns have the same names between the lists.  You might get a formula error because the auto-complete doesn’t close the formula properly.  Just add a closing parenthesis.
    
    
11. Now do the same thing for the other columns.  It should look something like this:
    
    
12. To render and pivot our data, click the PivotTable button dropdown, and choose Flattened PivotTable.  This is VERY IMPORTANT, because a standard pivottable will not render each row in a worksheet type fashion but flattened will.  Click New Worksheet, and now you have a new sheet and a pivottable fields screen.  Rename your worksheet to something like Contacts Filter.
13. Expand Contacts in the Pivot Table fields, and click the Contact rows you want to show.  I’m using Full Name, Job Title, Lookup Account.  This will render the “rows” horizontally in the pivot like a worksheet.  Cool, let’s add the filters.
14. Drag each of the 3 Acct fields you made to the Filters box.  When done, it should look something like this:
    
    
15. Almost done!  By default it adds some subtotals for each column.  Right click on a record in Full Name, and uncheck Subtotal “Full Name”.  Do the same for Job Title and Lookup Account.
16. Ah, looks better.  Guess what, you’re done!  Now you can start filtering.  Here I’m filtering by all three.  Now you have your refined list, you can do whatever you like at this point.  You can also filter by the Account by clicking on the Lookup Account column as well.
    
    

The great thing is that when the content gets stale or the next time you need get your filtered data, just click the Data tab, then Refresh All.  As a bonus, if you want to see this data geographically, you can load it into Power Map (another Microsoft Excel add-in) and get a cool view of accounts in each City, with the higher bars by higher revenue by Account name:

For more information about this blog entry or C5 Insight, please Contact Us.