| Author |
Message |
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
   Topic: Highlighting upcoming end date
Posted: 25 Jan 2016 at 4:29am |
|
Is there a way that I can highlight upcoming end dates on my report? For example, in my End_Date column, could I highlight dates within 3-months in yellow, 2-months in orange, and 1-month in red?
Crystal Reports XI/Windows 7
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 25 Jan 2016 at 6:16am |
|
are you basing this on a day difference or a month difference? I am guessing you nee something more like 0-30, 31-60 and 61-90 days.
right click on the field
select format field
select border
select the background formula button
use something like
if datediff('d',currentdate,table.duedate) in 61 to 90 then crYellow
if datediff('d',currentdate,table.duedate) in 31 to 60 then color(128,0,64)
if datediff('d',currentdate,table.duedate) <=30 then crRed
else crNoColor
|
|
IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 25 Jan 2016 at 7:40am |
|
The way you have is set up with the 0-30, 31-60 and 61-90 days would be fine. However, I seem to be having an issue.
By following your instructions, if I use the following formula:
if datediff('d',currentdate,{PURCHVIEW.ENDDATE}) in 61 to 90 then crYellow
if datediff('d',currentdate,{PURCHVIEW.ENDDATE}) in 31 to 60 then color(128,0,64)
if datediff('d',currentdate,{PURCHVIEW.ENDDATE}) <=30 then crRed
else crNoColor
I get an error saying that the last three lines don’t appear to be part of the formula. If I use any of these formulas separately, it works fine:
if datediff('d',currentdate,{PURCHVIEW.ENDDATE}) in 61 to 90 then crYellow
else crNoColor
if datediff('d',currentdate,{PURCHVIEW.ENDDATE}) in 31 to 60 then color(255,128,64) then
else crNoColor
if datediff('d',currentdate,{PURCHVIEW.ENDDATE}) <=30 then crRed
else crNoColor
I’ve tried many different combinations, but I’m just not familiar enough with CR to figure out how to write the formula to have them all work together.
|
|
IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 25 Jan 2016 at 7:46am |
|
I was able to get it to work using the following formula:
if datediff('d',currentdate,{PURCHVIEW.ENDDATE}) in 61 to 90 then crYellow
else if datediff('d',currentdate,{PURCHVIEW.ENDDATE}) in 31 to 60 then color(255,128,64)
else if datediff('d',currentdate,{PURCHVIEW.ENDDATE}) <=30 then crRed
else crNoColor
Thanks for your help. I couldn't have done this without your formula and instructions!
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 25 Jan 2016 at 8:34am |
|
oops, I forgot the else between each if.
glad you figured it out!
|
|
IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 25 Jan 2016 at 9:31am |
|
I did have one other question in regards to this formula. If I wanted this section of the formula:
if datediff('d',currentdate,{PURCHVIEW.ENDDATE}) <=30 then crRed
to have white font against the CrRed background, what needs to be added in the formula?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 25 Jan 2016 at 9:34am |
|
you would need to add a conditional formula to the font color
if datediff('d',currentdate,{PURCHVIEW.ENDDATE}) <=30 then crWhite else crBlack
Note the <=30 also handle negative values and appleis the red/white to overdue items
|
|
IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 25 Jan 2016 at 9:41am |
|
That's perfect? Thanks for all the help and the quick replies.
|
|
IP Logged |
|
|
|
