How can i run the report without specifying the parameter value in SSRS

0
votes

H, I have a parameter 'Client' as drop down in SSRS report. My requirement is to select all the records for all the clients if i don't select any parameter value and if i select particular value in the drop down,i need to display records for the that particular client.

I am getting the list of clients as a input from query.How can i add option select all by default.

Thanks in advance

2
reporting-servicesssrs-2008ssrs-2008-r2ssrs-tablixssrs-grouping

2 Answers

2
votes

You can do this a few ways...

  • Check the "Allow Multiple values" on the general tab of the Parameter Properties, go into the Available values and select the dataset you are using to get the values, make sure the the column that contains the actual data to search on is what you select for the VALUE field...(with this one - make sure your query eliminates the NULLs for the Value field) then you will have a drop down with all the values and it will add Select All...
  • Then in the Default Values tab, you can hook to the same query and select the VALUE field again... (as long as there aren't any NULLS) You may need to do tweaking depending on your query and values...

or

  • You can check the "Allow Null Value" on the General tab in the Parameter Properties, then in the Default Values - select "Specify Values", then Add, and (null) pops in there automatically...

  • Then call a stored procedure where the parameter defaults to NULL and if you pass NULL or don't pass in a string of values, your query will return all (not sure how you'd implement this if your query is embedded in the report... I try to do all of mine in Stored Procedures..)

1
votes

Add "All" to the results of the query that returns a list of clients for the drop-down and make it the default selection. Then handle it in your main stored procedure that if "All" was selected, you don't filter by clientId, and just get all clients.