Multiselect dropdown parameter for a SSRS SharePoint List report

1. First create the data source in your Datasets folder.

In my case, my dataset is called dsStatusDDL because I'm pulling a status field from a SharePoint list.First create the data source in your Datasets folder.

 

TWO THINGS to also keep in mind which may help you for this dsStatusDDL dataset:

1. Some values are NULL (this will cause the multiselect dropdown parameter to be blank and appear to have no values)
2. I thought the values would be either null or "Approved", but it looks like I have a row containing a value that has an extra space on the end (I found this out by creating an expression and using the len() on the end of the field...and b/c the multiselect dropdown list appeared to have duplicate values of:
   
   1. Blank
   2. Approved
   3. Approved

I couldn't just do an applied filter where the Status field was not null, b/c then all my "BLANK" rows wouldn't come in.

How to fix that little gem?

• Right click on dsStatusDDL and select "Add Calculated Field"
• Field Name was "Status_Cleaned"
• Created and expression of >> =iif(isnothing(Fields!Status.Value), "BLANK", ltrim(rtrim(Fields!Status.Value)))
  • Using "BLANK" to make is very obvious to the user the status field was in fact blank or w/o value.
  • Using ltrim(rtrim( got rid of that one pesky space on the end of that one record (and maybe more in the future)

By now, the Dataset dsStatusDDL is ready to go.

Next, put this function to grab the distinct values from the above Dataset in Report >> Report Properties >> 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

2. Create Your First Hidden Parameter to pull from the above Dataset:

1. Create a parameter called DistinctStatusHidden
2. Make sure you select "Allow Multiple values"
3. Make sure you select "Hidden" for the visibility
4. On Available Values, select "Get values from a query"
5. Make sure the Dataset is dsStatusDDL, Value Field is Status_Cleaned, and Label field is "Status_Cleaned"
6. Default Values should also be from Dataset dsStatusDDL and Value field is Status_Cleaned

3. Now, create another parameter called StatusPull:

1. Make sure you select "Allow Multiple values"
2. Make sure you select "Visible" for the visibility
3. For Available Values, select "Specify values"
4. For Label >> put in the expression =Code.RemoveDuplicates(Parameters!DistinctStatusHidden)
5. For Value >> put in the expression =Code.RemoveDuplicates(Parameters!DistinctStatusHidden)
6. For Default values, select "Specify values"
7. For Value >> put in the expression =Code.RemoveDuplicates(Parameters!DistinctStatusHidden)

4. Finally, for the actual filtering tie to bring back all or selective status records.

I have no grouping in my report, but just a single "Row Groups" line of (table1_Details_Group):

1. Right click this and select "Group Properties"
2. Go to Filters and click Add
3. For the Expression >> I added the following (isnothing works with the Nulls, and the ltrim(rtrim gets rid of my spaces):
   =iif(isnothing(Fields!Change_Status.Value), "BLANK", ltrim(rtrim(Fields!Change_Status.Value)))
4. Operator is "In"
5. Value is my @StatusPull parameter

...and viola...you're done...and I hope this helped save you some time and effort!

Thanks!