Wednesday, August 10, 2011

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:




22 comments:

  1. Dude...nice job! Worked like a charm. Thanks for sharing such a thorough solution.

    ReplyDelete
  2. My field is grayed out, any ideas?

    ReplyDelete
    Replies
    1. I just went through your exact scenario, and put in all my steps to fix the issue @:
      http://solwebsolutions.com/blog/entry/multiselect-dropdown-parameter-for-a-ssrs-sharepoint-list-report

      Delete
  3. I also had this problem.

    It seemed to be because my hidden parameter was getting passed null values, filtering out null values on my parameter dataset solved the issue.

    ReplyDelete
    Replies
    1. Hi Jarret,

      I just went through your exact scenario, and put in all my steps to fix the issue @:
      http://solwebsolutions.com/blog/entry/multiselect-dropdown-parameter-for-a-ssrs-sharepoint-list-report

      Delete
  4. I can partially tell from the results that the solution works, but the field I'm doing a query against is a series of persons' names from a SharePoint people picker column and I get the following error:
    Cannot add multi value query parameter 'DummyParameter' for dataset 'DummyProjectDetails' because it is not supported by the data extension. (rsErrorAddingMultiValueQueryParameter)

    ReplyDelete
  5. I also have the same problem of parameter field greying out. I double checked to see if I have any null values passed on my parameter dataset. But I still have the issue. Any ideas?

    ReplyDelete
    Replies
    1. I just went through your exact scenario, and put in all my steps to fix the issue @:
      http://solwebsolutions.com/blog/entry/multiselect-dropdown-parameter-for-a-ssrs-sharepoint-list-report

      Delete
  6. Its working except I can not use this list for multi value queries. Is there a way to do this?

    ReplyDelete
    Replies
    1. having the same issue, did you solved it yet?

      Delete
  7. Works fine in BIDS but not working when report uploaded to Report server site. Am getting the error

    "The report parameter ‘parameter1’ has expression-based ValidValues. The sizes of the value and the label (multi-value) arrays have to be identical. (rsInvalidValidValueList)"

    Do you have any solution for this?

    Thanks in advance.....

    Regards,
    Samson

    ReplyDelete
    Replies
    1. I'm getting this error as well. Anyone find a solution to the rsInvalidValidValueList issue?

      Delete
  8. This comment has been removed by the author.

    ReplyDelete
  9. i have applied but its not working still repeated item is showing .

    ReplyDelete
  10. Thank you for this. I am running into an issue with the parameter dependencies.
    First of all, I need to explain that I am using multiple parameters. First a StartDate and EndDate, both of which are referenced in the query that pulls my dataset. Then I want to add two parameters which will filter the dataset based on two different string columns.
    I created the dummy paramters for each (with unique names), then created the visible parameters with available/default values that result from passing my dummy parameter to the code RemoveDuplicates function.
    When I have the dummy parameters occuring first in the parameters list I get an error saying that the dummy parameter depends on the visible parameter and forward dependencies aren't allowed. If I change the order (dummy parameters are lower than visible parameters) I get an error saying that the dummy parameter is not found in the parameter collection.

    Any advice?

    ReplyDelete
  11. The duplicates are removed but when I select the parameter it does not filter. Any thoughts?

    ReplyDelete
    Replies
    1. same issue here, won't seem to filter. any ideas guys?

      Delete
    2. It does not filter because existing parameters are not applied to your query. This is what I did and it worked.
      Create a separate dataset for querying parameter values. In Dummy Parameter Values indicate that dataset.
      On your main dataset create a new parameter whose value is your distinct parameter.
      Hope this helps.

      Delete
  12. This comment has been removed by the author.

    ReplyDelete
  13. I want the same for date values, as it is not sorting for date values...Please help!!!

    ReplyDelete