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     [CLOSED] Conditional Formatting Formula
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Conditional Formatting Formula
 Login/Join
 
Member
posted
I have a field in my report that is a concatenation, AMT_CUST/A10V = NEW_VALUE | '-' | IFS_CUSTOMER_ID;, and I'd like to format the data based on only the IFS_CUSTOMER_ID, which would be the last 4 characters of my concatenation. I have the below code for my conditional formatting but I'm getting an error for UNBALANCED PARENTHESES. Am I able to use the formula for my "when" in conditional formatting? We are using App studio 8201

TYPE=DATA,
BACKCOLOR=RGB(255 192 0),
STYLE=BOLD,
WHEN=EDIT(AMT_CUST,'$$$9999') EQ '1001',
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
$

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


8201
 
Posts: 26 | Location: MN | Registered: June 27, 2016Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Here's an example using the CAR file ...

DEFINE FILE CAR
FLAG/A1 = IF CAR IN ('JAGUAR','DATSUN','AUDI') THEN 'Y' ELSE 'N';
NEW_FIELD/A50V = COUNTRY || ('-' | CAR);
END
-*
TABLE FILE CAR
PRINT
FLAG
NEW_FIELD
BY COUNTRY
BY CAR
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     DEFMACRO=FLAGIT,
     MACTYPE=RULE,
     WHEN=FLAG EQ 'Y',
$
TYPE=DATA,
    COLUMN=NEW_FIELD,
    BACKCOLOR=RGB(255 192 0),
	STYLE=BOLD,
	BORDER-LEFT=LIGHT,
	BORDER-RIGHT=LIGHT,
	BORDER-TOP=LIGHT,
	BORDER-BOTTOM=LIGHT,
    MACRO=FLAGIT,
$
END


WebFocus 8.201M, Windows, App Studio
 
Posts: 221 | Location: Lincoln Nebraska | Registered: August 12, 2008Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Another example ...

DEFINE FILE CAR
FLAG/A1 = IF CAR IN ('JAGUAR','DATSUN','AUDI') THEN 'Y' ELSE 'N';
NEW_FIELD/A50V = COUNTRY || ('-' | CAR);
END
-*
TABLE FILE CAR
PRINT
FLAG NOPRINT
NEW_FIELD
BY COUNTRY
BY CAR
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=DATA,
    WHEN=FLAG EQ 'Y',
    BACKCOLOR=RGB(255 192 0),
    STYLE=BOLD,
    BORDER-LEFT=LIGHT,
    BORDER-RIGHT=LIGHT,
    BORDER-TOP=LIGHT,
    BORDER-BOTTOM=LIGHT,
$
END


WebFocus 8.201M, Windows, App Studio
 
Posts: 221 | Location: Lincoln Nebraska | Registered: August 12, 2008Reply With QuoteReport This Post
Member
posted Hide Post
I don't think that's going to work. We have quite a few customers so I have the below section written each time for a new customer. So the "'1001'" changes, it's not a constant because each customer will have a different color.

TYPE=DATA,
BACKCOLOR=RGB(255 192 0),
STYLE=BOLD,
WHEN=EDIT(AMT_CUST,'$$$9999') EQ '1001',
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
$
TYPE=DATA,
BACKCOLOR=RGB(255 0 0),
WHEN=EDIT(AMT_CUST,'$$$9999') EQ '1002',
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
$


8201
 
Posts: 26 | Location: MN | Registered: June 27, 2016Reply With QuoteReport This Post
Platinum Member
posted Hide Post
It doesn't like the EDIT function in the WHEN statement. Could you do this ...

TYPE=DATA,
	BACKCOLOR=RGB(255 192 0),
	STYLE=BOLD,
	WHEN=IFS_CUSTOMER_ID EQ '1001',
	BORDER-LEFT=LIGHT,
	BORDER-RIGHT=LIGHT,
	BORDER-TOP=LIGHT,
	BORDER-BOTTOM=LIGHT,
$
TYPE=DATA,
	BACKCOLOR=RGB(255 0 0),
	WHEN=IFS_CUSTOMER_ID EQ '1002',
	BORDER-LEFT=LIGHT,
	BORDER-RIGHT=LIGHT,
	BORDER-TOP=LIGHT,
	BORDER-BOTTOM=LIGHT,
$


WebFocus 8.201M, Windows, App Studio
 
Posts: 221 | Location: Lincoln Nebraska | Registered: August 12, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Jen

