SSRS 2008 :Is it possible to specify an entire where clause within parameter?

Question

I have a table that I am building a report for. There are about 10 fields, but 4 of those fields may contain value(s) that users would want to filter the report by.

I was thinking I could create a parameter with a list of label/value pairs and the value portion of the parameter item would be an actual where clause for the underlying dataset like:

@filter
label/value
exceptions/where error_field like '%exception%'
counts/where count_field > 100
2016/where year_field = 2016

I tried dataset:

select error_field, count_field, year_field from mytable
@filter

I also tried(leaving where out of parameter value):

select error_field, count_field, year_field from mytable
where @filter

Both dataset queries failed to save. I am thinking I could include all the varying where clauses inside the dataset query statement, but it may require different parameters but how can they be empty unless I used 1=1 as default value. I only wanted to use a single parameter tho.

Any other ideas?

Thank you.

Just trying to understand the requirement.. you want to be able to run a query and filter on four different columns using the same parameter.. is that correct? Can you post some sample data and your desired output as well? — Harry
Correct. So if user selected 2016, the where clause would filter the year_field for 2016, if user selected count 200, the where clause would filer on count_field. — MaxAx
Will there be only one parameter or would you consider multiple parameters ? What would the prefixes be for the parameter entries? Need something more predictive for each option.. Too many questions.. can you clarify everything in your question by updating your question — Harry

Wes H Wes H · Accepted Answer · 2018-04-09T22:13:35

Your dataset query needs to be valid SQL. You can use an expression for the dataset, and work with the SSRS expression language to generate the SQL you need.

Something like:

="SELECT * FROM TABLE " + IIF(Value = True," WHERE 'A' = 'B'","")

However, I think they are a pain to work with. They are harder to understand, and take longer to maintain, and much easier to contain a mistake.
I find it easer and safer just to pass the parameters to a SQL Stored Procedure on the server, especially if you are using a TEXT BOX entry field.

SSRS 2008 :Is it possible to specify an entire where clause within parameter?

1 Answers