Reporting Services 2000 Filtering based on a text parameter

I've looked through the reporting services help and it's very vague on how to do this.

What I want to do is set up a prompt with three values; Open, Closed, and Due for Completion.

Based on the user selection then a filter will be inserted, something like:If the user selects "Open" then Datatable.Status not in ('Closed', 'Cancelled', 'On Hold'), if the user selects "Closed" then Datatable.Status in ('Closed', 'Cancelled', 'On Hold'), if the user selects "Due for Completion" then Datatable.Completion date > getDate()-14.

In the Report Parameters screen there is a Value column for each label, but I haven't been able to work out how that value can be inserted into the where statement.

Thanks,

Bruce

Just use the parameter name you setup in the parameter dialog and insert it into SQL statement as @.ParameterName.

You can try the 'Multi-value ' option if want to allow user choose more then one values.

|||

OK here goes...

I've created a parameter in layout called Report_Type, and I've setup the following available values...

Label Value

Open requests.[Work Request Status] NOT IN ('Completed', 'Cancelled', 'Business Hold')

Closed requests.[Work Request Status] IN ('Completed', 'Cancelled', 'Business Hold')

Due For Completion requests.[Required By] > (getDate()-14)

Now I add a where statement to the SQL that reads "WHERE @.Report_Type"

The error I get is "syntax error or access violation".

Where am I going wrong?

Thanks.