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.
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.
For my demonstration, SharePoint lists are being used to hold CRM-style data. There are 2 lists:
The Accounts list stores the following standard info:
The Contacts list has a lookup column that uses the title column from the Accounts list:
Your options are many:
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.
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.
So we have our two lists, cool. Let’s get going:
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.
The complementary paper includes over 12 years of research, recent survey results, and CRM turnaround success stories.
This 60-second assessment is designed to evaluate your organization's collaboration readiness.
Learn how you rank compared to organizations typically in years 1 to 5 of implementation - and which areas to focus on to improve.
This is a sandbox solution which can be activated per site collection to allow you to easily collect feedback from users into a custom Feedback list.
Whether you are upgrading to SharePoint Online, 2010, 2013 or the latest 2016, this checklist contains everything you need to know for a successful transition.