5
Mar

SharePoint multiple values field in Pivot

   Posted by: Grumelo   in SharePoint

Requirements

How to deal with SharePoint (or MS Access) choice column, multi-value field (lookup field, person field) in Pivot or even PowerPivot?
How to split, expand, explode multi-value fields and use them in Excel reports?
Analyze SharePoint multiple values field in an Excel pivot table.

The solution must be automated, not manual execution of a script or macro.

Scope

The solution described in this post is meant to help users in a Personal BI, Self-Service BI environment.
In such environment they don’t have access to advanced tools like SQL server nor its components like SSIS or SSAS.

Problematic

Multi-value field, Choice field, Person field with multi values, lookup field with multi-values once exposed in Excel, PowerPivot or any text editor look like this:

value1;#value2;#value3

or even worst if it’s a lookup:

Person 1 ;#125;#Person 3;#68;#Person 4;#122

Unfortunately this kind of value can not be filtered, grouped and of course not used in Pivots.

Audience / Prerequisites

To understand what I’m talking about in this post you should know

  • SharePoint
  • SharePoint List with choice column (or similar multivalues fields)
  • Excel Pivot
  • How to connect a SharePoint List in MS Access
  • How to consume data from an MS Access Database in Excel

Solution

SharePoint List -> MS Access Linked Table -> Views -> Excel Pivot | PowerPivot

The concept is to not consume data directly from a SharePoint List in Excel or PowerPivot but to do it through an MS Access database.
In MS Access you will have a Linked Table to the SharePoint List you are trying to analyse.
You will need to set up some Views to expose properly your data.

The MS Access database file is stored in the same SharePoint space.
Once set up you don’t need to reopen the MS Access file, Data are automatically refreshed.

  • Case 1: Excel Pivot (no PowerPivot Data Model)
  • Case 2: PowerPivot Data Model

How a multi-value field look like in Excel?

2014-03-05_multivalues01

How the same field looks like in MS Access?

2014-03-05_multivalues02 

Case 1: Excel Pivot (no PowerPivot Data Model)

MS Access is doing the split of multi-values automatically.
To be able to use your data you just need to create 2  Views per multi-value field.

  1. One to list the values
  2. One to add the extra info you may need in your Pivot (we are in Case 1).

Your View will look like this

2014-03-05_multivalues04 2014-03-05_multivalues03

The MS Access View to be used in Excel Pivot is using a relation like in this example (this extra step is only required in case you can not use the PowerPivot Data Model in Excel).

You have to add all the columns you may need in your Pivot.

2014-03-05_multivalues05 

Case 2: PowerPivot Data Model

If you can use the PowerPivot Data Model then it’s even more straight forward.
You only need to create a MS Access View for each Multi-value field you want to expose in your Pivots (see Case 1).
You will set the relation inside PowerPivot Data Model, like in this example:

2014-03-05_multivalues06 2014-03-05_multivalues07 

Pros / Cons

  • + MS Access file is hosted in SharePoint
  • + Data refresh work (in Excel)
  • + No coding
  • + Fully automated
  • + Works fine
  • + No need of an SQL database
  • + Concept is simple
  • – Need MS Access
  • – May need PowerPivot Data Model
  • – if exposed in Excel Webpart auto refresh is not functional

Other solutions

  • using an SQL database
  • using macro to explode data record in Excel
  • complex DAX statements that could do half of the work

References

This post is a reply to http://www.mrexcel.com/forum/excel-questions/446240-analyze-sharepoint-multiple-values-field-excel-pivot-table.html

 

Evaluation: Pas terribleAssez bienBienSuper!Excellent! (Pas de votes)
Loading...

Tags: , , , , ,

This entry was posted on Wednesday, March 5th, 2014 at 12:22 and is filed under SharePoint. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a reply

Name (*)
Mail (will not be published) (*)
URI
Comment