2

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?

thnkwthprtls
  • 3,287
  • 11
  • 42
  • 63

1 Answers1

2

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.

enter image description here

enter image description here

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

HamiltonPharmD
  • 582
  • 3
  • 19
Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
  • I couldn't get the first solution to work, it didn't seem to really change much. I got the second to work though (with a little but of tinkering, I'll make an edit for the change), thanks :) – thnkwthprtls May 10 '16 at 12:48
  • update: Sorry, I had to remove the accepted check, since it doesn't work for me as written. I tried to update the answer since the same code just very slightly changed apparently isn't an acceptable edit. @Hannover Fist, maybe they'll let you change it instead, then I can mark it as accepted – thnkwthprtls May 10 '16 at 16:12
  • What change did you need to make? It looks like it should work. – Hannover Fist May 10 '16 at 16:45
  • In the eselect union block of code, it gave me errors as it's written, but if the code above and below the UNION is swapped (ie SELECT '' first) it works perfectly – thnkwthprtls May 10 '16 at 17:28
  • I'm not sure what you mean - the `SELECT ALL` is first in the **UNION** query and it seems to work for me as it is. – Hannover Fist May 10 '16 at 17:48
  • I'm not sure why it would be different, but for me it only worked the second way – thnkwthprtls May 10 '16 at 17:49