Shown below is a demo program to help illustrate what I'm trying to achieve (I apologise for the lengthy FEX). The FEX should run on any WebFOCUS installation.
I would like to report on a year's worth of data By Quarter Across Month.
To achieve the ACROSS, I defined a field called Month-In-Quarter which I do not want to show on the report - hence the NOPRINT. I would like to show the Actual Month instead. The Attached FEX does not show the Month. How would I do this, hopefully without having to use FRL.
Thanks in advance.
Francis.
SET NODATA=0
-RUN
DEFINE FILE CENTFIN
MTH_IN_QTR/I1 =
IF MONTH IN (1, 4, 7, 10) THEN 1 ELSE
IF MONTH IN (2, 5, 8, 11) THEN 2 ELSE
IF MONTH IN (3, 6, 9, 12) THEN 3;
ITEMX/A20 = SUBSTR(20, ITEM, 2, 20, 19, 'A20');
END
-RUN
TABLE FILE CENTFIN
SUM VALUE
BY ITYPE NOPRINT PAGE-BREAK
BY QUARTER NOPRINT
ACROSS MTH_IN_QTR NOPRINT AND ROW-TOTAL
BY ITEMX
ROWS 'Product'
OVER 'Interest'
OVER 'Inventory'
ON ITYPE SUBTOTAL AS 'Total Type'
ON QUARTER SUBTOTAL AS 'Total'
WHERE YEAR EQ 2000
HEADING
" "
"Type: <ITYPE"
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLESHEET *
TYPE=REPORT, GRID=OFF, FONT='VERDANA', SIZE=8, $
TYPE = REPORT, COLUMN=ROWTOTAL, STYLE=BOLD, COLOR=BLUE, $
TYPE=HEADING, STYLE=BOLD, $
TYPE=SUBTOTAL, STYLE=BOLD, COLOR=BLUE, $
TYPE=ACROSSTITLE, STYLE=BOLD, COLOR=NAVY, JUSTIFY=CENTER, $
TYPE=ACROSSVALUE, STYLE=BOLD, COLOR=NAVY, JUSTIFY=CENTER, $
TYPE=GRANDTOTAL, STYLE=BOLD, $
ENDSTYLE<br />END
This message has been edited. Last edited by: <Mabel>,
June 20, 2005, 04:08 PM
HÃ¥kan
Francis, just add another ACROSS showing the month name, like this:
ACROSS MTH_IN_QTR NOPRINT ACROSS MTH_NAME AS ''
June 20, 2005, 04:56 PM
Francis Mariani
Unfortunately, what that does is to show all 12 months on the same row, what I need is only the three months for each quarter on the same row.
June 20, 2005, 05:18 PM
Francis Mariani
Now, I'm getting somewhere, using the WHEN clause with the SUBHEAD statement.
SET NODATA=0
-RUN
DEFINE FILE CENTFIN
MTH_IN_QTR/I1 =
IF MONTH IN (1, 4, 7, 10) THEN 1 ELSE
IF MONTH IN (2, 5, 8, 11) THEN 2 ELSE
IF MONTH IN (3, 6, 9, 12) THEN 3;
ITEMX/A20 = SUBSTR(20, ITEM, 2, 20, 19, 'A20');
END
-RUN
TABLE FILE CENTFIN
SUM VALUE
BY ITYPE NOPRINT PAGE-BREAK
BY QUARTER NOPRINT
ACROSS MTH_IN_QTR NOPRINT
AND ROW-TOTAL
BY ITEMX
ROWS 'Product'
OVER 'Equipment'
OVER 'Benefits'
OVER 'Interest'
OVER 'Inventory'
ON ITYPE SUBTOTAL AS 'Total'
ON QUARTER SUBTOTAL AS 'Total'
ON QUARTER SUBHEAD
" <+0> Jan <+0> Feb <+0> Mar <+0> Total Q1"
WHEN QUARTER EQ 'Q1'
ON QUARTER SUBHEAD
" <+0> Apr <+0> May <+0> Jun <+0> Total Q2"
WHEN QUARTER EQ 'Q2'
ON QUARTER SUBHEAD
" <+0> Jul <+0> Aug <+0> Sep <+0> Total Q3"
WHEN QUARTER EQ 'Q3'
ON QUARTER SUBHEAD
" <+0> Oct <+0> Nov <+0> Dec <+0> Total Q4"
WHEN QUARTER EQ 'Q4'
WHERE YEAR EQ 2000
HEADING
" "
"Type: <ITYPE"
" "
ON TABLE NOTOTAL
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLESHEET *
TYPE = REPORT, BORDER=1, BORDER-COLOR=SILVER, FONT='ARIAL', SIZE=8, $
TYPE = REPORT, COLUMN=ROWTOTAL, STYLE=BOLD, COLOR=BLUE, $
TYPE=HEADING, STYLE=BOLD, $
TYPE=SUBTOTAL, STYLE=BOLD, COLOR=BLUE, $
TYPE=GRANDTOTAL, STYLE=BOLD, $
TYPE=SUBHEAD, HEADALIGN=BODY, STYLE=BOLD, JUSTIFY=RIGHT, $
TYPE=SUBHEAD, ITEM=5, COLOR=BLUE, $
ENDSTYLE
END
July 17, 2007 - Cleaned up code.This message has been edited. Last edited by: Francis Mariani,