Count all results without a white background color

[cjbrown815]

Hello,
I have a manual XTAB report with conditional formatting. The formatting is causing the cells to turn red if they are outside of a defined range. now we want to see how many of those fell outside of that range. So I need to caount all cells that are not white background.

CODE:
if ({#Moisture}) like "NR" then crBlue else

if {@Product Type} like "*MozzPS*" and
val({#Moisture}) in 0 to 48.49 then crRed else
if {@Product Type} like "*MozzPS*" and
val({#Moisture}) in 50.51 to 101 then color(255,147,147) else

if {@Product Type} like "*MozzWM*" and
val({#Moisture}) in 0 to 46.49 then crRed else
if {@Product Type} like "*MozzWM*" and
val({#Moisture}) in 48.51 to 101 then color(255,147,147) else

//#Moisture ~ProvoNello
if {@Product Type} like "*Provo_Nello*" and
val({#Moisture}) in 0 to 41.99 then crRed else
if {@Product Type} like "*Provo_Nello*" and
val({#Moisture}) in 45.01 to 101 then color(255,147,147) else
if {@Product Type} like "*Provo_DeliStyle*" and
val({#Moisture}) in 0 to 41.99 then crRed else
if {@Product Type} like "*Provo_DeliStyle*" and
val({#Moisture}) in 45.01 to 101 then color(255,147,147) else
if {@Product Type} like "*ProvoNello*" and
val({#Moisture}) in 0 to 41.99 then crRed else
if {@Product Type} like "*ProvoNello*" and
val({#Moisture}) in 45.01 to 101 then color(255,147,147) else

//#Moisture ~Ricotta
if {@Product Type} like "*Ric_DelPast*" and
val({#Moisture}) in 0 to 63.69 then crRed else
if {@Product Type} like "*Ric_DelPast*" and
val({#Moisture}) in 65.81 to 101 then color(255,147,147) else

if {@Product Type} like "*Ric_Dolce*" and
val({#Moisture}) in 0 to 74.24 then crRed else
if {@Product Type} like "*Ric_Dolce*" and
val({#Moisture}) in 78.26 to 101 then color(255,147,147) else

if {@Product Type} like "*Ric_Sop*" and
val({#Moisture}) in 0 to 72.49 then crRed else
if {@Product Type} like "*Ric_Sop*" and
val({#Moisture}) in 75.01 to 101 then color(255,147,147) else

if {@Product Type} like "*Ric_Tenera*" and
val({#Moisture}) in 0 to 70.59 then crRed else
if {@Product Type} like "*Ric_Tenera*" and
val({#Moisture}) in 73.41 to 101 then color(255,147,147) else

if {@Product Type} like "*ProvoNello*" and
val({#Moisture}) in 0 to 41.99 then crRed else
if {@Product Type} like "*ProvoNello*" and
val({#Moisture}) in 47.01 to 101 then color(255,147,147) else

//#Moisture ~FDL
if {@Product Type} like "*FDL_Ciliegine*" and
val({#Moisture}) in 0 to 58.99 then crRed else
if {@Product Type} like "*FDL_Ciliegine*" and
val({#Moisture}) in 63.01 to 101 then color(255,147,147) else
if {@Product Type} like "*FDL_Bocc*" and
val({#Moisture}) in 0 to 58.99 then crRed else
if {@Product Type} like "*FDL_Bocc*" and
val({#Moisture}) in 63.01 to 101 then color(255,147,147) else
if {@Product Type} like "*FDL_Ovoline*" and
val({#Moisture}) in 0 to 58.99 then crRed else
if {@Product Type} like "*FDL_Ovoline*" and
val({#Moisture}) in 63.01 to 101 then color(255,147,147) else
if {@Product Type} like "*FDL_Roton*" and
val({#Moisture}) in 0 to 58.99 then crRed else
if {@Product Type} like "*FDL_Roton*" and
val({#Moisture}) in 63.01 to 101 then color(255,147,147) else
if {@Product Type} like "*FDL_Ceppon*" and
val({#Moisture}) in 0 to 58.99 then crRed else
if {@Product Type} like "*FDL_Ceppon*" and
val({#Moisture}) in 63.01 to 101 then color(255,147,147) else

//#Moisture~Aged Cheese
if {@Product Type} like "*Cheddar" and
val({#Moisture}) in 0 to 34.99 then crRed else
if {@Product Type} like "*Cheddar" and
val({#Moisture}) in 37.51 to 101 then color(255,147,147) else

if {@Product Type} like "*Parmesan" and
val({#Moisture}) in 0 to 29.99 then crRed else
if {@Product Type} like "*Parmesan" and
val({#Moisture}) in 31.99 to 101 then color(255,147,147) else

if {@Product Type} like "*Parmesan_Shred" and
val({#Moisture}) in 0 to 29.99 then crRed else
if {@Product Type} like "*Parmesan_Shred" and
val({#Moisture}) in 31.99 to 101 then color(255,147,147) else

if {@Product Type} like "*Parmesan_Grat" and
val({#Moisture}) in 0 to 23.49 then crRed else
if {@Product Type} like "*Parmesan_Grat" and
val({#Moisture}) in 28.51 to 101 then color(255,147,147) else

if {@Product Type} like "*Salamini*" and
val({#Moisture}) in 0 to 36.99 then crRed else
if {@Product Type} like "*Salamini*" and
val({#Moisture}) in 43.01 to 101 then color(255,147,147) else

if {@Product Type} like "*Boccino*" and
val({#Moisture}) in 0 to 36.99 then crRed else
if {@Product Type} like "*Boccino*" and
val({#Moisture}) in 43.01 to 101 then color(255,147,147) else

if {@Product Type} like "*25#_G*" and
val({#Moisture}) in 0 to 36.49 then crRed else
if {@Product Type} like "*25#_G*" and
val({#Moisture}) in 39.99 to 101 then color(255,147,147) else

if {@Product Type} like "*55#_G*" and
val({#Moisture}) in 0 to 36.49 then crRed else
if {@Product Type} like "*55#_G*" and
val({#Moisture}) in 39.99 to 101 then color(255,147,147) else

if {@Product Type} like "*Mandarini*" and
val({#Moisture}) in 0 to 36.49 then crRed else
if {@Product Type} like "*Mandarini*" and
val({#Moisture}) in 39.99 to 101 then color(255,147,147) else

if {@Product Type} like "*Romano*" and
val({#Moisture}) in 0 to 31.49 then crRed else
if {@Product Type} like "*Romano*" and
val({#Moisture}) in 35.76 to 101 then color(255,147,147) else

if {@Product Type} like "*Provolettina*" then crWhite else

if {@Product Type} like "*Competitor*" then crWhite else

//#Moisture~Dice/Shred
if {@Product Type} like "*EastCoast_*" and
val({#Moisture}) in 0 to 47.49 then crRed else
if {@Product Type} like "*EastCoast_*" and
val({#Moisture}) in 49.51 to 101 then color(255,147,147) else

if {@Product Type} in ("*WKLY_MildProvoBlend_Shred*""*WKLY_MildProvoBlend_Dice*") and
val({#Moisture}) in 0 to 46.86 then crRed else
if {@Product Type} in ("*WKLY_MildProvoBlend_Shred*""*WKLY_MildProvoBlend_Dice*") and
val({#Moisture}) in 49.64 to 101 then color(255,147,147) else

if {@Product Type} like "*50/50_*" and
val({#Moisture}) in 0 to 46.36 then crRed else
if {@Product Type} like "*50/50_*" and
val({#Moisture}) in 48.13 to 101 then color(255,147,147) else

if {@Product Type} like "*Ched_Blend_*" and
val({#Moisture}) in 0 to 45.71 then crRed else
if {@Product Type} like "*Ched_Blend_*" and
val({#Moisture}) in 47.73 to 101 then color(255,147,147) else

if ({#Moisture}) like "NR" then crBlue else


crWhite

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[xxyyzz49]

Every place crRed is used increment a counter then set the color. Then create a forumla with the global variable NumRed in it to return its value along with another formula to initialize the variable to 0 and put that formula in the report header.

// report header
Global numbervar NumRed;
NumRed := 0 ;

//segment of code to get the red counter
Global numbervar NumRed;
if ({#Moisture}) like "NR" then crBlue else

if {@Product Type} like "*MozzPS*" and
val({#Moisture}) in 0 to 48.49 then 
(
NumRed := NumRed + 1
crRed 
)
else

if {@Product Type} like "*MozzPS*" and
val({#Moisture}) in 50.51 to 101 then color(255,147,147) else
...

// formula to return the value of the red counter
Global numbervar NumRed;
NumRed;