SSRS report to show all rows on load and then filter based on multi-value text paramater

Question

I have created an SSRS report that renders a table from my DB. I have created a multi-value text parameter. I know I can use the parameter three ways:

• use it in the dataset query (WHERE col1 IN (@parm))
• use it in the dataset filter (Expression = col1, Operator = In, Value = @parm)
• use it in the Tablix filter

The issue is that when I do this, on the initial page/report load there are no rows rendered and I have to enter something in the mult-value text parameter and re-run the report.

How can I get it to show all rows/records when the the parameter is empty and then only the matching rows otherwise.

Answer 1

Pass some default value in your dataset so at the time of page load, report will get rendered for those default value and it will not ask you to pass any value for the first time.

Answer 2

You can either

1. set the default values on the your parameter to the same query as the available values (i.e. it will select all members)
2. update your dataset query to something like WHERE (col1 IN(@parm) OR ISNULL(@parm,'')='')

The first option is heavier on resource, the second option is preferable but you may have to update the parameter properties to allow blanks/NULL and a blank default value for it.