Hi All,

I'm stuck with writing the logic in FOCUS, please help me getting rid of this.

If data satisfies Conditon1 then back color should be green, else data satisfies Condition2 then its yellow else red.



Condtion1/Condition3 has values of <=,>=, <,>.So simply, data has to directly compare with column values.

Note: Condition columns are Alpha and data is Decimal.

This message has been edited. Last edited by: Rifaz,

I've just recently been working on some KPIs that have similar logic to change their background color based on KPI % variance performance. (A lot of this logic I found in the 8.1.03 Responsive Demo from IBI.)

The if/else part of my code:

-SET &CLASS = IF (&PCTDIFF GT 0) THEN 'green-alert' ELSE
-IF (&PCTDIFF LT 0) THEN 'red-alert' ELSE 'orange-alert';

Which corresponds to this HTML div tag within my -HTMLFORM section of my .fex:

<div class="!IBI.AMP.CLASS;" style="border-radius: 0px; padding-top: 5px; padding-bottom: 5px">

This code basically allows for conditional CSS styling of element(s) within my KPI. I link to an external .css file and then reference the classes I want used based on the % variance value.

Hope this helps.

Rifaz,

I would normally handle that in the style sheet. Look into conditional styling in the help file.

Rifaz,
you can use save file to store the define field with necessary if then else logic and further call that save file in the style sheet directly.
that should work..Please let me know if that makes sense, else I will try to prepare sample code for you..

thanks,
Vivek

The issue I see with Vivek's suggestion is that Condition2 is a compound condition, which cannot be used in a StyleSheet WHEN clause. If I understand the problem correctly, I believe the only way to approach it is by parsing Conditions 1/2/3 in DEFINEs and determining the appropriate background color for each value also in a DEFINE. The DEFINEs can then be used in StyleSheet WHEN clauses to set the background colors for the cells. But an important piece of information is missing from your post. Do the values in every cell of the table have a different field name, or do all of the values in each column have the same field name? For example, do all of the values in the January column (43.52, 17.7) come from the same database field, or does each value in that column come from a different database field?

This message has been edited. Last edited by: Dan Satchell,

Coolguy, I saw your other post, hard for me to understand it now. I thought the solution would be straight using IF/ELSE & Conditional Styling.Thanks BTW... Will look into it again.

Eric, Yes, I'm trying to attain using the same, but stuck with writing the IF..ELSE logic.

Vivek - Can you please post the sample code?

Dan,

Condition1, Condition2, Condition3 are coming from different columns in DB and it's format is Alpha, so I have to convert to Decimal using ATODBL function before comparing with ACTUAL values(Jan-15,Feb-15,Mar-15) are coming from same database column named 'DATA'. Prior to this, HOLD DATA field using ACROSS, so I can print from DATA1 to DATA12 in final TABLE FILE. I have no problem doing so.

I think, we can simply ignore the condition2 and keep it in (last) ELSE part. We may just need to compare DATA values with Condition1 else DATA values with Condition3 else Condition2.

This message has been edited. Last edited by: Rifaz,

Here's an example of some DEFINEs that could be used to determine the BACKCOLOR for each value in a row of data (none of this code has been tested, so it might need some tweaking). As I see it, the problem comes in the StyleSheet when you try to identify which set of conditions apply to which row in your output table. For this approach to work I am assuming that you have saved the number of lines (&LINES) from your HOLD file.

DEFINE FILE XXX
-* Add a sequence number for use in the SyleSheet.
 SEQ_NBR/I5 = LAST SEQ_NBR + 1 ;
-*
-* Remove spaces from Condition1.
 COND1_STRIP_SPACES/A50 = STRIP(50,CONDITION1,' ','A50');
-* Remove '=' signs.
 COND1_STRIP_EQUAL/A50  = STRIP(50,COND1_STRIP_SPACES,'=','A50');
-* Remove '<' signs.
 COND1_STRIP_LE/A50     = STRIP(50,COND1_STRIP_EQUAL,'<','A50');
-* Remove '>' signs.
 COND1_STRIP_GE/A50     = STRIP(50,COND1_STRIP_LE,'<','A50');
