Using a report parameter conditionally in the sql query parameters

1 Answer 45 Views
DataSource SQL Expressions General Discussions Report Designer (standalone) Report Parameters
Adam
Top achievements
Rank 1
Adam asked on 12 Oct 2023, 01:11 AM | edited on 12 Oct 2023, 01:12 AM

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

 

 

 

 


 

 

1 Answer, 1 is accepted

Sort by
0
Dimitar
Telerik team
answered on 16 Oct 2023, 12:41 PM

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

Stay tuned by visiting our roadmap and feedback portal pages, enjoy a smooth take-off with our Getting Started resources, or visit the free self-paced technical training at https://learn.telerik.com/.
Tags
DataSource SQL Expressions General Discussions Report Designer (standalone) Report Parameters
Asked by
Adam
Top achievements
Rank 1
Answers by
Dimitar
Telerik team
Share this question
or