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,

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

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

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,
$

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,
$

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.

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.

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.

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

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;  

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.

As CAWarner stated ... this works as well ...

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

Or this ...

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