Don’t create list of options for multiple values parameter in parameter properties!
Why? Let’s go through parameter creation:
- Create parameter for „Status” of some sort. Mark „Allow multiple values.”
- Define available values
- Now you have nice multiselect filter on your report:
- 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:
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 🙂
- Create new dataset
- Select some existing dataset
- In query text put:
SELECT ‚Created’ AS „Created”
SELECT ‚In progress’ AS „In progress”
SELECT ‚Closed’ AS „Closed”
- Change parameter properties to get values from query
- Select „Default values” on the left and than
- Now when you open your report it will show up with all possible options selected by default: