COGNOiSe.com - The IBM Cognos Community

Hello i am trying to create a variable for use of a colour condition and for some reason it is not working.  I have a date in the system EVT_REQUESTSTART and i want it to show certain colours when this date is 2 or 3 days old.  For instance if the evt_requeststart date is 23/08/2010 and todays date is 25/08/2010 then this 2 days over the start date.  I have tried using sysdate but this doesn't work.  Below is the code/variable.


CASE
WHEN  [REQUESTSTART]< (sysdate + 7) THEN 'RED'
WHEN  [REQUESTSTART]< (sysdate + 10) THEN 'YELLOW'
WHEN  [REQUESTSTART]< (sysdate + 14) THEN 'GREEN'
ELSE 'WHITE'
END

Many Thanks for looking


Chris

Quote from: cbyrne on 01 Sep 2010 02:28:42 AM
Hello i am trying to create a variable for use of a colour condition and for some reason it is not working.  I have a date in the system EVT_REQUESTSTART and i want it to show certain colours when this date is 2 or 3 days old.  For instance if the evt_requeststart date is 23/08/2010 and todays date is 25/08/2010 then this 2 days over the start date.  I have tried using sysdate but this doesn't work.  Below is the code/variable.


CASE
WHEN  [REQUESTSTART]< (sysdate + 7) THEN 'RED'
WHEN  [REQUESTSTART]< (sysdate + 10) THEN 'YELLOW'
WHEN  [REQUESTSTART]< (sysdate + 14) THEN 'GREEN'
ELSE 'WHITE'
END

Many Thanks for looking


Chris

Hi

Try in this way!

CASE
WHEN  [REQUESTSTART]< _add_days({sysdate},7) THEN ('RED')
WHEN  [REQUESTSTART]< _add_days({sysdate},10) THEN ('YELLOW')
WHEN  [REQUESTSTART]< _add_days({sysdate},14) THEN ('GREEN')
ELSE ('WHITE')
END

if not works use sysdate() instead of {sysdate}

No sorry this doesn't work either i still get the below error when i validate.


RSV-RND-0051 Invalid expression 'CASE  WHEN  [REQUESTSTART]< _add_days({sysdate},7) THEN ('RED') WHEN  [REQUESTSTART]< _add_days({sysdate},10) THEN ('YELLOW') WHEN  [REQUESTSTART]< _add_days({sysdate},14) THEN ('GREEN') ELSE ('WHITE') END  '.
RSV-RND-0051 Invalid expression 'CASE  WHEN  [REQUESTSTART]< _add_days({sysdate},7) THEN ('RED') WHEN  [REQUESTSTART]< _add_days({sysdate},10) THEN ('YELLOW') WHEN  [REQUESTSTART]< _add_days({sysdate},14) THEN ('GREEN') ELSE ('WHITE') END  '.

Thanks  Chris

Im not getting UR logic first ..
as of my understanding if the REQUESTSTART  is less then sysdate ...then take the age bucket to show the Required String Varable in the report. correct me if i'm wrong..

For this,

First u calculate the AGE bucket between sysdate and REQUESTSTART date..u will get the 'aging bucket' which is difference between those two dates

then apply the 'between' function to get date difference on aging bucket

ex: [dataItem1]->_days_between(sysdate(),REQUESTSTART) =>It will give the no of days between these two dates

[dataItem2]->

case
when([dataItem1] between 1 and 7)
then('RED')
when([dataItem1] between 8 and 10)
then('YELLOW')
when([dataItem1] between 11 and 14)
then('GREEN')
else('WHITE')
end

the [dataItem2] is now ur report column

Yes this makes sence although do you have any idea what the syntax would be in report studio 8.4 for the data item?  I have tried a few different method but all failed using the between dates.

Data Item

between(sysdate() and [EVT_REQUESTSTART])

Thanks

Chris

Hello this works in report studio 8.4 which is great and thanks for the information, but i am still using 8.2 and the syntax is different.  Do you know what the syntax is in 8.2 for in the Data Item Between dates?

Many Thanks

Chris