-* Remove percent signs.
 COND1_STRIP_PCT/A50    = STRIP(50,COND1_STRIP_GE,'%','A50');
-* Reduce size of field to something suitable for conversion to decimal number.
 COND1_CUT/A12          = EDIT(COND1_STRIP_PCT,'9999999999999$');
-* Convert alpha number to decimal number.
 COND1_NUMERIC/D15.2    = ATODBL(COND1_CUT,'12',D15.2);
-* Convert percentages to their decimal equivalents (depending on the format of the data values, this step may not be necessary).
 COND1_NUMBER/D15.2     = IF (CONDITION1 CONTAINS '%') THEN (COND1_NUMERIC / 100) ELSE COND1_NUMERIC ;
-*
-* Remove spaces from Condition2.
 COND2_STRIP_SPACES/A50 = STRIP(50,CONDITION2,' ','A50');
-* Remove word 'between'.
 COND2_STRIP_WORD/A50   = STRREP(50,COND2_STRIP_SPACES,7,'between',0,'x',50,'A50');
-* Replace word 'and' with single character for use in function GETTOK.
 COND2_REPLACE_WORD/A50 = STRREP(50,COND2_STRIP_WORD,3,'and',1,'Z',50,'A50');
-* Get first number.
 COND2_NUM1/A6          = GETTOK(COND2_REPLACE_WORD,50,1,'Z',6,'A6');
-* Get second number.
 COND2_NUM2/A6          = GETTOK(COND2_REPLACE_WORD,50,2,'Z',6,'A6');
-* Convert first alpha number to decimal number.
 COND2_NUMBER1/D9.2     = ATODBL(COND2_NUM1,'06',D9.2);
-* Convert second alpha  number to decimal number.
 COND2_NUMBER2/D9.2     = ATODBL(COND2_NUM2,'06',D9.2);
-*
-* Remove spaces from Condition3.
 COND3_STRIP_SPACES/A50 = STRIP(50,CONDITION3,' ','A50');
-* Remove '=' signs.
 COND3_STRIP_EQUAL/A50  = STRIP(50,COND3_STRIP_SPACES,'=','A50');
-* Remove '<' signs.
 COND3_STRIP_LE/A50     = STRIP(50,COND3_STRIP_EQUAL,'<','A50');
-* Remove '>' signs.
 COND3_STRIP_GE/A50     = STRIP(50,COND3_STRIP_LE,'<','A50');
-* Remove percent signs.
 COND3_STRIP_PCT/A50    = STRIP(50,COND3_STRIP_GE,'%','A50');
-* Reduce size of field to something suitable for conversion to decimal number.
 COND3_CUT/A12          = EDIT(COND3_STRIP_PCT,'9999999999999$');
-* Convert alpha number to decimal number.
 COND3_NUMERIC/D15.2    = ATODBL(COND3_CUT,'12',D15.2);
-* Convert percentages to their decimal equivalents (depending on the format of the data values, this step may not be necessary).
 COND3_NUMBER/D15.2     = IF (CONDITION3 CONTAINS '%') THEN (COND3_NUMERIC / 100) ELSE COND3_NUMERIC ;
-*
-* Determine the color for each monthly data value.
-REPEAT ENDREPEAT1 FOR &M FROM 1 TO 12
 MONTH&M._COLOR/A6 = IF (CONDITION1 LIKE '%<%=%') AND (DATA&M LE COND1_NUMBER) THEN 'GREEN' ELSE
                     IF (CONDITION1 LIKE '%>%=%') AND (DATA&M GE COND1_NUMBER) THEN 'GREEN' ELSE
                     IF (CONDITION1 LIKE '%<%')   AND (DATA&M LT COND1_NUMBER) THEN 'GREEN' ELSE
                     IF (CONDITION1 LIKE '%>%')   AND (DATA&M GT COND1_NUMBER) THEN 'GREEN' ELSE
                     IF (CONDITION1 LIKE '%=%')   AND (DATA&M EQ COND1_NUMBER) THEN 'GREEN' ELSE
                     IF (CONDITION1 UNLIKE '%=%' OR '%<%' OR '%>%') AND (DATA&M EQ COND1_NUMBER) THEN 'GREEN' ELSE
                     IF (DATA&M FROM COND2_NUMBER1 TO COND2_NUMBER2) THEN 'YELLOW' ELSE
                     IF (CONDITION3 LIKE '%<%=%') AND (DATA&M LE COND3_NUMBER) THEN 'RED' ELSE
                     IF (CONDITION3 LIKE '%>%=%') AND (DATA&M GE COND3_NUMBER) THEN 'RED' ELSE
                     IF (CONDITION3 LIKE '%<%')   AND (DATA&M LT COND3_NUMBER) THEN 'RED' ELSE
                     IF (CONDITION3 LIKE '%>%')   AND (DATA&M GT COND3_NUMBER) THEN 'RED' ELSE
                     IF (CONDITION3 LIKE '%=%')   AND (DATA&M EQ COND3_NUMBER) THEN 'RED' ELSE
                     IF (CONDITION3 UNLIKE '%=%' OR '%<%' OR '%>%') AND (DATA&M EQ COND3_NUMBER) THEN 'RED' ELSE 'WHITE';
