Posts Tagged ‘SharePoint’

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. Read the rest of this entry »

Tags: , , , , ,