Format SharePoint Multi Value People Picker Field in SSRS Report
Problem
Microsoft Report Builder supports SharePoint Sites as the Data Source within SQL Reporting Services Reports.
You can add person or group fields to the report.
SharePoint lookup fields are displayed along with the referenced item ids in the report — which does not look nice.
E.g. people picker (person or group) fields are presented as
43;#Donald Duck
The leading number is the item id of the user in the hidden user list (http://sharepointsite/sites/site/_catalogs/users/detail.aspx — User Information List).
Then the characters “;#” follow the string before the users display name.
If you have a multi valued people picker field, the values will show up in the report as:
115;#Mickey Mouse;#116;#Daisy Duck;#117;#Pluto
The Result
In my exampe, I will show you how you can
1. format the entries of the SPFieldUserValueCollection nicely
2. add additional information of the users in the report, e.g. the telephone number of the user
The result will look like
Mickey Mouse (555–123)
Daisy Duck (555–234)
Pluto (555–456)
Note:
I did not spend too much time in optimizing my approach: this example will demonstrate how to format max. three entries in the multi value people picker field.
If you have the situation where more than three users can be added to the person or group field, you need to do some improvements.
I saw some approaches where you can create unnumbered lists with vba, but I am no vba developper and probably will never be!
The solution
Another DataSet for the hidden User List
First you need another dataset within your report. I assume you already added the dataset of the SharePoint list to the report.
The query looks like and os related to your data source for the SharePoint Site
<RSSharePointList xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd=”http://www.w3.org/2001/XMLSchema">
<ListName>UserInfo</ListName>
</RSSharePointList>
In my example I named the dataset “UserInfo”.
Some Formulas
Within the report I added three rows to my table: one for each user record.
Each row contains a placeholder with an expression.
For the first user in the SharePoint field, the folmula looks like:
=Lookup(CInt(split(Fields!Resp.Value,”;”)(0)),Fields!ID.Value,Fields!Name.Value, “UserInfo”) & “ (“ & Lookup(CInt(split(Fields!Resp.Value,”;”)(0)),Fields!ID.Value,Fields!Work_phone.Value, “UserInfo”) & “)”
You will have to replace the fields name “Fields!Resp.Value” with yours.
The second record will use the formula:
=Lookup(CInt(replace(split(Fields!Resp.Value,”;”)(2), “#”, “”)),Fields!ID.Value,Fields!Name.Value, “UserInfo”) & “ (“ & Lookup(CInt(replace(split(Fields!Resp.Value,”;”)(2), “#”, “”)),Fields!ID.Value,Fields!Work_phone.Value, “UserInfo”) & “)”
and the third:
=Lookup(CInt(replace(split(Fields!Resp.Value,”;”)(4), “#”, “”)),Fields!ID.Value,Fields!Name.Value, “UserInfo”) & “ (“ & Lookup(CInt(replace(split(Fields!Resp.Value,”;”)(4), “#”, “”)),Fields!ID.Value,Fields!Work_phone.Value, “UserInfo”) & “)”
What the formulas do is to extract the item ids of the users in the user hidden list and then lookup the values “Name” and “Work_phone” in the user hidden list.
The following formulas will hide the rows two and three of only one “responsible” is maintained in the SharePoint field.
You can place these expressions to the Row Visibility configuration of the table.
For the second record:
=iif(split(Fields!Resp.Value,”;”).length > 2, False, True)
For the third record:
=iif(split(Fields!Resp.Value,”;”).length > 4, False, True)
I hope my approaches will help someone out there as I had quite some trouble finding input on this requirements on the internet.
If you have different approaches I will be happy if you would share them with me!