It may be worth your time to create a cross reference table of customer to color and have a fex that does the lookup prior to the report and set a parameter you can substitute in the style sheet.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1566 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Member
posted Hide Post
jfr,

That's what I had initially, but it won't work because I don't have IFS_CUSTOMER_ID in my report as it's own field. If I do it that way, my columns get all crazy. I don't know how to post pictures on here, but if I did it might be easier to understand.


8201
 
Posts: 26 | Location: MN | Registered: June 27, 2016Reply With QuoteReport This Post
Platinum Member
posted Hide Post
You can add field IFS_CUSTOMER_ID to your report with NOPRINT after it ... this will make it available to your WHEN clause but not be printed on your output. One of the examples above had the NOPRINT option on the field FLAG.

Hopefully that will work.


WebFocus 8.201M, Windows, App Studio
 
Posts: 221 | Location: Lincoln Nebraska | Registered: August 12, 2008Reply With QuoteReport This Post
Member
posted Hide Post
ok, I think I got that part to work! Thank you! Another question....I have ROW-TOTAL, but is there a way to exclude the rows where the total is equal to 0? I tried WHERE ROW-TOTAL NE 0 but that doesn't work

TABLE FILE FINLDATA
SUM
VALUE
BY COST_CENTER NOPRINT
BY IFS_CUSTOMER_ID NOPRINT
BY COSTCENTER AS ' '
ACROSS WK_BEG NOPRINT
-*ACROSS CURRENT_WEEK NOPRINT
ACROSS FINLDATA.FINLDATA.YEAR NOPRINT
ACROSS FINLDATA.FINLDATA.MTH NOPRINT
ACROSS FINLDATA.FINLDATA.DAY1 NOPRINT
ACROSS FINLDATA.FINLDATA.MONTH_NAME AS 'Week Beginning Date'
ACROSS FINLDATA.FINLDATA.DAY1 AS ''
ON TABLE ROW-TOTAL AS 'Total'
WHERE ROW-TOTAL NE 0;
ON TABLE HEADING
"&HEADING_INFO"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE SET FORMULTIPLE ON
ON TABLE SET AUTOFIT ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(,,,,,,,,,,,,,,,).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$


8201
 
Posts: 26 | Location: MN | Registered: June 27, 2016Reply With QuoteReport This Post
Member
posted Hide Post
Jen,

The command you are looking for is:

 WHERE TOTAL {FIELD} NE 0

or

WHERE TOTAL {FIELD} GT 0  


This will filter out rows that fail the logical test AFTER your SUM statement is complete. in your case I think all you need is:

WHERE TOTAL VALUE NE 0;  


WebFOCUS 8202
Windows, All Outputs
 
Posts: 12 | Registered: June 16, 2016Reply With QuoteReport This Post
Platinum Member
posted Hide Post
You need a (WHERE TOTAL sum_field NE 0) ... but as you said WHERE ROW-TOTAL NE 0 doesn't work.

And the following doesn't work either (at least I couldn't get them to work) ...

WHERE TOTAL ROW-TOTAL NE 0
WHERE TOTAL ACROSS-TOTAL NE 0

So ... Here is an example using the CAR file that might work for you ...

TABLE FILE CAR
SUM SALES NOPRINT
BY CAR
SUM SALES
BY CAR
ACROSS COUNTRY
ACROSS-TOTAL
WHERE TOTAL SALES NE 0
END


The trick here is adding ...

SUM SALES NOPRINT
BY CAR

and then using ...

WHERE TOTAL SALES NE 0

This will exclude any CAR lines that have zero SALES (but the SALES field will not print on the report.

Hopefully this will work for you.


WebFocus 8.201M, Windows, App Studio
 
Posts: 221 | Location: Lincoln Nebraska | Registered: August 12, 2008Reply With QuoteReport This Post
Platinum Member
posted Hide Post
As CAWarner stated ... this works as well ...

TABLE FILE CAR
SUM SALES
BY CAR
ACROSS COUNTRY
ACROSS-TOTAL
WHERE TOTAL SALES NE 0
END


WebFocus 8.201M, Windows, App Studio
 
Posts: 221 | Location: Lincoln Nebraska | Registered: August 12, 2008Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Or this ...

TABLE FILE CAR
SUM SALES
ACROSS COUNTRY
BY CAR
ON TABLE ROW-TOTAL
WHERE TOTAL SALES NE 0
END


WebFocus 8.201M, Windows, App Studio
 
Posts: 221 | Location: Lincoln Nebraska | Registered: August 12, 2008Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Conditional Formatting Formula

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