-ENDREPEAT1
END
-*
TABLE FILE XXX
 PRINT DATA1 ...... DATA12
 SEQ_NBR NOPRINT
-*
-REPEAT ENDREPEAT2 FOR &M FROM 1 TO 12
 MONTH&M._COLOR NOPRINT
-ENDREPEAT2
-*
 BY .....

 ON TABLE SET STYLE *
-*
-REPEAT ENDREPEAT3 FOR &R FROM 1 TO &LINES
-REPEAT ENDREPEAT4 FOR &M FROM 1 TO 12
  TYPE=DATA, COLUMN=DATA&M, BACKCOLOR=MONTH&M._COLOR, WHEN=SEQ_NBR EQ &R, $
-ENDREPEAT4
-ENDREPEAT3
-*
 ENDSTYLE
END

This message has been edited. Last edited by: Dan Satchell,

Thank you so much Dan!

I had the similar plan, but to segregate the conditons(LE,LT,GE,GT) and read it in variable to do the comparision. Also, to omit the Condition2 but mine is not efficient one. Something like below..

COMPUTE AS1/A8= STRIP(7, CONDITION1, ' ', 'A8'); NOPRINT
COMPUTE S1/A8= STRIP(8, AS1, '%', 'A8'); NOPRINT
COMPUTE C1/A10 = IF S1 LIKE '>=%' THEN EDIT(STRREP(8,S1,2,'>=',2,'GE',10,C1),'99')
				ELSE IF S1 LIKE '<=%' THEN EDIT(STRREP(8,S1,2,'<=',2,'LE',10,C1),'99')
				ELSE IF S1 LIKE '>%' THEN EDIT(STRREP(8,S1,1,'>',2,'GT',10,C1),'99')
				ELSE IF S1 LIKE '<%' THEN EDIT(STRREP(8,S1,1,'<',2,'LT',10,C1),'99')
			    ELSE ('EQ' ||EDIT((S1),'$$')); 

COMPUTE CN1/A10 = IF S1 LIKE '>=%' THEN EDIT(STRREP(8,S1,2,'>=',2,'GE',10,CN1),'$$99999')
				ELSE IF S1 LIKE '<=%' THEN EDIT(STRREP(8,S1,2,'<=',2,'LE',10,CN1),'$$99999')
				ELSE IF S1 LIKE '>%' THEN EDIT(STRREP(8,S1,1,'>',2,'GT',10,CN1),'$$99999')
				ELSE IF S1 LIKE '<%' THEN EDIT(STRREP(8,S1,1,'<',2,'LT',10,CN1),'$$99999')
				ELSE S1; NOPRINT

COMPUTE DCML/D6.2 = ATODBL(CN1,'10',DCML);
:
:
-REPEAT :ENDREPEAT1 FOR &I FROM 1 TO &LINES
-* CLA file holds the conditions of 1 & 3
-READ CLA, NOCLOSE &CDN1.A2. &CDN2.A2.
-TYPE &CDN1 &CDN2
COMPUTE FLAG&I/A1 = IF DATA&I &CDN1 DCML THEN 'T' ELSE 'F';
-:ENDREPEAT1
  

Yours is simple and easy to understand, worked like a charm. Here is my final code..

