Sol Web Solutions Blog

Sol Web Solutions Blog is aimed to help, inform, educate and enable our site visitors.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Login
    Login Login form

Multiselect dropdown parameter for a SSRS SharePoint List report

Posted by on in SharePoint
  • Font size: Larger Smaller
  • Hits: 118550
  • 1 Comment
  • Subscribe to this entry
  • Print

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!

0

Comments

  • Guest
    Jussi Palo Thursday, 30 January 2014

    Thank you, your article immediately helped me fix Default Values issue in my multiselect drop down. In my case if Default Values contain values that are not found on the drop down, nothing will get selected.

Leave your comment

Guest
Guest Saturday, 25 November 2017

Help Today

Send an email here!

Sol Web Solutions will be glad to help you anytime with general or technical questions, suggestions or comments.

Website Hosting

Web Hosting

Sol News

We will not share, trade, or sell any of your information in any way. Your privacy is completely respected.