Conditional Formating

[BradCustom]

Hi,

I'm using Crystal Reports 2008 and I'd like to change the background color of the Detail section based on the following criteria.

These conditions would change the background to Red.
If Promised Date is < Current Date
If Datediff ("d",SO_Detail.Order_Date,CurrentDate) >7 and isnull {SO_Detail.Job}

These conditions would change the background to Yellow.
If the {Available Qty} <=0
If Datediff ("d",SO_Detail.Order_Date,CurrentDate) >1 but <=6 and isnull {SO_Detail.Job}

All other conditions would be NoColor.

When I try to combine these conditions the only one that works is the first Red condition even though Crystal doesn't see any errors with the formulas I've tried.

Thanks for the help!

 

[briangriffin]

You just need one formula, entered in the details section X2 formula of the Section Expert, Color tab. This should work:

(If Promised Date is < Current Date
and Datediff ("d",SO_Detail.Order_Date,CurrentDate) >7
and isnull {SO_Detail.Job})
then crRed

else

(If {Available Qty} <=0
and Datediff ("d",SO_Detail.Order_Date,CurrentDate) between 2 and 6
and isnull {SO_Detail.Job})
then crYellow

else crWhite

 

[lbass]

It is unclear whether the two conditions for each color are both required or if only one of them is required (the difference between 'and' and 'or').

Re: Brian's suggestion, instead of "between 2 and 6" you would need to use:

in 2 to 6

-LB

 

[BradCustom]

Sorry about the confusion,

The two conditions are not required together, I'm evaluating both conditions seperately. Below is my attempt at the formula. It's probably not the correct way to write it but it seems to work.

if DateDiff ("d",{SO_Detail.Promised_Date},CurrentDate )>3 
then crRed
else
If (Datediff ("d",{SO_Header.Order_Date},CurrentDate) >7) and isnull ({SO_Detail.Job})
then crRed
else
if isnull ({SO_Detail.Job})
then crYellow
else
if {@QtyAvailable}<0
then crYellow
else crNoColor

I'm continuing to work on this and adding other conditions as I evaluate the output.

Thanks you your help!

 

[BradCustom]

I've changed the formula to something a little easier but I'm geting some inconsistant output. I'm also trying to change the background color for the Detail and GF3 Sections. I'm wondering if the inconsistant output is because of where the formula {@QtyAvailable} is located GF3.

Below is the formula and I've underlined the statement that's giving the inconsistant ouput. The "inconsistant output" is some records that meet the condition both the Detail and GF3 Section is Yellow; other records that meet the condition either the Detail is Yellow or the GF3 is Yellow but not both Sections. In a few cases there are records that don't meet the condition and the Detail Section is Yellow.

If (Datediff ("d",{SO_Header.Order_Date},CurrentDate) >3) and isnull ({SO_Detail.Job})
then crRed
else
[u]if ({@QtyAvailable}) <0
then crYellow[/u]
else crNoColor

Below is the {@QtyAvailable} formula:

({@PassOnHandQty}+{@PassInProdQty})-{@PassAllocatedTot}

If you need to see any of the formulas of the location os Subreports then please let me know.

Thanks for your help!

 

[lbass]

WHAT subreports? I recall these formula names from another thread, and they contain shared variables, I think. I'm wondering whether you have reset the shared variables somewhere--you should.

-LB

 

[BradCustom]

LB

This is a different report on the same database so the fields and Subreports are similar. I tend to use the same naming convention on Subreports and formulas so that could be why it looks familiar.

There are three Subreports on the Main Report; Sub-OnHandQty, Sub-InProdQty and Sub-SOAllocated. These Subreports are located in Detail B. The shared variables are invtot, prodtot, allocatedtot (all variables are totals for a single Material Record). The passed variables are located in Detail C. The Main Report has three groups:
SO_Detail.Promised_Date G1
SO_Header.Sales_Order G2
SO_Detail.Material G3

Lastly there is a formula on the Main report {@QtyAvailable} which is located in GF3 and is posted in the previous post.

None on the shared variables are reset because I'm not preforming any running totals. I'm not sure if the reset is necessary because there are no running totals?

Thanks for the help!

 

[lbass]

You need to reset them because if you hit a null, the last value of the shared variable will be used.

-LB

 

[BradCustom]

LB

I added the Reset for each shared variable but it didn't seem to have any effect on the background color change; specificly the change to Yellow.

Below is the Reset formula for one of the shared variables; I used the same formula for the others I just changed the variable name. The Reset formula has been placed in GH3.

//{@resetwk}:
whileprintingrecords;
numbervar invtot := 0;

Thanks for your help!

 

[BradCustom]

Okay,

I figured out what it's doing wrong now I just have to figure out how to fix it.

The formula below is changing the next record (Group3) to Yellow, I noticed this when I removed the other conditions and just tested for the condition below.

if ({@QtyAvailable}) <0
then crYellow
else crNoColor

When this is the only color change if the first three records are less than 0 and the fourth record is greater than 0. Then the first records background color isn't changed but the next three records are changed to Yellow regardless of {@QtyAvailable} value for the fourth record.

 

[BradCustom]

I figured out how to fix the problem with changing the Detail Section to Yellow based on the formula {@QtyAvailable}.

I had to move the three Subreports from the Detail Section into GH3a, the shared variables from the Detail Section to GH3b and the formula {@QtyAvailable} from GF3 to GH3c.

Now the color change to Yellow works fine.

 

[lbass]

Your reset formulas were not referencing the shared variables. They should have read:

whileprintingrecords;
shared numbervar intot := 0;
//etc.

Glad you got it working.

-LB

 

[BradCustom]

I thought I'd publish the final formula for changing the background color on the Detail Section based on certian conditions.

Below is the final fomula:

if datediff ("d",{SO_Header.Order_Date},CurrentDate)>3 and isnull({SO_Detail.Job})
then crRed

else

if {SO_Detail.Job}<> "" and {SO_Detail.Promised_Date}<CurrentDate and {@QtyAvailable}>=0
then crSilver

else

if {@QtyAvailable}<0
then crYellow

else crNoColor