You are here: Home » narzędzia IT » SSRS Reporting Services – Select all by default for multiple values parameter

SSRS Reporting Services – Select all by default for multiple values parameter

Featured Image

Don’t create list of options for multiple values parameter in parameter properties!

Why? Let’s go through parameter creation:

  1. Create parameter for „Status” of some sort. Mark „Allow multiple values.”Report Parameter Properties - allow multiple values
  2. Define available valuesReport Parameter Properties-specify available values
  3. Now you have nice multiselect filter on your report:Reporting services multiselect parameter
  4. But what about default values? Probably you want to select all statuses by default and avoid forcing user to select „(Select All)” option every time.So you can define default values, and you will have to add all possible values second time:Report Parameter Properties-define list of default values

How to make it smarter?

It is not unusual to get parameter options from SQL Query. Some people suggest to SELECT DISTINCT on status (column which is filtered) based on the same query as for „main” data source. But in that way:

  • You will have to wait for this query execution, which is rather expensive (server have to sort to calculate this). Imagine that you have thousands of rows…
  • Especially if list of options is rather small it is better to define the list „by hand”.
  • There might be problem with this strategy when possible values are changing. You will have to update report each time.

But if you just want to have simple list of options for parameter the better way is to create very simple query which is not making any query to server at all 🙂

  1. Create new datasetReporting services add dataset
  2. Select some existing datasetReporting services-Dataset Properties
  3. In query text put:

    SELECT ‚Created’ AS „Created”

    UNION

    SELECT ‚In progress’ AS „In progress”

    UNION

    SELECT ‚Closed’ AS „Closed”

  4. Change parameter properties to get values from queryReport Parameter Properties-get values from query
  5. Select „Default values” on the left and thanReport Parameter Properties-Get default values from query
  6. Now when you open your report it will show up with all possible options selected by default:Reporting services-multiselect parameter with default select all

Reporting services-multiselect parameter with default select all2

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *