SQL "OR" Condition in Cognos Report Studio

[raviahuja21]

Hi Guys,

I have a query with multiple prompts and user ability to enter wild card characters in search text boxes.

So basically I have a java page where I have two text boxes and based on what has been entered by the user I get to know if it is a wild card search, there is only one valid wild card search which is "starts with" so if the user appends an asterik ("*" ) at the end of the text search I assume he is trying to do a starts with.

My Java page is calling the report studio report with Parameter from the URL

So I have the following parameters
FirstName
LastName
isLikeFN contains 1 if the user has used asterik at the end or 0 otherwise for the first name field
isLikeLN contains 1 if the user has used asterik at the end or 0 otherwise for the last name field
isAllAny contains 'All' if the user wants to do a "And" Condition (where firstname='firstname' and lastname='lastname')
and 'Any' if the user wants to do a "OR" Condition (where firstname='firstname' or lastname='lastname')

my prompt condition is as follows

Code Select Expand


?isAllAny?='All' and
(?isLikeFN?=0 and [FirstName]=?FirstName?) or
(?isLikeFN?=1 and [FirstName] starts with ?FirstName?)
and
(
(?isLikeLN?=0 and [LastName]=?LastName?) or
(?isLikeLN?=1 and [LastName] starts with ?LastName?)


)
or

?isAllAny?='Any' and
(?isLikeFN?=0 and [FirstName]=?FirstName?) or
(?isLikeFN?=1 and [FirstName] starts with ?FirstName?)
or
(
(?isLikeLN?=0 and [LastName]=?LastName?) or
(?isLikeLN?=1 and [LastName] starts with ?LastName?)


)




my problem is that both firstname and last name are optional but when I use this entire expression in one filter it asks me to enter values for FirstName & LastName.

Can you please help how do I achieve a "OR" Sql condition in Report Studio?

[Chandrasekar]

Hi Ravi,

Once u entered a prompt name in query/filter, that will be allow default as required prompt.

Thanks.

[raviahuja21]

Thanks for the response.

So how do i achieve this in Cognos?How to do a or condition if it becomes a required prompt.

[Chandrasekar]

Hi,

May be this link help for you.

http://stackoverflow.com/questions/14441522/in-cognos-could-we-avoid-an-optional-prompt-asking-a-value-when-its-parameter-i

Thanks