hi friends ,

Our requirements is if the total of a numeric column adds upto a negative value in footing . It should be colored red .

The WHEN Condition in the code does not seem to work for me . Below is my code.
The Element ITEM =5 , LINE =4 IN footing should be red in color as it is negative. but the when condition is not working there.

Any help would be highly appreciated . Thank you for your patience people .

SET COMPOUND = OPEN
SET CENT-ZERO = ON
SET ALL = PASS
SET BYDISPLAY = ON
ENGINE SQLORA SET DEFAULT_CONNECTION MIBDTGT1B
SQL SQLORA PREPARE SQLOUT FOR
SELECT
A.CODE_TYPE_N AS CODE_TYPE,
B.DEPT_I AS DEPT_I,
B.PO_I AS PO_I,
(CASE WHEN A.CODE_TYPE_N = 'TTT' THEN A.BOL_I ELSE A.INVC_I END) AS BOL,
A.INVC_I AS INVC_I,
A.INVC_A AS AMOUNT,
A.CRTE_TS AS TIMESTAMP,
A.INVC_D AS INVC_D,
A.ORIG_PYMT_DUE_D AS ORIG,
SUM (B.EXT_COST_A) AS IEC,
SUM (C.EXT_PROD_PRCRM_MKUP_A) AS IMU,
A.INVC_A AS AMNT,
(CASE WHEN CODE_TYPE_N='TTT' THEN 1 WHEN CODE_TYPE_N='CMP' THEN 2 WHEN CODE_TYPE_N='DED' THEN 3 ELSE 4 END) AS CODE_DUMMY
FROM
FTMMGR.INVC A,
FTMMGR.INVC_ITEM B,
FTMMGR.INVC_ITEM_TPA_DET C
WHERE
A.INVC_SEQ_I=B.INVC_SEQ_I AND
B.INVC_SEQ_I=C.INVC_SEQ_I AND
B.INVC_ITEM_I=C.INVC_ITEM_I
GROUP BY
A.CODE_TYPE_N,
B.DEPT_I,
B.PO_I,
A.INVC_I,
A.BOL_I,
A.INVC_I,
A.CRTE_TS,
A.INVC_D,
A.ORIG_PYMT_DUE_D,
A.INVC_A
Order by
A.CODE_TYPE_N,
B.DEPT_I,
B.PO_I
;
END
-RUN
DEFINE FILE SQLOUT
IEC/D20.2B = IF IEC LT 0 THEN (IEC) ELSE IEC;
IMU/D20.2B = IF IMU LT 0 THEN (IMU) ELSE IMU;
AMNT/P12.3B = IF AMNT LT 0 THEN (AMNT) ELSE AMNT;
IECA/D20.2B = IF CODE_TYPE NE 'DED' THEN 0 ELSE IEC;
IMUA/D20.2B = IF CODE_TYPE NE 'DED' THEN 0 ELSE IMU;
AMNTA/P12.3B = IF CODE_TYPE NE 'DED' THEN 0 ELSE AMNT;
IECB/D20.2B = IF CODE_TYPE NE 'CMP' THEN 0 ELSE IEC;
IMUB/D20.2B = IF CODE_TYPE NE 'CMP' THEN 0 ELSE IMU;
AMNTB/P12.3B = IF CODE_TYPE NE 'CMP' THEN 0 ELSE AMNT;
IECC/D20.2B = IF CODE_TYPE NE 'TTT' THEN 0 ELSE IEC;
IMUC/D20.2B = IF CODE_TYPE NE 'TTT' THEN 0 ELSE IMU;
AMNTC/P12.3B = IF CODE_TYPE NE 'TTT' THEN 0 ELSE AMNT;
NOWTIME/A8 = HHMMSS(NOWTIME);
SUBFOOT1/A40 = 'Total Deduction Invoices';
SUBFOOT2/A40 = 'Total Compare Invoices';
SUBFOOT3/A40 = 'Total Trust To Target Invoices';
SUBFOOT4/A40 = 'Total Invoices';
SUBFOOT5/A80 = 'Total' | ' ' | CODE_TYPE | ' ' | 'Invoices' | ' ' |'For Dpt';
HEADER1A/A30 = 'Job Name : AR 6';
HEADER1B/A30 = 'Run Date '|'&DATEMDYY';
HEADER2A/A30 = 'Report ID 6';
HEADER4A/A30 = 'Company Number : 04';
HEADER4B/A30 = 'Company Name : TSS Trust';
HEADER5A/A30 = 'Invoice Creation Date :';
HEADER5C/A30 = 'Run Time '|NOWTIME;
HEADER6A/A50 = 'Page #';
TIMESTAMP/MDYY=TIMESTAMP;
COUNTER/I7=COUNTER + 1;
FIELD1/A70 = 'Invoice Extended Cost';
FIELD2/A70 = 'Invoice Mark Up';
FIELD3/A70 = 'Total Invoice Amount';
END
TABLE FILE SQLOUT
BY CODE_TYPE AS 'Invoice Type' NOPRINT
BY DEPT_I AS 'Department' NOPRINT
ON DEPT_I SUBFOOT
""
"""
HEADING
""
"DAILY INVOICE CREATION REPORT"
""
""""
"""
" FOOTING
""
""
"""""
"ON TABLE PCHOLD FORMAT EXL2K OPEN
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
FONT='ARIAL',
SIZE=10,
TYPE=REPORT,
TITLETEXT='SUMMARY REPORT ',
$
TYPE=SUBFOOT,
HEADALIGN=BODY,
STYLE=BOLD,
JUSTIFY=RIGHT,
$
TYPE=HEADING,
LINE=9,
ITEM=1,
COLSPAN=10,JUSTIFY=RIGHT,
$
TYPE=SUBFOOT,
ITEM=1,
COLSPAN=8,
JUSTIFY=RIGHT,
$
TYPE=FOOTING,
ITEM=1,
COLSPAN=9,
JUSTIFY=RIGHT,
$
TYPE=SUBFOOT,
LINE=2,
ITEM=4,
COLOR=RED,
WHEN = IEC LT 0,
$
TYPE=FOOTING,
LINE=7,
ITEM=3,
WHEN = IEC LT 0,
$
TYPE=FOOTING,
LINE=7,
ITEM=4,
WHEN = IMU LT 0,
$
TYPE=FOOTING,
LINE=7,
ITEM=5,
WHEN = AMNT LT 0,
$
TYPE=FOOTING,
LINE=3,
ITEM=4,
COLOR=RED,
WHEN=IMUB LT 0,
$
TYPE=FOOTING,
LINE=3,
ITEM=5,
COLOR=RED,
WHEN=AMNTB LT 0,
$
TYPE=FOOTING,
LINE=3,
ITEM=3,
COLOR=RED,
WHEN=IECB LT 0,
$
TYPE=FOOTING,
LINE=4,
ITEM=4,
COLOR=RED,
WHEN=IMUA LT 0,
$
TYPE=FOOTING,
LINE=4,
ITEM=5,
COLOR=RED,
WHEN=AMNTA LT 0,
$
TYPE=FOOTING,
LINE=4,
ITEM=3,
COLOR=RED,
WHEN=IECA LT 0,
$
TYPE=FOOTING,
LINE=5,
ITEM=4,
COLOR=RED,
WHEN=IMUC LT 0,
$
TYPE=FOOTING,
LINE=5,
ITEM=5,
COLOR=RED,
WHEN=AMNTC LT 0,
$
TYPE=FOOTING,
LINE=5,
ITEM=3,
COLOR=RED,
WHEN=IECC LT 0,
$
TYPE=SUBFOOT,
LINE=2,
ITEM=5,
COLOR=RED,
WHEN = IMU LT 0,
$
TYPE=SUBFOOT,
LINE=2,
ITEM=6,
COLOR=RED,
WHEN = AMNT LT 0,
$
TYPE=HEADING,
STYLE=BOLD,
HEADALIGN=BODY,
$
TYPE=HEADING,
LINE=2,
COLSPAN=10,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=4,
ITEM=1,
COLSPAN=3,
JUSTIFY=LEFT,
$
TYPE=HEADING,
LINE=4,
ITEM=2,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=4,
ITEM=3,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=4,
ITEM=4,
COLSPAN=6,
JUSTIFY=RIGHT,
$
TYPE=HEADING,
LINE=5,
ITEM=1,
COLSPAN=3,
JUSTIFY=LEFT,
$
TYPE=HEADING,
LINE=5,
ITEM=2,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=5,
ITEM=4,
COLSPAN=6,
JUSTIFY=RIGHT,
$
TYPE=HEADING,
LINE=7,
ITEM=1,
COLSPAN=10,
JUSTIFY=RIGHT,
$
TYPE=FOOTING,
HEADALIGN=BODY,
STYLE=BOLD,
$
TYPE=FOOTING,
LINE=3,
ITEM=3,
JUSTIFY=RIGHT,
$
TYPE=FOOTING,
LINE=3,
ITEM=4,
JUSTIFY=RIGHT,
$
TYPE=FOOTING,
LINE=3,
ITEM=5,
JUSTIFY=RIGHT,
$
TYPE=FOOTING,
LINE=4,
ITEM=3,
JUSTIFY=RIGHT,
$
TYPE=DATA,
COLUMN=N9,
COLOR=RED,
WHEN=N9 LT 0,
$
TYPE=DATA,
COLUMN=N10,
COLOR=RED,
WHEN=N10 LT 0,
$
TYPE=DATA,
COLUMN=N11,
COLOR=RED,
WHEN=N11 LT 0,
$
TYPE=FOOTING,
LINE=4,
ITEM=4,
JUSTIFY=RIGHT,
$
TYPE=FOOTING,
LINE=4,
ITEM=5,
JUSTIFY=RIGHT,
$
TYPE=FOOTING,
LINE=5,
ITEM=3,
JUSTIFY=RIGHT,
$
TYPE=FOOTING,
LINE=5,
ITEM=4,
JUSTIFY=RIGHT,
$
TYPE=FOOTING,
LINE=5,
ITEM=5,
JUSTIFY=RIGHT,
$
TYPE=FOOTING,
LINE=7,
ITEM=3,
JUSTIFY=RIGHT,
$
TYPE=FOOTING,
LINE=7,
ITEM=4,
JUSTIFY=RIGHT,
$
TYPE=FOOTING,
LINE=7,
ITEM=5,
JUSTIFY=RIGHT,
$
ENDSTYLE
END
TABLE FILE SQLOUT
SUM
IEC/D20.2B AS 'Invoice Extended Cost'
IMU/D20.2B AS 'Invoice Mark Up'
AMNT/P12.3B AS 'Total Invoice Amount'
COMPUTE WORKSHEET/I2=COUNTER/65000; NOPRINT
COMPUTE TITLESHEET/A40='DAILY INVOICE CREATION ' | EDIT(WORKSHEET) ; NOPRINT
BY TOTAL TITLESHEET NOPRINT
BY CODE_TYPE AS 'Invoice Type'
BY DEPT_I AS 'Department'
BY PO_I AS 'Purchase Order'
BY BOL AS 'Invoice Identifier'
BY INVC_I AS 'Invoice Number'
BY TIMESTAMP AS 'Invoice Creation Date'
BY INVC_D AS 'Invoice Date'
HEADING
""
"DAILY INVOICE CREATION REPORT"
""
""""
"""
ON TABLE PCHOLD FORMAT EXL2K BYTOC CLOSE
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
FONT='ARIAL',
SIZE=10,
TYPE=REPORT,
TITLETEXT='DAILY INVOICE CREATION',
$
TYPE=SUBFOOT,
HEADALIGN=BODY,
STYLE=BOLD,
$
TYPE=SUBFOOT,
ITEM=3,
JUSTIFY=RIGHT,
COLOR=RED,
WHEN = IEC LT 0,
$
TYPE=SUBFOOT,
ITEM=4,
JUSTIFY=RIGHT,
COLOR=RED,
WHEN = IMU LT 0,
$
TYPE=SUBFOOT,
ITEM=5,
JUSTIFY=RIGHT,
COLOR=RED,
WHEN = AMNT LT 0,
$
TYPE=HEADING,
STYLE=BOLD,
HEADALIGN=BODY,
$
TYPE=HEADING,
LINE=2,
COLSPAN=10,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=4,
ITEM=1,
COLSPAN=3,
JUSTIFY=LEFT,
$
TYPE=HEADING,
LINE=4,
ITEM=2,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=4,
ITEM=3,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=4,
ITEM=4,
COLSPAN=6,
JUSTIFY=RIGHT,
$
TYPE=HEADING,
LINE=5,
ITEM=1,
COLSPAN=3,
JUSTIFY=LEFT,
$
TYPE=HEADING,
LINE=5,
ITEM=2,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=5,
ITEM=4,
COLSPAN=6,
JUSTIFY=RIGHT,
$
TYPE=HEADING,
LINE=7,
ITEM=1,
COLSPAN=10,
JUSTIFY=RIGHT,
$
TYPE=SUBFOOT,
ITEM=1,
COLSPAN=6,
JUSTIFY=RIGHT,
$
TYPE=TABFOOTING,
HEADALIGN=BODY,
STYLE=BOLD,
$
TYPE=TABFOOTING,
LINE=3,
ITEM=1,
COLSPAN=7,
JUSTIFY=RIGHT,
$
TYPE=TABFOOTING,
LINE=3,
ITEM=2,
JUSTIFY=RIGHT,
$
TYPE=TABFOOTING,
LINE=3,
ITEM=3,
JUSTIFY=RIGHT,
$
TYPE=TABFOOTING,
LINE=3,
ITEM=4,
JUSTIFY=RIGHT,
$
TYPE=TABFOOTING,
LINE=4,
ITEM=2,
JUSTIFY=RIGHT,
$
TYPE=DATA,
COLUMN=N9,
COLOR=RED,
WHEN=N9 LT 0,
$
TYPE=DATA,
COLUMN=N10,
COLOR=RED,
WHEN=N10 LT 0,
$
TYPE=DATA,
COLUMN=N11,
COLOR=RED,
WHEN=N11 LT 0,
$
TYPE=TABFOOTING,
LINE=4,
ITEM=3,
JUSTIFY=RIGHT,
$
TYPE=TABFOOTING,
LINE=4,
ITEM=4,
JUSTIFY=RIGHT,
$
TYPE=TABFOOTING,
LINE=5,
ITEM=2,
JUSTIFY=RIGHT,
$
TYPE=TABFOOTING,
LINE=5,
ITEM=3,
JUSTIFY=RIGHT,
$
TYPE=TABFOOTING,
LINE=5,
ITEM=4,
JUSTIFY=RIGHT,
$
TYPE=TABFOOTING,
LINE=4,
ITEM=1,
COLSPAN=7,
JUSTIFY=RIGHT,
$
TYPE=TABFOOTING,
LINE=5,
ITEM=1,
COLSPAN=7,
JUSTIFY=RIGHT,
$
TYPE=TABFOOTING,
LINE=7,
ITEM=1,
COLSPAN=7,
JUSTIFY=RIGHT,
$
TYPE=TABFOOTING,
LINE=7,
ITEM=2,
JUSTIFY=RIGHT,
$
TYPE=TABFOOTING,
LINE=7,
ITEM=3,
JUSTIFY=RIGHT,
$
TYPE=TABFOOTING,
LINE=7,
ITEM=4,
JUSTIFY=RIGHT,
$
ENDSTYLE
END

