Question on returning all records when using parameters...

I'm trying to fully utilize parameters on a report.

I have 3 parameters, a start date, end date, and a cause.

The start and the end date are working as expected. What I would like is if a user fails to select a cause (queried from a lookup dataset) I would like to return all records, not filtering by cause. I'm using parameters handed to the dataset to do this and the report will not allow me not to select a value.

Should I be using Filter, Report Parameters, or some other means to solve this?

Any advice would be appreciated as I don't want to "KLUDGE" up the system with a bunch of reports each with varying levels of criteria.

Thanx!

TR

I handled a similar situation with creating an "All" choice in my optional parameter and setting this as the default value. If I understand what you are looking to accomplish, I believe this may work for you as well.|||Simone's suggestion will work, you may also use the 'multi-value' drop down which will automatically create the 'All' option. Assuming you are using SSRS 2005...|||

Say that I create an ALL choice on my drop down... What parameter do I put behind it such that my query will know to return all records as opposed to 'all' the string...

IE: Select * from myTable where myField = 'All'

would obviously try looking for the string value 'All' am I missing something really obvious? I have seen others suggest the all method so I have no doubt it works and I think I'm just missing part of the equation.

Thanks for the help.

TR

|||If you use a multivalued parameter, it will send in the values for the 'cause' parameter, so you can use an IN statement.

For example:

SELECT * FROM tblMyTable Where Cause in ('value1, value2, value3')|||

Hi Andy,

The problem with a multival box is that I will have between 24 and 60 values. Obviously too many to have a user selecting.

Thanks for the reply though.

TR

|||You can always go down the path of using dynamic SQL and generating your statment on the fly. You'll want to consider the performance impact, but on relatively basic queries, it should be negligble.

In that situation, set the 'All' label of the Cause parameter to a value of NULL (or -1, or 'All', whatever float your boat), check for the Cause query parameter value corresponding to All and if so, omit it from the WHERE clause.

Something like this:

If @.Cause IS Null then

Set @.SQL = 'SELECT * FROM Table'

Else

Set @.SQL = 'SELECT * FROM Table WHERE Cause = ' + @.Cause

Exec (@.SQL)|||We use dynamic SQL all the time....for further information see: http://sommarskog.se/|||

The SQL that I use when I want the 'all' option is as follows:

Code Snippet

Select * from table where id = case when @.id = -1(all) then id else @.id end

Hope this helps.

Simone