DEFINE FILE US_ALL
EVENODD/I3 = IMOD(CATEGORYORDER,2,'I3');
FLAG_C/A1=IF EVENODD EQ 0 THEN 'Y' ELSE 'N';
-* Remove spaces from Condition1.
 COND1_STRIP_SPACES/A50 = STRIP(50,C1,' ','A50');
-* Remove '=' signs.
 COND1_STRIP_EQUAL/A50  = STRIP(50,COND1_STRIP_SPACES,'=','A50');
-* Remove '<' signs.
 COND1_STRIP_LE/A50     = STRIP(50,COND1_STRIP_EQUAL,'<','A50');
-* Remove '>' signs.
 COND1_STRIP_GE/A50     = STRIP(50,COND1_STRIP_LE,'>','A50');
-* Remove percent signs.
 COND1_STRIP_PCT/A50    = STRIP(50,COND1_STRIP_GE,'%','A50');
-* Reduce size of field to something suitable for conversion to decimal number.
 COND1_CUT/A6          = EDIT(COND1_STRIP_PCT,'99999$');
-* Convert alpha number to decimal number.
-* COND1_NUMERIC/D6.2    = ATODBL(COND1_CUT,'06',COND1_NUMERIC);
 COND1_NUMBER/D6.2    = ATODBL(COND1_CUT,'06',COND1_NUMBER);
-* Convert percentages to their decimal equivalents (depending on the format of the data values, this step may not be necessary).
-* COND1_NUMBER/D6.2     = IF (C1 CONTAINS '%') THEN (COND1_NUMERIC / 100) ELSE COND1_NUMERIC ;
-*
-* Remove spaces from Condition2.
 COND2_STRIP_SPACES/A50 = STRIP(50,C2,' ','A50');
-* Remove word 'between'.
 COND2_STRIP_WORD/A50   = STRREP(50,COND2_STRIP_SPACES,7,'between',0,'x',50,'A50');
-* Replace word 'and' with single character for use in function GETTOK.
 COND2_REPLACE_WORD/A50 = STRREP(50,COND2_STRIP_WORD,3,'and',1,'Z',50,'A50');
-* Get first number.
 COND2_NUM1/A6          = GETTOK(COND2_REPLACE_WORD,50,1,'Z',6,'A6');
-* Get second number.
 COND2_NUM2/A6          = GETTOK(COND2_REPLACE_WORD,50,2,'Z',6,'A6');
-* Convert first alpha number to decimal number.
 COND2_NUMBER1/D6.2     = ATODBL(COND2_NUM1,'06',COND2_NUMBER1);
-* Convert second alpha  number to decimal number.
 COND2_NUMBER2/D6.2     = ATODBL(COND2_NUM2,'06',COND2_NUMBER2);
-*
-*Following the similar steps like Condition1
 COND3_STRIP_SPACES/A50 = STRIP(50,C3,' ','A50');
 COND3_STRIP_EQUAL/A50  = STRIP(50,COND3_STRIP_SPACES,'=','A50');
 COND3_STRIP_LE/A50     = STRIP(50,COND3_STRIP_EQUAL,'<','A50');
 COND3_STRIP_GE/A50     = STRIP(50,COND3_STRIP_LE,'>','A50');
 COND3_STRIP_PCT/A50    = STRIP(50,COND3_STRIP_GE,'%','A50');
 COND3_CUT/A6         = EDIT(COND3_STRIP_PCT,'99999$');
 COND3_NUMBER/D6.2    = ATODBL(COND3_CUT,'06',COND3_NUMBER);
