Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Conditional Formatting w several criteria
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Conditional Formatting w several criteria
 Login/Join
 
Platinum Member
posted
Hi All,

Suppose I have a TABLE and it has several WHERE clauses...
WHERE COLUMN1 EQ &PARAM1 
WHERE COLUMN2 EQ &PARAM2
...
WHERE COLUMN9 EQ &PARAM9

Nothing special there... Now suppose I want to change the TABLE so that it only filters on &PARAM1, and the rest of the parameters are used to highlight rows. So all the records that satisfy COLUMN1 EQ &PARAM1 should display, but the rows shows up with a turquoise background when COLUMN2 EQ &PARAM2 AND COLUMN3 EQ &PARAM3 ... AND COLUMN9 EQ &PARAM9 are ALL satisfied.

I've tried changing the styling information as such:
TYPE= DATA, BACKCOLOR=RGB(200 245 255), WHEN = COLUMN2 EQ $PARAM2, &

And that works, but it all goes to poopy when I start to introduce the other parameters like this:
TYPE= DATA, BACKCOLOR=RGB(200 245 255), WHEN = COLUMN2 EQ $PARAM2 AND COLUMN3 EQ $PARAM3, &

...so I have to conclude that it only likes one operand. A work around (which I found in a Francis response in another thread ) is to create a COMPUTE with the all the parameters and then use that COMPUTEd value in the styling... kinda like this:
TABLE FILE WHATEVER
...
COMPUTE CONDITION1/A10 = IF COLUMN2 EQ &PARAM2 AND COLUMN3 EQ &PARAM3 ... AND COLUMN9 EQ &PARAM9 THEN '1' ELSE '0'; NOPRINT
...
ON TABLE SET STYLE *
TYPE=DATA, BACKCOLOR=RGB(200 245 255), WHEN=CONDITION1 EQ '1', $

And that works well except when some of the parameters are empty (_FOC_NULL). I get an error message saying its an invalid use of FOC_NULL.

Then I read that a work around this is to remove the COMPUTE and put it into a DEFINE, but that didn't work either (I forgot what the issue was there).

I also tried the MACRO route with no success. I can't remember what the problem was there. I've been at this for too long.

So does anyone have a solution to this? All I want to do is highlight some records based on several conditions, some of which may include a _FOC_NULL parameter. Many months ago I remember reading a post where someone doesn't bother with the styling in webfocus at all. He just modifies the CSS... is that a possible solution for me? If so can someone steer me in that direction?

Just a little rant here... IMHO highlighting really should be easy to implement. It should be a standard BI feature.

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8201, SP 0.1, Windows 7, HTML
 
Posts: 138 | Registered: May 19, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You probably have a lot fewer than 9! (9*8*7*6...) combinations of conditions that require a specific colour?

You can play around with the order in which you define the conditional styles. The first match wins, IIRC.
With that in mind, you may be able to reduce the number of combinations you need for your style conditions.
Usually there is a clear order of priority in colouring a field, in which case there is a single condition in each set of combinations that determines the outcome of the combination.

If not, the COMPUTE problem can be solved by adding a new set of parameters based on your inputs, but with _FOC_NULL replaced with some no-op value.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1641 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Gold member
posted Hide Post
Hi,
Have a look at the following example.
This works for me.
Just change the values of one of both the parameters and you will see the background colour changing accordingly.

-SET &ECHO = ALL;

-DEFAULT &PARM1 = '_FOC_NULL';
-DEFAULT &PARM2 = 'A';

DEFINE FILE CAR
FLAG1/D2 = IF '&PARM1.EVAL' NE '_FOC_NULL' AND '&PARM2.EVAL' EQ 'A' THEN 1 ELSE
		   IF '&PARM1.EVAL' EQ '_FOC_NULL' AND '&PARM2.EVAL' EQ 'A' THEN 2 ELSE
		   IF '&PARM1.EVAL' NE '_FOC_NULL' AND '&PARM2.EVAL' NE 'A' THEN 3 ELSE 4;
END


TABLE FILE CAR
PRINT
CAR
FLAG1
MODEL
BY COUNTRY
ON TABLE SET STYLESHEET *
TYPE=REPORT, GRID=OFF, $ 
TYPE=DATA, BACKCOLOR=AQUA, STYLE=BOLD, WHEN=FLAG1 EQ 1, $ 
TYPE=DATA, BACKCOLOR=RED, STYLE=BOLD, WHEN=FLAG1 EQ 2, $ 
TYPE=DATA, BACKCOLOR=GREEN, STYLE=BOLD, WHEN=FLAG1 EQ 3, $ 
TYPE=HEADING, FONT='Arial', STYLE=BOLD, SIZE=11, $
ENDSTYLE
END
  


WF 8.2.04
Windows/Unix
All Formats
In Focus since 2006
 
Posts: 74 | Location: UK | Registered: September 17, 2018Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thanks for the replies.

I actually have 12 parameters... so ya the number of different combinations gets ridiculous.

So I think the only thing I can try is what Wep said about adding new parameters. That's not ridiculous. Seems unnecessary though, but whateves...


WebFOCUS 8201, SP 0.1, Windows 7, HTML
 
Posts: 138 | Registered: May 19, 2017Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Uggh... ok so that route doesn't quite work either. I need to use _FOC_NULL so that when _FOC_NULL is used that condition is just ignored. I tried putting some code that would sort of simulate this, but it still requires a join to be made whereas _FOC_NULL does not... know what I mean? Uggh... I don't know if that's clear. Really frustrating. These extra joins are limiting/shortening the recordset that is returned.

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


WebFOCUS 8201, SP 0.1, Windows 7, HTML
 
Posts: 138 | Registered: May 19, 2017Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Ok... I got it...

So I change the FOC_NULLs to FOC_NONEs...
SET &PARAM2_MOD = IF &PARAM2 EQ '_FOC_NULL' THEN FOC_NONE ELSE &PARAM2;
SET &PARAM3_MOD = IF &PARAM3 EQ '_FOC_NULL' THEN FOC_NONE ELSE &PARAM3;
...
COMPUTE HIGHLIGHT_ME2/I1 = IF 
    (COLUMN2 EQ &PARAM2_MOD)  AND 
    (COLUMN3 EQ &PARAM3_MOD)  AND 
    1 EQ 1
  THEN 1 ELSE 0; NOPRINT  
...
TYPE=DATA, BACKCOLOR=RGB(200 245 255),
   	 WHEN = HIGHLIGHT_ME2 = 1, $


The 1 EQ 1 is important in case only the last parameter is used. Putting each
(COLUMN# EQ &PARAM#_MOD) AND
on a new line is important too 'cause the FOC_NONE ignores the line, and we don't want to ignore all the conditions.

Anyway... still doing a little unit testing... should be good though. I'll post again if I have any more problems.

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


WebFOCUS 8201, SP 0.1, Windows 7, HTML
 
Posts: 138 | Registered: May 19, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
TYPE=DATA, BACKCOLOR=RGB(200 245 255), WHEN = HIGHLIGHT_ME2 = 1, $


That should read:
TYPE=DATA, BACKCOLOR=RGB(200 245 255), WHEN = HIGHLIGHT_ME2 EQ 1, $


Using FOC_NONE looks like a good solution to your problem.


@Addy: You REALLY shouldn't use EVAL like that. Those are user-input parameters (because -DEFAULT) and they could put anything in there, such as malicious code! With EVAL, that will get executed!
You don't need EVAL there either, as Dialog Manager variables are parsed before FOCUS code gets parsed, so the values are substituted just fine without .EVAL already.

Say for example, that a user enters a value for &PARM1 that reads:
';WHERE 1 EQ 0;
END
SQL SQLMSS
DROP TABLE EMPLOYEE;
END
-RUN
DEFINE FILE EMPLOYEE
X/A1024 = '

(The user would probably have to URL-encode that string to get the newlines right, but that is easy!)


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1641 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Gold member
posted Hide Post
@Wep Thanks for the input. Appreciate you explaning it in detail.

Many a times i have noticed that the variable inside single quotes is treated as String and not as a variable and EVAL solves the issue.
In our applications we dont allow users to enter anything (i.e no text boxes)
Also we dont allow any users to run SQL commands.

But again I appreciate you explaining it in detail and I will ensure that it goes in the best practices

Regards
Addy


WF 8.2.04
Windows/Unix
All Formats
In Focus since 2006
 
Posts: 74 | Location: UK | Registered: September 17, 2018Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Conditional Formatting w several criteria

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.