Hello,

I am trying to achieve cellular level formatting for certain fields from my table, for example:

COL1 COL2 COL3 COL4 COL5 COL6
BOLD BOLD&Underline TEXTABC $45 TEXTDEF www.google.com
- BOLD TEXTXYZ $0 TEXTLMN $10
- - TEXTASD 5% TEXTGHJ 2.2%

............
and so on

That is how my table looks for example - based on col1 & col2 I have to format the fields of the other columns.
So IF COL1 & ROW1 = BOLD THEN make ROW1 in COL3 as BOLD
IF COL2 & ROW1 = BOLD+UNDERLINE THEN make ROW1 in COL5 as BOLD & Underline

Can we do this in WebFocus - cellular level formatting?

I have to display 4 columns on a PDF page - COL3, COL4, COL5 & COL6 - with few rows in between as BOLD etc depending on the clues/values from COL1 & COL2.

Any ideas/advise? Thanks!

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

COL1    COL2              COL3         COL4         COL5        COL6
BOLD    BOLD&Underline    TEXTABC       $45         TEXTDEF     www.google.com
 -      BOLD              TEXTXYZ       $0          TEXTLMN      $10
 -       -                TEXTASD       5%          TEXTGHJ      2.2%

Trying to post this again...

You can create a conditional style based on whatever threshold you want. Look up MACTYPE or Conditional Styling in the doc. In InfoAssist it's an icon called Traffic Lights. For instance:

TABLE FILE ibisamp/car
SUM CAR.BODY.SEATS
BY CAR.ORIGIN.COUNTRY
BY CAR.CARREC.MODEL
BY CAR.COMP.CAR
BY CAR.BODY.BODYTYPE
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
DEFMACRO=Condition_1, MACTYPE=RULE, WHEN=CAR EQ 'BMW', $
TYPE=DATA, COLUMN=CAR, MACRO=Condition_1, FONT='ARIAL', SIZE=14, COLOR=RGB(0 0 255), STYLE=BOLD, HYPERLINK-COLOR=RGB(0 0 255), $
ENDSTYLE
END

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

quote:

Originally posted by BabakNYC:
You can create a conditional style based on whatever threshold you want. Look up MACTYPE or Conditional Styling in the doc. In InfoAssist it's an icon called Traffic Lights. For instance:

TABLE FILE ibisamp/car
SUM CAR.BODY.SEATS
BY CAR.ORIGIN.COUNTRY
BY CAR.CARREC.MODEL
BY CAR.COMP.CAR
BY CAR.BODY.BODYTYPE
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
DEFMACRO=Condition_1, MACTYPE=RULE, WHEN=CAR EQ 'BMW', $
TYPE=DATA, COLUMN=CAR, MACRO=Condition_1, FONT='ARIAL', SIZE=14, COLOR=RGB(0 0 255), STYLE=BOLD, HYPERLINK-COLOR=RGB(0 0 255), $
ENDSTYLE
END

Is it possible to do this without hardcoding the CAR value 'BMW' - can I use ROW reference?

Your choices are either a constant or a Field. So you could test

DEFMACRO=Condition_1, MACTYPE=RULE, WHEN=CAR EQ NEW_CAR, $

where NEW_CAR is another column in the table or something you DEFINEd.

And by the way, you don't have to always say EQ you can use whatever logical operator you want. So, you could say Not EQ (NE) or Greater Than (GT).

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

Thank you so much for your help!

So this maybe a dumb question - but how different is doing this

TYPE=DATA, COLUMN=MODEL, STYLE=BOLD, WHEN=CAR EQ 'BMW',$

as opposed to using macros or MACTYPE?

You can apply Macros to individual columns once you've defined them. In your example you're applying it to all columns by saying TYPE=DATA.

I mentioned COLUMN=MODEL in my statement - so I am giving it a specific column to focus on

There are many ways to accomplish the task. Yours works just as well. The key is WHEN= syntax.

TABLE FILE ibisamp/car
SUM CAR.BODY.SEATS
BY CAR.ORIGIN.COUNTRY
BY CAR.CARREC.MODEL
BY CAR.COMP.CAR
BY CAR.BODY.BODYTYPE
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$

TYPE=DATA, COLUMN=MODEL, STYLE=BOLD, WHEN=CAR EQ 'BMW', FONT='ARIAL', SIZE=14, COLOR=RGB(0 0 255), HYPERLINK-COLOR=RGB(0 0 255), $
ENDSTYLE
END

