Using a report parameter conditionally in the sql query parameters

[Adam]

Hi,

I would like to use/not use a parameter as a filter depending on the user entered value. If it's 0 then I want to ignore the filter.

Here is my configuration in report designer.

 

Essentially I want to achieve this:

SQL if parameter  aualtranpstper= 0 then 

pst_yer = parameter aualtranpstyer

 

SQL if parameter  aualtranpstper<>0

pst_yer = parameter aualtranpstyer and pst_per = parameter aualtranpstper

 

What should my value column in this screen look like?

I posted the same question after reading here https://www.telerik.com/forums/conditional-filtering-and-to-skip-filter-on-empty-report-parameters#205578 but thought it better to create new question.

Thanks for your help.

 

AW

 

 

 

 

 

 

[Dimitar]

Hello Adam,

Thank you for the provided information!

The approach from the top answer in the Conditional filtering and to skip filter on empty report parameters in Reporting | Telerik Forums thread doesn't actually 'ignore' the filter but rather uses the value of a field when the report parameter has a certain value.

If you need to ignore a filter for a certain condition, my recommendation is to do this inside the SQL query because it should be much easier.

For example, you may have a look at the sql server - How to ignore a TSQL Where condition when the value of a filter is null in one statement? - Stack Overflow thread where they check if a parameter is NULL to ignore the WHERE condition. You may use the same approach except that you need to check for a value of 0 instead of NULL.

Please try this approach and let me know if it helps.

Regards,
Dimitar
Progress Telerik