making Report Parameters optional

I′m currently working on a report (SSRS 2005) with parameters which I want to make optional. In other words: when I open the report in the preview or IE, I′m always forced to enter values for every single parameter. But I want to have them optional so I can decide whether to choose values (as the option "Multi-value" is enabled) or leave them blank. (ignore in selection result)

is this possible? - I hope;-)

thanks in advance

pamike

The way to make entering parameter values optional is to give them default values. You can set the default value to give you all results and thereby achieve your result.|||

hi thanks for the answer csi_hugh, but i still don′t know:"non queried" or "from query" (or NULL?) and which value (expression) has to be set as default value?

thx

pamike

|||

The answer depends on your data. If the parameter is used in a Where condition of your Dataset, you have to consider what value will return all records based on the Where condition. In the case of Multi-select parameters, if you are using SP1 you can add the value "All" to the top of the choice lists, and then add Where conditions to your Dataset that if @.param = 'All' or Field = @.param. That will give you all records if you pick 'All'.

Hope that helps.

|||

I am also seeking an answer for this question. I guess we can do it with default values and using stored procedure to make the select.

Certainly we wish RS can provide this feature. it is fairly a basic feature.

|||

What we did to achieve this is to create a parameter with a (valid) default value.

Say, the parameter @.filter of type 'integer' is allowed to have a 'null' value and has default value 'null'.

We use this same default value in the query, like (very simple example)

SELECT * FROM Employee WHERE active=1 AND company_id=CASE WHEN @.filter IS NULL THEN company_id ELSE @.filter END

This way, if @.filter has a value other than 'null', we limit our query result to Employees with a company_id equal to @.filter. If @.filter has no value (it gets it's default value of 'null') then the query result is not limited based on company_id.

It's not pretty, but it works without creating stored procedures or functions.