Somehow when I use this in my code, it formats a particular row for all columns instead of formatting that particular row for only 1 column

For example, I have to format the value 'ABC' in COL1 as BOLD - so I used:

TYPE=DATA, COLUMN=COL1, STYLE=BOLD, BACKCOLOR=SILVER, WHEN=COL1 EQ 'ABC',$

But this highlighted the whole row for value 'ABC' as silver - I want only ABC in COL1 highlighted and bold.
I tried using the DEFMACRO & MACTYPE commands too, same result

What did I do wrong?

I am using this conditional formatting in PDF report - is that the reason for this?

Also, I just noticed that the STYLE=BOLD applies only to ABC in COL1 (as expected), but BACKCOLOR=SILVER is the issue - the entire row has SILVER backcolor.

It should work for any output type, if you have the column name correct.

I use the macro technique very rarely - only when the condition is repeated in several lines of code.

An example:

SET BYDISPLAY=ON

TABLE FILE CAR
SUM
SALES
BY COUNTRY
BY CAR
BY MODEL

ON TABLE PCHOLD FORMAT PDF

ON TABLE SET STYLE *
TYPE=REPORT, FONT=CALIBRI, SIZE=9, $
TYPE=DATA, COLUMN=CAR, STYLE=BOLD, BACKCOLOR=SILVER, WHEN=CAR EQ 'ALFA ROMEO', $
END
-RUN
-RUN

Here a sample using MACROs and where you can either apply styling to a specific column or row

DEFINE FILE CAR
SEATS5    /A1 = IF SEATS   EQ 5 THEN 'Y' ELSE 'N';
ENGLAND   /A1 = IF COUNTRY EQ 'ENGLAND' THEN 'Y' ELSE 'N';
ENGSEATS2 /A1 = IF COUNTRY EQ 'ENGLAND' AND SEATS EQ 2 THEN 'Y' ELSE 'N';
BMWSEATS4 /A1 = IF CAR     EQ 'BMW'     AND SEATS EQ 4 THEN 'Y' ELSE 'N';
END
TABLE FILE CAR
PRINT SEATS
      SEATS5    NOPRINT
      ENGLAND   NOPRINT
      ENGSEATS2 NOPRINT
      BMWSEATS4 NOPRINT
BY COUNTRY
BY CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET BYDISPLAY ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
     DEFMACRO=ROWSEATS5,
     MACTYPE=RULE,
     WHEN=SEATS5 EQ 'Y',
$
     DEFMACRO=ENGCNTRY,
     MACTYPE=RULE,
     WHEN=ENGLAND EQ 'Y',
$
     DEFMACRO=ENG_SEATS2,
     MACTYPE=RULE,
     WHEN=ENGSEATS2 EQ 'Y',
$
     DEFMACRO=BMW_SEATS4,
     MACTYPE=RULE,
     WHEN=BMWSEATS4 EQ 'Y',
$
TYPE=DATA,
     COLOR=BLUE,
     MACRO=ROWSEATS5,
$
TYPE=DATA,
     COLUMN=SEATS,
     COLOR=RED,
     MACRO=ENG_SEATS2,
$
TYPE=DATA,
     COLUMN=COUNTRY,
     BACKCOLOR=YELLOW,
     MACRO=ENGCNTRY,
$
TYPE=DATA,
     COLUMN=COUNTRY,
     BACKCOLOR=GREEN,
     COLOR=WHITE,
     MACRO=BMW_SEATS4,
$
TYPE=DATA,
     COLUMN=SEATS,
     BACKCOLOR=GREEN,
     COLOR=WHITE,
     MACRO=BMW_SEATS4,
$
ENDSTYLE
END
-RUN

Thank you guys for all your help!

There were some minor changes I had to make inorder for the conditional formatting to work. That was making all the columns for a particular row to have backcolor = silver.

I had given positions to my columns (for example: COL1 IN 5, COL2 IN 20, etc...) in my fex since I needed them to appear in a certain layout, but I got rid of the "IN position" statements and adjusted the positions using SQUEEZE/WRAP later.

Doing that made it all better - conditional formatting is working as expected.

Thanks again!

You can style individual cells when you do a FML(Financial Modeling Language) report. This requires the FOR keyword. Rows are referenced by labels.

  
TABLE FILE CAR
SUM 
     DEALER_COST
     RETAIL_COST
     SALES
FOR
     CAR