Instead of consistently coming on here and asking people to do your job for you,
why not do some searching and find the answer for yourself.

Search on negative values...

Look up what HEADALIGN=BODY actually does.

No verb in sight. Nothing summed. ASNAMES with NOPRINT

amazing...

hi tom

sorry abt that ...darn i dint copy the code properly ....

will redo it now

Arvind,

1. When posting some code YOU MUST ENTER IT BETWEEN TAGS (the last icon on the ribbon) otherwise some of your code disappears.

2. When you have a problem, reproduce it on one of the IBI files, CAR for example.

3. Had you done this, you would have seen that it is a very simple problem:

  
-* File arvind6.fex
DEFINE FILE CAR
RCOSTN/D7=IF SALES EQ 0 THEN -RCOST ELSE RCOST;
END
TABLE FILE CAR
SUM RCOST RCOSTN
BY COUNTRY
BY CAR
BY BODY
ON COUNTRY SUBFOOT
"Total for <COUNTRY <ST.RCOST<ST.RCOSTN"
" "
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=SUBFOOT, HEADALIGN=BODY,$
TYPE=SUBFOOT, ITEM=4, JUSTIFY=RIGHT, $
TYPE=SUBFOOT, ITEM=5, JUSTIFY=RIGHT, $
TYPE=SUBFOOT, ITEM=5, COLOR=RED, WHEN=RCOSTN LT 0, $
ENDSTYLE
END

thanks for the tip,

Daniel..

hi friends ,

Thanks a lot for your help ..

I solved this issue . I used some commmon sense which is totally uncommon for me..

Sorry again Tom ...

ciao

Arvind,

There is no need to apologize. The more you learn yourself, via the search, manuals, testing sample code from here and understanding "everything" the code is doing, the better off, yaddayaddayadda.

Glad you figured it out...

AND, I apologize if came across a bit harsh, but, highlighting negative values is pretty simple...