Walkthrough: How to get distinct values of a column of a SharePoint list using SQL Server Reporting Services
You can connect to a Windows SharePoint Services Lists Web Services, and use GetListItems method retrieve data from a SharePoint list by using the XML data source type in SSRS.However, the GetListItems returns records for a column of a SharePoint list may include duplicate records.
Unfortunately there is no direct way to remove the duplicate records using the Windows SharePoint Services Lists Web Services.This article will show you how to retrieve a column of a Sharepoint list and how to use Custom Code in SSRS to remove the duplicate records.
Create a Report Server Project in Business Intelligence Development Studio
Open Business Intelligence Development Studio (BIDS) and create a new Report Server Project.
Add a report to the project
1. Right click the Report folder, select Add, click New Item....
2. Select Report template, input a report name "Remove Duplicate Records.rdl" … .
Create a XML data source
1. From the View menu, select Report Data.
2. In the Report Data panel, right-click Data Sources, and then click Add Data Sources… .
3. In the Data Source Properties dialog, configure the connection
a. Type a name for the data source in the Name textbox
b. Check Embedded connection
c. Select type XML
d. Type the connection string in the Connection string textbox
The connection string is the URL of the Windows SharePoint Services Lists Web Services. For example:
https://sharepointasia/sites/IRSIS/_vti_bin/lists.asmx
4. Go to the Credentials tab, check the item Use Windows Authentication(integrated security)
Create a Dataset based on the XML data source type
1. In the Report Data panel, right-click Datasets, and then click Add Dataset… .
2. In the Dataset Properties dialog, type the name for the Dataset.
3. Select the data source we created in the last step in the Data source dropdown list.
4. Select Text for Query type.
5. In the query textbox, type the following statement:
<Query>
<SoapAction>https://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
<Method Namespace="https://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
<Parameters>
<Parameter Name="listName">
<DefaultValue>{5D7A343B-CFE2-4B53-8C51-EEEB87591A12}</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="True">*</ElementPath>
</Query>
Note: You need to replace the value for listName parameter with actual value in your environment.
Add a dummy parameter to the report
1. In the Report Data panel, right-click Parameters, and then click Add Parameters… .
1. In the Report Parameter Properties dialog, type a name for the parameter.
2. Check the item Hidden and Allow multiple values
3. Go to Available Values tab, set the parameter to get values from the Dataset we set before.
4. Go the Default Values tab, set the parameter to get values from the Dataset we set before.
Embed code to the report
The code is used to remove duplicate records from the dummy parameter, and then return a unique array.
1. Click the report body.
2. Click the Report menu, and select Report Properties…
3. Go to Code tab.
4. Embed the following code:
Public Shared Function RemoveDuplicates(parameter As Parameter) As String()
Dim items As Object() = parameter.Value
System.Array.Sort(items)
Dim k As Integer = 0
For i As Integer = 0 To items.Length - 1
If i > 0 AndAlso items(i).Equals(items(i - 1)) Then
Continue For
End If
items(k) = items(i)
k += 1
Next
Dim unique As [String]() = New [String](k - 1) {}
System.Array.Copy(items, 0, unique, 0, k)
Return unique
End Function
Create another parameter
This parameter is used for user interaction. This parameter get the distinct value from the custom code function RemoveDuplicates.
1. In the Report Data panel, right-click Parameters, and then click Add Parameters… .
2. In the Report Parameter Properties dialog, type a name for the parameter.
3. Go to Available Values tab, check item Specify values
4. Click Add to add a value, type the following expression in both Label and Value textbox.
=Code.RemoveDuplicates(Parameters!DummyParameter)
Note: the DummyParameter is name of the dummy parameter.
5. Go to Default Values tab, check item Specify values
6. Click Add to add a value, type the following expression in the Value textbox.
If Allow multiple values is checked, please use the following expression
=Code.RemoveDuplicates(Parameters!DummyParameter)
Otherwise, you can use:
=Code.RemoveDuplicates(Parameters!DummyParameter)(0)
Note: the DummyParameter is name of the dummy parameter.
Additionally, if you use cascading parameter, and the child parameter includes duplicate records, please use the custom code function to remove the duplicate records.
Reference
Custom Code and Assembly References in Expressions in Report Designer (SSRS)
https://msdn.microsoft.com/en-us/library/ms159238.aspx
XML Connection Type (SSRS)
https://msdn.microsoft.com/en-us/library/dd220468.aspx
Lists.GetListItems Method
https://msdn.microsoft.com/en-us/library/lists.lists.getlistitems(v=office.12).aspx