'AUDI' AS 'AUDI' LABEL R1 OVER 
'BMW' AS 'BMW' LABEL R2  
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
$
TYPE=DATA,
     LABEL=R2,
     COLUMN=N4,
     BACKCOLOR='YELLOW',
$
ENDSTYLE
END

The basic problem is that WebFOCUS styling is not cascading - you sometimes have to repeat styling for different elements...

Repetition:

...
TYPE=SUBTOTAL, STYLE=BOLD, BORDER-LEFT=LIGHT, BORDER-RIGHT=LIGHT, $
TYPE=SUBTOTAL, COLUMN=DAILYVALUE, BORDER-LEFT=OFF, $
TYPE=SUBTOTAL, COLUMN=MTDVALUE  , BORDER-LEFT=OFF, $
TYPE=SUBTOTAL, COLUMN=YTDVALUE  , BORDER-LEFT=OFF, $

TYPE=SUBTOTAL, BY=DIMENSION2_TOT_LABEL, STYLE=NORMAL, $

TYPE=SUBTOTAL, COLUMN=DAILYVALUE, BORDER-LEFT=OFF, WHEN=DAILYVALUE GE 0, $
TYPE=SUBTOTAL, COLUMN=MTDVALUE  , BORDER-LEFT=OFF, WHEN=MTDVALUE GE 0, $
TYPE=SUBTOTAL, COLUMN=YTDVALUE  , BORDER-LEFT=OFF, WHEN=YTDVALUE GE 0, $

TYPE=SUBTOTAL, BY=DIMENSION1_TOT_LABEL, COLUMN=DIMENSION3, BORDER-TOP=LIGHT, BACKCOLOR=RGB(253 233 217), WHEN=DAILYVALUE LT 0, $
TYPE=SUBTOTAL, BY=DIMENSION1_TOT_LABEL, COLUMN=DAILYVALUE, COLOR=RED, BORDER-TOP=LIGHT, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, BACKCOLOR=RGB(253 233 217), WHEN=DAILYVALUE LT 0, $
TYPE=SUBTOTAL, BY=DIMENSION1_TOT_LABEL, COLUMN=MTDVALUE  , COLOR=RED, BORDER-TOP=LIGHT, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, BACKCOLOR=RGB(253 233 217), WHEN=MTDVALUE   LT 0, $
TYPE=SUBTOTAL, BY=DIMENSION1_TOT_LABEL, COLUMN=YTDVALUE  , COLOR=RED, BORDER-TOP=LIGHT, BORDER-LEFT=OFF, BACKCOLOR=RGB(253 233 217), WHEN=YTDVALUE LT 0, $

TYPE=SUBTOTAL, BY=DIMENSION1_TOT_LABEL, COLUMN=DIMENSION3, BORDER-TOP=LIGHT, BACKCOLOR=RGB(253 233 217), WHEN=DAILYVALUE GE 0, $
TYPE=SUBTOTAL, BY=DIMENSION1_TOT_LABEL, COLUMN=DAILYVALUE, BORDER-TOP=LIGHT, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, BACKCOLOR=RGB(253 233 217), WHEN=DAILYVALUE GE 0, $
TYPE=SUBTOTAL, BY=DIMENSION1_TOT_LABEL, COLUMN=MTDVALUE  , BORDER-TOP=LIGHT, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, BACKCOLOR=RGB(253 233 217), WHEN=MTDVALUE   GE 0, $
TYPE=SUBTOTAL, BY=DIMENSION1_TOT_LABEL, COLUMN=YTDVALUE  , BORDER-TOP=LIGHT, BORDER-LEFT=OFF, BACKCOLOR=RGB(253 233 217), WHEN=YTDVALUE GE 0, $

TYPE=SUBTOTAL, BY=DIMENSION2_TOT_LABEL, COLUMN=DAILYVALUE, COLOR=RED, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, WHEN=DAILYVALUE LT 0, $
TYPE=SUBTOTAL, BY=DIMENSION2_TOT_LABEL, COLUMN=MTDVALUE  , COLOR=RED, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, WHEN=MTDVALUE   LT 0, $
TYPE=SUBTOTAL, BY=DIMENSION2_TOT_LABEL, COLUMN=YTDVALUE  , COLOR=RED, BORDER-LEFT=OFF, WHEN=YTDVALUE LT 0, $

