How to Get a List Distinct Values from a SharePoint List using SSRS

When you create Dataset using SharePoint List as datasource the list columns includes duplicate records. There is no direct way to remove duplicates and get only the distinct values. This article describes how to get distinct values from SharePoint list and display in a parameter dropdown. You may use Report Builder 3.o or BIDS to do this. I’m using Report Builder 3.o.

Open Report builder and create a datasource. Name it as ‘DummyDatasource’. Select ‘Use a connection embedded on my report’ option and select connection type as Microsoft SharePoint List’. At the Connection string box enter the connection string to the SharePoint site.

Go to Credential tab and select ‘Use current windows user’ option. And click Ok.

Right click on Datasets on Report Data pane and select Add Dataset. Name it as ‘DummyProjectDetails’. Select ‘Use a dataset embedded in my report’ option. Select the DummyDatasource, which you create at the previous step as the datasource. Use the Query Designer to select whatever the list you want to include in the dataset. Click Ok.

Your Report Data pane may look like this. I select Project_Number and Agreed_Governance_Board fields from SharePoint for my dataset.

Right click on the Parameters at the Report data pane and select Add parameter. Name it as ‘DummyParameter’ select ‘Allow multiple values’ check box, select ‘Hidden’ option. Go to Available Values tab, select Get values from query tab. Select dataset, value field and label field from the drop downs as following image.

Go to Default values tab; select ‘Get values from a query option’ and select dataset and value field as follows.

Next you need to add the Custom code that will get the duplicate values and return only the distinct values.

Right click at the report body and select report properties. Go to Code tab. Add the following code to Custom code box.

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

And click Ok.

Next you are going to add the distinct parameter dropdown.

Add a new parameter and name it as ‘GovernanceBoard’, go to Available values tab and select Specify values option. Click on the Add button. Add the following expression to Label and value fields.

=Code.RemoveDuplicates(Parameters!DummyParameter)

Go to Default values tab and select Specify values option. Click on the Add button. Add the following expression to value fields.

=Code.RemoveDuplicates(Parameters!DummyParameter)(0)

Run the report and check the result. The result may like this: