Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

IF AND STATEMENT

Status
Not open for further replies.

k2msl200

Technical User
Mar 7, 2013
6
US
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.
 
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:

Code:
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

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





 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top