TYPE=SUBTOTAL, BY=DIMENSION3_TOT_LABEL, COLUMN=DAILYVALUE, COLOR=RED, BORDER-TOP=LIGHT, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, WHEN=DAILYVALUE LT 0, $
TYPE=SUBTOTAL, BY=DIMENSION3_TOT_LABEL, COLUMN=MTDVALUE  , COLOR=RED, BORDER-TOP=LIGHT, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, WHEN=MTDVALUE   LT 0, $
TYPE=SUBTOTAL, BY=DIMENSION3_TOT_LABEL, COLUMN=YTDVALUE  , COLOR=RED, BORDER-TOP=LIGHT, BORDER-LEFT=OFF, WHEN=YTDVALUE LT 0, $

TYPE=SUBTOTAL, BY=DIMENSION3_TOT_LABEL, COLUMN=DIMENSION3, BORDER-TOP=LIGHT, WHEN=SHADING_IND EQ 'NO', $
TYPE=SUBTOTAL, BY=DIMENSION3_TOT_LABEL, COLUMN=DIMENSION4, BORDER-TOP=LIGHT, WHEN=SHADING_IND EQ 'NO', $
TYPE=SUBTOTAL, BY=DIMENSION3_TOT_LABEL, COLUMN=DAILYVALUE, BORDER-TOP=LIGHT, WHEN=SHADING_IND EQ 'NO', $
TYPE=SUBTOTAL, BY=DIMENSION3_TOT_LABEL, COLUMN=MTDVALUE  , BORDER-TOP=LIGHT, WHEN=SHADING_IND EQ 'NO', $
TYPE=SUBTOTAL, BY=DIMENSION3_TOT_LABEL, COLUMN=YTDVALUE  , BORDER-TOP=LIGHT, WHEN=SHADING_IND EQ 'NO', $

TYPE=SUBTOTAL, BY=DIMENSION3_TOT_LABEL, COLUMN=DIMENSION3, BORDER-TOP=LIGHT, BACKCOLOR=RGB(232 232 232), WHEN=SHADING_IND EQ 'YES', $
TYPE=SUBTOTAL, BY=DIMENSION3_TOT_LABEL, COLUMN=DIMENSION4, BORDER-TOP=LIGHT, BACKCOLOR=RGB(232 232 232), WHEN=SHADING_IND EQ 'YES', $
TYPE=SUBTOTAL, BY=DIMENSION3_TOT_LABEL, COLUMN=DAILYVALUE, BORDER-TOP=LIGHT, BACKCOLOR=RGB(232 232 232), WHEN=SHADING_IND EQ 'YES', $
TYPE=SUBTOTAL, BY=DIMENSION3_TOT_LABEL, COLUMN=MTDVALUE  , BORDER-TOP=LIGHT, BACKCOLOR=RGB(232 232 232), WHEN=SHADING_IND EQ 'YES', $
TYPE=SUBTOTAL, BY=DIMENSION3_TOT_LABEL, COLUMN=YTDVALUE  , BORDER-TOP=LIGHT, BACKCOLOR=RGB(232 232 232), WHEN=SHADING_IND EQ 'YES', $
...

I really wish there was an option to format the entire ROW with a condition, instead of repeating syntax for each column.

Just out of curiosity, I tried this but it failed. Can someone tell me what am I missing here?

 
-SET &I=1;
TABLE FILE CAR
SUM SALES
DEALER_COST
BY COUNTRY
BY CAR
BY MODEL
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET BYDISPLAY ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
-REPEAT PTEST WHILE &I LT 5;
TYPE=DATA,
COLUMN=N&I ,
BACKCOLOR='YELLOW',
WHEN=COUNTRY EQ 'JAPAN',
$
-PTEST
ENDSTYLE
END 

Using this format the entire row according to condition

TABLE FILE CAR
SUM SALES
DEALER_COST
BY COUNTRY
BY CAR
BY MODEL
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET BYDISPLAY ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
TYPE=DATA,
BACKCOLOR='YELLOW',
WHEN=COUNTRY EQ 'JAPAN',
$
ENDSTYLE
END

But this also work, however it does for 5 columns only

TABLE FILE CAR
SUM SALES
DEALER_COST
BY COUNTRY
BY CAR
BY MODEL
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET BYDISPLAY ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
-REPEAT PTEST FOR &I FROM 1 TO 5;
TYPE=DATA,
COLUMN=N&I.EVAL,
BACKCOLOR='YELLOW',
WHEN=COUNTRY EQ 'JAPAN',
$
-PTEST
ENDSTYLE
END