#CognosAnalyticswithWatson

Hi Dave,
The following should work:
if (workorders.Complete_Date>workorders.Target_Date) then ( 1) else (0)

My personal preference is to use case statements instead, because there are fewer parenthesis to deal with.

case when workorders.Complete_Date>workorders.Target_Date
then 1
else 0
end

Currently(11.1.5) the output datatype of boolean is not supported as a result type of a calculation.

// Henk

------------------------------
HENK CAZEMIER
------------------------------

------Original Message------

Hi Dave,
The following should work:
if (workorders.Complete_Date>workorders.Target_Date) then ( 1) else (0)

My personal preference is to use case statements instead, because there are fewer parenthesis to deal with.

case when workorders.Complete_Date>workorders.Target_Date
then 1
else 0
end

Currently(11.1.5) the output datatype of boolean is not supported as a result type of a calculation.

// Henk

------------------------------
HENK CAZEMIER
------------------------------

#CognosAnalyticswithWatson

There are two ways to define a conditional expression in Cognos Analytics. If you take a look at the tool tip in the expression editors there should be simple examples of the construct. 

IF (  ... ) THEN ( ...  ) ELSE (  ....  )

CASE WHEN ...  THEN...  WHEN ... THEN ...  ELSE ...  END

Essentially, if-then-else internally is the same thing as CASE. In fact, when you look at the SQL pushed to a database, it will be expressed as CASE.

If you were using an OLAP source, IF-THEN-ELSE may be preferred in terms of being able to push that expression down to the OLAP provider. Meanwhile CASE may force more local processing within the query engine.

In your original note you appear to indicate wanting to use the constant value TRUE which is a BOOLEAN data type.

You cannot return a BOOLEAN type as a value to a report. You would need to use alternate values such as 0 or 1.

You can use a BOOLEAN in a filter or within an expression where you are testing a value to be TRUE or FALSE.

See the expression editor and online docs re constructs if-then-else, in_range, case etc.

------------------------------
NIGEL CAMPBELL
------------------------------

Dave, to simplify; if you are using if...then...else, your construct must follow these rules:
1. Your conditional test <the ... in the If(...) part of the expression> must be enclosed in parentheses ()
2. Your outcome if true <the ... in the then(...) part of the expression> must also be enclosed in parentheses ()
3. Your outcome if FALSE <the ... in the else ...) part of the expression> must also be enclosed in parentheses ()
Finally your text output must be enclosed in single quotes, so your solution might look like:
if (workorders.Complete_Date>workorders.Target_Date) then ('True') else ('False')

------------------------------
Albert Valdez
albert@intelalytics.com
------------------------------

Original Message:
Sent: Tue January 14, 2020 02:16 AM
From: NIGEL CAMPBELL
Subject: Issue with an IF/THEN statement

There are two ways to define a conditional expression in Cognos Analytics. If you take a look at the tool tip in the expression editors there should be simple examples of the construct. 

IF (  ... ) THEN ( ...  ) ELSE (  ....  )

CASE WHEN ...  THEN...  WHEN ... THEN ...  ELSE ...  END

Essentially, if-then-else internally is the same thing as CASE. In fact, when you look at the SQL pushed to a database, it will be expressed as CASE.

If you were using an OLAP source, IF-THEN-ELSE may be preferred in terms of being able to push that expression down to the OLAP provider. Meanwhile CASE may force more local processing within the query engine.

In your original note you appear to indicate wanting to use the constant value TRUE which is a BOOLEAN data type.

You cannot return a BOOLEAN type as a value to a report. You would need to use alternate values such as 0 or 1.

You can use a BOOLEAN in a filter or within an expression where you are testing a value to be TRUE or FALSE.

See the expression editor and online docs re constructs if-then-else, in_range, case etc.

------------------------------
NIGEL CAMPBELL
------------------------------

------Original Message------

There are two ways to define a conditional expression in Cognos Analytics. If you take a look at the tool tip in the expression editors there should be simple examples of the construct. 

IF (  ... ) THEN ( ...  ) ELSE (  ....  )

CASE WHEN ...  THEN...  WHEN ... THEN ...  ELSE ...  END

Essentially, if-then-else internally is the same thing as CASE. In fact, when you look at the SQL pushed to a database, it will be expressed as CASE.

If you were using an OLAP source, IF-THEN-ELSE may be preferred in terms of being able to push that expression down to the OLAP provider. Meanwhile CASE may force more local processing within the query engine.

In your original note you appear to indicate wanting to use the constant value TRUE which is a BOOLEAN data type.

You cannot return a BOOLEAN type as a value to a report. You would need to use alternate values such as 0 or 1.

You can use a BOOLEAN in a filter or within an expression where you are testing a value to be TRUE or FALSE.

See the expression editor and online docs re constructs if-then-else, in_range, case etc.

------------------------------
NIGEL CAMPBELL
------------------------------

#CognosAnalyticswithWatson