-*
-* Determine the color for each monthly data value.
-REPEAT ENDREPEAT1 FOR &I FROM 1 TO &howmany
 FLAG&I._COLOR/A6 = IF DAT&I EQ 0.00 THEN 'WHITE' ELSE IF (C1 LIKE '%<%=%') AND (DAT&I LE COND1_NUMBER) THEN 'GREEN' ELSE
                     IF (C1 LIKE '%>%=%') AND (DAT&I GE COND1_NUMBER) THEN 'GREEN' ELSE
                     IF (C1 LIKE '%<%')   AND (DAT&I LT COND1_NUMBER) THEN 'GREEN' ELSE
                     IF (C1 LIKE '%>%')   AND (DAT&I GT COND1_NUMBER) THEN 'GREEN' ELSE
                     IF (C1 LIKE '%=%')   AND (DAT&I EQ COND1_NUMBER) THEN 'GREEN' ELSE
                     IF (C1 UNLIKE '%=%' OR '%<%' OR '%>%') AND (DAT&I EQ COND1_NUMBER) THEN 'GREEN' ELSE
                     IF (DAT&I FROM COND2_NUMBER1 TO COND2_NUMBER2) THEN 'YELLOW' ELSE
                     IF (C3 LIKE '%<%=%') AND (DAT&I LE COND3_NUMBER) THEN 'RED' ELSE
                     IF (C3 LIKE '%>%=%') AND (DAT&I GE COND3_NUMBER) THEN 'RED' ELSE
                     IF (C3 LIKE '%<%')   AND (DAT&I LT COND3_NUMBER) THEN 'RED' ELSE
                     IF (C3 LIKE '%>%')   AND (DAT&I GT COND3_NUMBER) THEN 'RED' ELSE
                     IF (C3 LIKE '%=%')   AND (DAT&I EQ COND3_NUMBER) THEN 'RED' ELSE
                     IF (C3 UNLIKE '%=%' OR '%<%' OR '%>%') AND (DAT&I EQ COND3_NUMBER) THEN 'RED' ELSE 'WHITE';
-ENDREPEAT1
END
TABLE FILE US_ALL
SUM
-REPEAT :LP FOR &I FROM 1 TO &howmany
-READ SAVE NOCLOSE &MONTH.20
-SET &FIELDS=DECODE &MONTH('DAT1' 'DAT1/D6.2S AS ''Jan-&Yr'''  'DAT2' 'DAT2/D6.2S AS ''Feb-&Yr''' 'DAT3' 'DAT3/D6.2S AS ''Mar-&Yr''' 
'DAT4' 'DAT4/D6.2S AS ''Apr-&Yr''' 'DAT5' 'DAT5/D6.2S AS ''May-&Yr''' 'DAT6' 'DAT6/D6.2S AS ''Jun-&Yr''' 'DAT7' 'DAT7/D6.2S AS ''Jul-&Yr''' 
'DAT8' 'DAT8/D6.2S AS ''Aug-&Yr''' 'DAT9' 'DAT9/D6.2S AS ''Sep-&Yr''' 'DAT10' 'DAT10/D6.2S AS ''Oct-&Yr''' 'DAT11' 'DAT11/D6.2S AS ''Nov-&Yr''' 
'DAT12' 'DAT12/D6.2S AS ''Dec-&Yr''' ELSE '');
&FIELDS.EVAL
FLAG&I._COLOR NOPRINT
-:LP
:
:
:
:
-REPEAT :BCR FOR &I FROM 1 TO &howmany
TYPE=DATA,COLUMN=DAT&I,STYLE=BOLD,JUSTIFY=CENTER, BACKCOLOR=WHITE,WHEN=FLAG&I._COLOR EQ 'WHITE',$
TYPE=DATA,COLUMN=DAT&I,STYLE=BOLD,JUSTIFY=CENTER, BACKCOLOR=GREEN,WHEN=FLAG&I._COLOR EQ 'GREEN',$
TYPE=DATA,COLUMN=DAT&I,STYLE=BOLD,JUSTIFY=CENTER, BACKCOLOR=YELLOW,WHEN=FLAG&I._COLOR EQ 'YELLOW',$
TYPE=DATA,COLUMN=DAT&I,STYLE=BOLD,JUSTIFY=CENTER, BACKCOLOR=RED,WHEN=FLAG&I._COLOR EQ 'RED',$
-:BCR
  

However, I wondered when you assign a DEFINE field name to BACKCOLOR instead of values. So, I tweaked my code as mentioned above to make it work.
TYPE=DATA, COLUMN=DATA&M, BACKCOLOR=MONTH&M._COLOR, WHEN=SEQ_NBR EQ &R, $

Thanks again for looking into it!!!

This message has been edited. Last edited by: Rifaz,

I'm glad I was able to help and that you were able to find a solution. It was an interesting problem.