Load unique values from a SharePoint List column on a SSRS Parameter

Business Case 

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.

Solution

  • Open Report builder and create a datasource and name it as ‘SPListDS’
  • 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 ‘SharePointListDetails’.
  • Select ‘Use a dataset embedded in my report’ option. Select the SPListDS, 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. I selected the column I have to use in my parameter from SharePoint List 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.
  • 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. (Thanks to Mohamed for this 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

  • And click Ok.
  • Next you are going to add the distinct parameter dropdown.
  • Add a new parameter and name it as ‘OriginalParameter’, 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)

That’s it done

Advertisements

About Joseph Velliah
As a SharePoint Developer my professional interests tend to be technical and SharePoint focused. I run a blog at "SP RIDER" where you can expect to read HOW TOs and scenarios that I run into during my day to day job. I hope my posts will give back a little to the community that is helped me.

14 Responses to Load unique values from a SharePoint List column on a SSRS Parameter

  1. Hi there. This post seems to be exactly what I’m looking for. However, each time I run the query, I get an error that says “An error occurred during local report processing. The definition of the report ‘Main Report’ is invalid. There is an error on line 10 of custom code: [BC30037] Character is not valid.” I copied the code as plain text and pasted in notepad. Then I added it to the code in Report Properties. I’ve gone through the code in report builder and removed/replaced the characters on each row, but still get this error. Please advise as to what I might be doing wrong. Thank you.

    • This is the one i am using in one of my report. Please check and let me know

      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

  2. One other quick question. Does this code work if the list column is a lookup column? I seem to be running into an issue with that. I’m using a lookup column from my list (which is actually InfoPath Form library). The parameter that’s displayed when I run the report is grayed out. I’ve chosen the DummyParameter as “hidden” and left the default as “visible” on the OriginalParameter.

    • It will work with SSRS lookup expression. Please try if you have any queries let me know i will help you. http://msdn.microsoft.com/en-us/library/ee210450.aspx

      • Hi Joseph. I’m stuck. I have created parameters for three different fields and named as follows: DummySeverity/OriginalSeverity, DummyDepartment/OriginalDepartment, and DummyReportType/OriginalReportType. I create the Dummy and Original parameters for each as described above. I created the “DummyParameter”, then added the code in report properties, then added the “OriginalParameter” for each (above). After doing so, when I run the report, the parameters at the top are grayed out and I’m not able to select anything. In the “OriginalParameter”, the Default Values property is set to “no default value”. I’ve tried changing other parameter properties, but still the parameter is grayed out when running report. Any direction is much appreciated.

        ~beargal34

  3. Hi Beth,

    The Parameter order should be like this (since its cascading)

    DummySeverity
    OriginalSeverity
    DummyDepartment
    OriginalDepartment
    DummyReportType
    OriginalReportType

    Are you selecting default value in the parameters?

    Could you please send your contact details to joseph@sprider.org

    i will get in touch with you to trouble shoot this issue (if required)

  4. I’ve encountered the same issue as Beth Beck (@beargal34) did. I’ve added 2 dummy parameters….pointed them to the correct dataset for each…set the available values (label and value) to look at the expression and set default value to look at the expression for each “live” parameter….but they remain grayed out. It is like the code isn’t firing to populate the dummy parameter. I have seen this issue and resolution in several places but have not been able to get it to work nor have I seen anyone reply saying they got it to work. I’d appreciate any assistance you may give.

  5. I’m trying to remove duplicates from a SharePoint list dataset. For simplicity sake, my list items have an ID number and a date. The ID is duplicate but I want to return the record with the latest date. Any idea how to do that? Thanks in advance.

    • Yes we can if we fetch the data something like this 🙂 If i get any thoughts i will update here. This is just an idea. May be i am wrong

      SELECT * FROM [ListName] WHERE id IN (SELECT Top(1) * FROM [ListName] Order by DateColumn DESC GROUP BY IDNumber)

      • Thanks but when using the RSSharePointList as the datasource, the SharePoint list data is CAML query. Is there a way to format this query with CAML?

      • Well, I found out the way to do it. Joseph’s code works but he left out one little detail. The field in the sharepoint list has to be set to multi-select as well as the parameter. This may cause issues with allowing, for example, someone to accidentally introduce bad data in the list say by setting a project to 2 different statuses or 2 different developers. So with that caveat, we are good to go! Thanks Joseph!

      • Hi Johnson
        The field you are mentioning in this context is a lookup column with multi-select option enabled? If not I can send you the working copy of my RDL and list stp file with data

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: