SSRS - XML datasource - Return result

Question

Wondering if anyone can help - been google-ing aaaaaall morning with no results.

I've created a dataset in SQL Server Report Builder that gets my Sharepoint userprofile (datasource = xml web service SharePoint UserProfileService). The dataset query results shows my info - columns are f.ex.

Name - type - Value

Title - xsd:string - Specialist

Department - xsd:string - Accounting

WorkPhone - xsd:string - 555-55555

etc.

Now, I want the query to only return the *Value column *, where Name equals Department.... So the result of the dataset looks like this

Value

Accounting

If anyone can help please :)

Update This is the Return result xml node I only want to return

 <PropertyData>
 <IsPrivacyChanged>false</IsPrivacyChanged>
 <IsValueChanged>false</IsValueChanged>
 <Name>Department</Name>
 <Privacy>Public</Privacy>
<Values>
<ValueData>
 <Value xsi:type="xsd:string">Accounting</Value>
 </ValueData>

     </Values> 
</PropertyData>

I´m guessing I have to do something like

<ElementPath IgnoreNamespaces="true">GetUserProfileByNameResponse{}/GetUserProfileByNameResult{}/PropertyData{**?????**}/</ElementPath>

I dont get it - why bother? Cant you just use the columns you want in your SSRS page design and ignore the rest? — Mike Honey
because I want to use the result as a parameter in another dataset. I only need the department data. — gijane

Adrian Sullivan Adrian Sullivan · Accepted Answer · 2013-10-30T08:02:13

Have you tried using a "Microsoft SharePoint List" as Connection type in the data source, you can still select the "User Information List" and create a filter for that in the dataset properties?

SSRS - XML datasource - Return result

1 Answers