IF AND STATEMENT

[k2msl200]

I'm new to working in COGNOS. The version I work in is the Business Insite advanced v10.1.1. I have previous knowledge writing expressions in business objects DSS 1.5, MicroStrategy and MS Excel, but I'll be damned if I cant figure out how to create what I need using the Data Item Expression in COGNOS.

Here is what I'm ultimately trying to accompolish:

Pharmacy Type (Attribute)
Days Supply (Metric)

=IF(AND([Pharmacy Type]="Retail",[Days Supply]>=84),"Retail 90",IF(AND([Pharmacy Type]="Retail",[Days Supply]<84),"Retail 30",IF(AND([Pharmacy Type]="Mail Order",[Days Supply]>0),"Mail","UNKNOWN")))

Any assistence is appreciated.

 

[blom0344]

AFAIK Cognos uses the SQL equivalent CASE and IF THEN ELSE syntax. The CASE approach is very much equivalent with the ANSI-SQL 'CASE' syntax:

CASE 
WHEN
<<BOOLEAN EXPRESSION 1>>
THEN
<<VALUE2>>
WHEN
<<BOOLEAN EXPRESSION 2>>
THEN
<<VALUE2>>
ELSE
<<VALUE3>>
END

The ELSE part is optional and you can nest CASE within CASE

CASE
WHEN
([Pharmacy Type]='Retail' AND [Days Supply]>=84)
THEN 'Retail 90'
WHEN
([Pharmacy Type]='Retail' AND [Days Supply]<84)
THEN 'Retail 30'
WHEN
([Pharmacy Type]='Mail Order' AND [Days Supply]>0)
THEN 'Mail'
ELSE
null
END

Ties Blom

 

[k2msl200]

Thank you for your help on this, Ties!!! It worked perfectly!!