[SOLVED] COLUMN_TOTALS above the column instead of below
I have a requirement to produce a report in EXL07 which has the column totals above the column instead of below. Where I am having a problem is getting this column total to have text color red when the value is less than zero. In this code, everything works, the totals are placed above the appropriate column, I just haven't figured out how to get the text color when the value is less than zero. Thank you in advance for any and all assistance.
This message has been edited. Last edited by: BGronli,
7.7, z/OS & Win7, excel & pdf 8.2 testing
June 14, 2017, 07:33 AM
MartinY
TABLE FILE CAR
SUM RETAIL_COST
BY COUNTRY
ON TABLE SUMMARIZE
ON TABLE SET SUBTOTALS ABOVE
-* Or
-*ON TABLE SET SUBTOTAL ABOVE
END
-RUN
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
June 16, 2017, 12:27 PM
BGronli
Thanks Martin. I have tested out both and using SUMMARIZE is close, however, it puts the totals below the column totals, instead of above. Using 'ON TABLE SET SUBTOTAL ABOVE' did not produce a subtotal line at all.
Due to the requirements for this I need the totals to be three lines above the column titles and I need them to be red when the subtotal is less than zero.
The search continues.
7.7, z/OS & Win7, excel & pdf 8.2 testing
June 16, 2017, 01:38 PM
FP Mod Chuck
Hi BGronli
Maybe think about using the alignment grid in the page heading and use Hope this helps,
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
June 16, 2017, 01:45 PM
MartinY
Here is the closest that I can give you. You have must of the things that you need to play with. I put SUBHEAD and SUBFOOT but on or the other may be used. Both may not be needed. It only depend if you want a blank line between Grand-Total and first Sub-Total. SKIP-LINE can also be used. since I don't have negative data, I put the condition as for GT 10000, you can change for LT 0. Having the Sub-Total ABOVE the column's title, I don't think that is possible (in a simple way) since a Sub-Total is part of the data and the column's title displays ABOVE data.
TABLE FILE CAR
PRINT RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL
-*ON COUNTRY SKIP-LINE
ON COUNTRY SUBHEAD
""
ON COUNTRY SUBFOOT
""
ON COUNTRY SUBTOTAL AS 'Sub-Total : '
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SUBTOTALS ABOVE
ON TABLE COLUMN-TOTAL AS 'Grand-Total : '
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
DEFMACRO=ST_NEG,
MACTYPE=RULE,
WHEN=RETAIL_COST GT 10000,
$
TYPE=SUBTOTAL,
COLUMN=RETAIL_COST,
COLOR=RED,
MACRO=ST_NEG,
$
TYPE=SUBHEAD,
BORDER=OFF,
$
TYPE=SUBFOOT,
BORDER=OFF,
$
ENDSTYLE
END
-RUN
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
June 19, 2017, 04:12 AM
Danny-SRL
Hi BGronli,
Instead of using in your PRINT: APPR_GEN/P17.2BMC etc
use a DEFINE to change the format of you fields
DEFINE FILE BDGTOFCR APPR_GEN/P17.2BMC=APPR_GEN; ... END
and in the TABLE PRINT APPR_GEN without a format
The reason for this is that when you change the format in the TABLE request, you are in fact creating a COMPUTE which means that for each field (e.g. APPR_GEN) you have 2 fields. I suppose that is why your style is not applied. Worth a try...
Example with the CAR file:
-* File bgronli.fex
DEFINE FILE CAR
PRESALE/I6BMC=SALES - 10000;
END
TABLE FILE CAR
HEADING
" "
"Totals: <TOT.PRESALE"
" "
PRINT PRESALE
BY COUNTRY
ON TABLE SET STYLE *
TYPE=HEADING, HEADALIGN=BODY, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, ITEM=1, COLSPAN=2, $
TYPE=HEADING, LINE=3, ITEM=1, COLSPAN=2, $
TYPE=DATA, COLUMN=PRESALE, COLOR=RED, WHEN=PRESALE LT 0,$
ENDSTYLE
END
This message has been edited. Last edited by: Danny-SRL,
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
June 19, 2017, 10:42 AM
BGronli
Thank you Danny. While this does allow the formatting for the field to carry to the heading, it still does not change the COLOR of the field in the HEADING to red when the total is LT 0.
7.7, z/OS & Win7, excel & pdf 8.2 testing
June 20, 2017, 09:03 AM
Danny-SRL
Sorry, I didn't see that you wanted the total in the HEADING to be colored.
-* File bgronli.fex
DEFINE FILE CAR
PRESALE/I6=SALES - 10000;
END
TABLE FILE CAR
HEADING
" "
"Totals: <TOT.PRESALE"
" "
SUM TOT.PRESALE NOPRINT
PRINT PRESALE
BY COUNTRY
ON TABLE SET STYLE *
TYPE=HEADING, HEADALIGN=BODY, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, ITEM=1, COLSPAN=2, $
TYPE=HEADING, LINE=3, ITEM=1, COLSPAN=2, $
TYPE=HEADING, LINE=2, OBJECT=FIELD, ITEM=1, color=red, WHEN=TOT.PRESALE LT 30000, $
TYPE=DATA, COLUMN=PRESALE, COLOR=RED, WHEN=PRESALE LT 0,$
ENDSTYLE
END
... should do the trick
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
June 21, 2017, 10:27 AM
BGronli
That still did not do the trick, however, changes similar to what I have entered here did. I had to set-up the COMPUTE statement for the total and then use that in the TYPE=HEADING style. Thanks for the help and the responses.
DEFINE FILE CAR
PRESALE/I6=SALES - 10000;
END
TABLE FILE CAR
HEADING
" "
"Totals: <TOTPRESALE"
" "
PRINT PRESALE
COMPUTE TOTPRESALE/I6 = TOT.PRESALE; NOPRINT
BY COUNTRY
ON TABLE SET STYLE *
TYPE=HEADING, HEADALIGN=BODY, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, ITEM=1, COLSPAN=2, $
TYPE=HEADING, LINE=3, ITEM=1, COLSPAN=2, $
TYPE=HEADING, LINE=2, OBJECT=FIELD, ITEM=1, color=red, WHEN=TOTPRESALE LT 30000, $
TYPE=DATA, COLUMN=PRESALE, COLOR=RED, WHEN=PRESALE LT 0,$
ENDSTYLE
END
7.7, z/OS & Win7, excel & pdf 8.2 testing
June 22, 2017, 03:38 AM
Danny-SRL
Interesting that my code did not work for you. On my machine it does in 8.1 and 8.2. If I may be so bold and ask did you run my code as-is?This message has been edited. Last edited by: Danny-SRL,
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF