Conditional Formatting Formula

[dannyb29]

Hi All,
I am compiling a report that highlights how staff complete line checks. I need to highlight any line check that are all equal to the same quantities.
The psuedo code in my head would read something like this:

If{ProductsSold.Quantity} all = 0 highlight{ProductsSold.TxnId} in RED or if all entries are = the same quantities then highlight {ProductsSold.TxnId} in BLUE.

Once this calculation has been made I would like to distinctcount each 'RED' {ProductsSold.TxnId} and each 'BLUE' {ProductsSold.TxnId} by user.

I hope this makes sense.

 

[Madawc]

Right-click on the field and choose Format Field. You'll find options available.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

By "all", do you mean within some group? What field are you grouping on? How does "user" relate to the group? Are there different users within the group? It might help to see some sample (mock) data.

-LB

 

[MCuthill]

dannyb29,

A question surrounding the structure of your report. Are all these fields on the detail line, and you wish to highlight any detail row with the above criteria or is there a summary level that values are summed up to then evaluated?

I ask because of these statements: "If{ProductsSold.Quantity} all = 0" and "if all entries are = the same quantities". If "all" quantities within... a Transaction ID group? are 0, or if the quantity of a specific item within a transaction.

To format the background of a section, versus a field, go to Section Expert > Color > x+2. The formula's can be keyed in there for the color. Colors are such as crRed and crBlue. If you are looking at a detail-level formatting, the formula would look like:

IF {ProductsSold.Quantity} = 0 THEN crRed

For group level's, you would perhaps look at something like:

IF Sum({ProductsSold.Quantity},{yourgroup}) = 0 THEN crRed

Please advise as to the specific structure and we can go from there.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[dannyb29]

Hi all below is some example data which will hopefully help me explain this a little better. You will see that 'john jones' has completed a lines check with all of the same quantites which may or may not be correct, but I would like to highlight examples like these on my report. There is also a count on group 3 (the user) how many times this has occured. RedCount being the number of unique txid's with all quantities = 0 and BCount being the number of unique txid's with quantites = the same but not 0.

group 1 Stanford
group 2 Souvenir
group 3 2109 john jones RedCount Bcount
1 1


pcode description quantities tranxid
detail 84067 1st Diet Coke 2 87574
24497 1st Burts Crisps 2 87574
24136 1st S'bucks Cup 2 87574
24165 1st XC Cup 2 87574
20714 Clear Sack 2 87574
20509 Tear Back Lid 2 87574

14547 1st Eng Mustard 0 87572
14549 1st Brown Sauce 0 87572
14550 1st Tomato Ketch 0 87572
17521 Kitchen Cloth 0 87572

I hope this helps. :{

D

 

[lbass]

You can use a color formula like this on the detail section, after inserting a group #4 on TranxID:

if distinctcount({table.qty},{tranxid}) = 1 then (
if maximum({table.qty},{tranxid}) <> 0 then
crBlue else
if maximum({table.qty},{tranxid}) = 0 then
crRed
) else
crNocolor

-LB

 

[dannyb29]

I have created the formula and I have inserted another group based on Tranxid.
I have placed the formula in the detail section and this hasnt worked. I just generates a set of numbers. Am i putting the formula in the correct place?

Sorry about this
D

 

[MCuthill]

DannyB,

In Crystal 10 (I assume very similar in other versions)

Assuming you wish to color the background of the section:
"Section Expert" (then select either the header or foot for Group 4, wherever the fields are) >> "Color" Tab >> "x+2"

If for a specific field only:
"Format Field" >> "Border" Tab >> "x+2" (next to "background" heading)


Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

You have to go to the section expert->details->color tab->background ->x+2 and enter it there.

-LB

 

[dannyb29]

Thanks guys, that works great.
Were you able to help me with the distinct count formulas for each user to show on my report.

formula 1
Count all transactions where all quantities are blank or "0"

formula 2
Count all transactions where all quantities the same but >0

In my example above the outcome would be 1 for each formula.

D x

 

[lbass]

Could the result appear in the user group footer or must it be in the group header?

-LB

 

[dannyb29]

Hi LB
Yes it can appear in the footer

 

[lbass]

Then create these formulas:

//{@accum} to be placed in a Group #4 tranxID section:
whileprintingrecords;
numbervar rd;
numbervar bl;
if distinctcount({table.qty},{tranxid}) = 1 then (
if maximum({table.qty},{tranxid}) <> 0 then
bl := bl + 1;
if maximum({table.qty},{tranxid}) = 0 then
rd := rd + 1
);

//{@reset} to be placed in the user group#3 header:
whileprintingrecords;
numbervar rd;
numbervar bl;
if not inrepeatedgroupheader then (
rd := 0;
bl := 0
);

//{@displrd} to be placed in the user Group #3 footer:
whileprintingrecords;
numbervar rd;

//{@displbl} to be placed in the user Group #3 footer:
whileprintingrecords;
numbervar bl;

-LB

 

[dannyb29]

Thank you!!!

 

[bmlkidd]

Kind of on the same line, we planned on using a chr(13) as a delimeter to tell the field whether to be bold or not.

It works, only if there is at least one character on the new line, otherwise, it doesn't bold.

Any thoughts?

if instr({QuoteDet.Descrip},chr(13)) <> 0 then crbold

 

[lbass]

Not really the same topic--but I just tested this and the formula was bold even if chr(13) was the last character. What it didn't do was wrap without an additional character after the chr(13).

-LB