condition with another condition

[Little Princess]

Hello Cognos experts,
I am new to Cognos and would like some help.
I have situation where I have to check date if in two pervious prompts certain selections were made. The date has to be changed to date -1, if the conditions are not met than all records have to be brought up. This is my if statement:

if (?Terminated? = 'Yes' and ?Include Employees? = 'No')
then (_add_days ([CoveredLife].[CoveredLife Info].[CovLifeExpDt], -1 ) = ?Terminated Date?)
else ([CoveredLife].[CoveredLife Info].[CovLifeExpDt] <= 9999-12-31)

The Report studio doesn't like equation in THEN condition, how can I go around it?
Any help greatly appreciated.
LP

[zb12]

*

[Little Princess]

Hi zb12

Thanks for the quick reply.
This is the problem the ELSE statement applies to anything that doesn't fit the IF conditions, there are no other requirements. 9999-12-31 is the default date for the [CovLifeExpDt] field and it applies to all records except Terminated.

How can I go around this problem?
Thank you.
LP

[zb12]

IC. Sorry about that. I'll withdraw my earlier reply. I'll keep looking at you formula though because you should be able to use that I think.

[DSR]

if (?Terminated? = 'Yes' and ?Include Employees? = 'No')
then (_add_days ([CoveredLife].[CoveredLife Info].[CovLifeExpDt], -1 ) = ?Terminated Date?)
else ([CoveredLife].[CoveredLife Info].[CovLifeExpDt] <= 9999-12-31)

I have some questions on your query.

[CoveredLife].[CoveredLife Info].[CovLifeExpDt]---> How many values are going to avail in this column.

Your saying it should be -1 value ---> are you trying to filter selected terminated date-1 or ?.

Please let me know the answers for the above queries.....You can solve this issue  :)

[Little Princess]

Hi mynamesanju,

Thank you so much for helping me.
The first question, only one date, if the condition ?Terminated? = 'Yes' and ?Include Employees? = 'No' met. If this condition is not met, then the date shouldn't be taken to consideration, so this column can have as many values as records
the second question, the application and datawarehouse have 1 day difference, I don't know why and my team lead doesn't know it either, but for people who run reports it's all the same, so I have to hardcode it in the report, so it's looking for a day back.

I hope my explanations are clear.

LP

[zb12]

IDK if this will answer your question or help at all but do you even need <=9999-12-31? All you are wanting is the date in the field [CoveredLife].[CoveredLife Info].[CovLifeExpDt] to be returned right? Y do you need to define it?

[Little Princess]

This is the default date, all active records have this as terminated date until they change their status to terminated and when they are terminated then they get new date and that's the date I have to filter on.

[zb12]

have you tried using  ...else null
maye I'm missing the point in defining it but I really don't think you need it. If the record is still active you don't even want it on the report correct?

[Little Princess]

I don't want to replace the date with the null, I want to keep the default date and on the top of it keep the date for the terminated records.
But the error I get is in the THEN statement, it doesn't like my equation.

[zb12]

I gotcha.

Ok so have you tried this?:
then ([CoveredLife].[CoveredLife Info].[CovLifeExpDt] =_add_days(...

[DSR]

Hey Little Princess,

Thanks for the clarification on the queries.

Try this ....Just a small modification nothing else

if (?Terminated? = 'Yes' and ?Include Employees? = 'No')
then ([CoveredLife].[CoveredLife Info].[CovLifeExpDt]) = _add_Days(?Terminated Date?),-1)
else ([CoveredLife].[CoveredLife Info].[CovLifeExpDt] <= 9999-12-31)

a samll example for the explanation.....

user selected 02-01-2008 as terminated date.....now you need to filter data with 01-01-2008....Here we are equaling ExpDt=terminated date-1....

As per my understanding you need to extract the data or filter the data for Selected / Terminated date -1 in the above specified [CovLifeExpDt] field.

Check your luck... :)

Let me know if am wrong..........

[Little Princess]

Hello mynamesanju and zb12,

Thanks you so much for helping me out

I tried both statements and this is the error I get when I use it:

QE-DEF-0260 Parsing error before or near position: 114 of: "if (?Terminated? = 'Yes' and ?Include Employees? = 'No')
then ([CoveredLife].[CoveredLife Info].[CovLifeExpDt]) ="
QE-DEF-0261 QFWP - Parsing text: if (?Terminated? = 'Yes' and ?Include Employees? = 'No')
then ([CoveredLife].[CoveredLife Info].[CovLifeExpDt]) = _add_Days(?Terminated Date?),-1)
else ([CoveredLife].[CoveredLife Info].[CovLifeExpDt] <= 9999-12-31)

Clearly it doesn't like that I make an equation, rather than anything else.
Any ideas why it gives me error at the = sign?

Many thanks in advance for your help.

LP

[zb12]

take out the ) before the = sign

[Little Princess]

thanks zb12
it totally worked!
thank you!