Hello,

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

Francis, just add another ACROSS showing the month name, like this:

ACROSS MTH_IN_QTR NOPRINT
ACROSS MTH_NAME AS ''

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.

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,