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
April 25, 2019, 12:38 PM
jfr99
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
April 25, 2019, 12:57 PM
jfr99
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
April 25, 2019, 01:29 PM
Jen Moser
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.
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
April 25, 2019, 02:45 PM
Jen Moser
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
April 25, 2019, 03:28 PM
jfr99
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
April 25, 2019, 04:30 PM
Jen Moser
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
April 26, 2019, 09:18 AM
CAWarner
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
April 26, 2019, 09:32 AM
jfr99
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
April 26, 2019, 09:37 AM
jfr99
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
April 26, 2019, 09:39 AM
jfr99
Or this ...
TABLE FILE CAR
SUM SALES
ACROSS COUNTRY
BY CAR
ON TABLE ROW-TOTAL
WHERE TOTAL SALES NE 0
END