SSRS - How to add a default value to a parameter with Available Values from a query?

Question

I have an SSRS report which contains a parameter ID, which will display sales totals for a person with selected ID. I get the options for the parameter by selecting "Get values from a query" under the Parameter's "Available Values" setting. However, in addition to these values, I want the first (default) value in the dropdown to be "All", which will display results for everyone combined. How can I add this hard-coded default to the dropdown list?

Answer 1

There are two ways to go about this.

I think the better way is to use a Multi-Value parameter and letting SSRS do the actual work. You would just need to change where you use the parameter to use it as a multi-value parameter (change = @PARAMETER to IN (@PARAMETER)).

For the Default values, use the same value field as was used for the Available Values.

The other way is to do it manually by appending an extra row to the parameter's dataset for the < ALL>. First you need to UNION an < ALL> to the data.

SELECT '<ALL>' AS EMPLOYEE_ID
UNION
SELECT EMPLOYEE_ID
FROM EMPLOYEES

For the Default value, Specify the value <ALL>.

Then you change you query or expression to use the EMPLOYEE ID or ALL:

SQL:

WHERE (EMPLOYEE_ID = @EMPLOYEE_ID or @EMPLOYEE_ID = '<ALL>') 

SSRS Expression:

=IIF(FIELDS!EMPLOYEE_ID.VALUE = Parameters!EMPLOYEE_ID.Value OR Parameters!EMPLOYEE_ID.Value = "<ALL>", 1, 0)

Value: 1