Wildcard in parameter

[olushow]

I have a report with parameters where I want to select specific Status states. I want to use a wildcard entry '*' in the parameter field, when I want to return all the status e.g. Assigned, Resolved, Pending..etc..


I used the following statement in the Select Expert Window
Can anyone please tell me how to do this?

If {?Status} = 'ALL'
Then {HPD_Search_Association_CIJoin.Status} like '*'
Else {HPD_Search_Association_CIJoin.Status} = {?Status}

However I get the following error message: -

Database connector Error: 'S0002:AR System ODBC Rdrive[Data types are not appropriate for relational operation. Databae vendor code; 9055..

I'm assuming SQL SErver does not like my like statement, can someone help me modify the select statement..

 

[briangriffin]

If you don't have any other criteria in your select statement, then use this:

If {?Status} = 'ALL'
Then true
Else {HPD_Search_Association_CIJoin.Status} = {?Status}

You want to get the select statement to execute on the SQL server, and the wildcard for any set of characters in SQL Server is the % sign. But if you look at the SQL that is generated from the above statement, you'll see that there is no where clause being passed when you use the 'All' parameter, so no wildcard is needed.

 

[olushow]

What if i do have other criteria, do I remove the IF statement? Because i have other criteria like the follwing

If {?Status} = 'ALL'
Then true
Else {HPD_Search_Association_CIJoin.Status} = {?Status}
and
{HPD_Search_Association_CIJoin.Request_Description01} = "data" and
{@DateRange}


It seems my current statement is discounting the other criteria. How can I rewrite my query?

 

[Turkbear]

Hi,
In an IF..Then..Else structure, once the IF is satisfied no other part of the formula is evaluated, so you need to separate and combine to get other criteria met, like:

(
If {?Status} = 'ALL' 
Then 
true
Else 
{HPD_Search_Association_CIJoin.Status}  = {?Status}
)
and
{HPD_Search_Association_CIJoin.Request_Description01} = "data"
 and
[COLOR=red]What does this mean...are you comparing something to a date range?[/color]
{@DateRange}

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[olushow]

Yep.. That worked. the @DateRange is a formula I used for my date range parameter...