I'm attempting to have a subtotal of $ amount for each desg (fund #), but counting only those without a "Y" in the MEMO column. MEMO has a Y or null value.
I tried right-clicking on the SUBTOTAL column in the GUI to add a conditional styling where MEMO does not equal Y, but it didn't work.
I tried changing my code to contain this language:
$
TYPE=SUBTOTAL,
BY=1,
SIZE=8,
WHEN=MEMO NE 'Y',
$
TYPE=SUBTOTAL,
BY=2,
SIZE=8,
WHEN=MEMO NE 'Y',
$
In place of WHEN=MEMO NE 'Y', I also tried...
WHEN=MEMO EQ MISSING, and WHEN=MEMO EQ '',
None of them work and it totals everything, including the records with a "Y" in the MEMO column. I want to keep the value null, but I'm not sure how to get the subtotal to only total the gifts with a null value in MEMO.
This message has been edited. Last edited by: a415,
8009 Windows 7 Excel/HTML/AHTML/PDF
September 25, 2013, 05:38 PM
a415
Example using EMPDATA sample file, where I want the subtotal to EXCLUDE those with WE in DIV column:
TABLE FILE EMPDATA
PRINT
EMPDATA.EMPDATA.DEPT
EMPDATA.EMPDATA.LASTNAME
EMPDATA.EMPDATA.FIRSTNAME
EMPDATA.EMPDATA.DIV
EMPDATA.EMPDATA.TITLE
EMPDATA.EMPDATA.SALARY
EMPDATA.EMPDATA.HIREDATE/MDYY
BY EMPDATA.EMPDATA.DEPT NOPRINT
ON EMPDATA.EMPDATA.DEPT SUBTOTAL AS '*TOTAL'
WHERE ( EMPDATA.EMPDATA.DIV EQ 'NE' ) OR ( EMPDATA.EMPDATA.DIV EQ 'WE' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
PAGESIZE='Letter',
LEFTMARGIN=0.250000,
RIGHTMARGIN=0.250000,
ORIENTATION=LANDSCAPE,
$
DEFMACRO=COND0001,
MACTYPE=RULE,
WHEN=N5 NE 'WE',
$
TYPE=REPORT,
GRAPHCOLOR='GREEN',
$
TYPE=SUBTOTAL,
SIZE=8,
WHEN=DIV NE 'WE',
$
TYPE=REPORT,
OBJECT=MENU,
COLOR='WHITE',
HOVER-COLOR=RGB(66 70 73),
BACKCOLOR=RGB(102 102 102),
HOVER-BACKCOLOR=RGB(218 225 232),
BORDER-COLOR='WHITE',
$
TYPE=REPORT,
OBJECT=STATUS-AREA,
COLOR='WHITE',
BACKCOLOR=RGB(102 102 102),
$
TYPE=REPORT,
OBJECT=CURRENT-ROW,
HOVER-BACKCOLOR=RGB(218 225 232),
BACKCOLOR=RGB(200 200 200),
$
TYPE=REPORT,
OBJECT=CALC-AREA,
COLOR='WHITE',
BACKCOLOR=RGB(102 102 102),
$
ENDSTYLE
END
8009 Windows 7 Excel/HTML/AHTML/PDF
September 25, 2013, 06:40 PM
Francis Mariani
You cannot manipulate the amounts in the subtotal using the stylesheet - that is used for styling only.
You cannot manipulate the amounts in the subtotal - the subtotal is an exact sum of the column. People use SUBFOOT to resolve this.
TABLE FILE EMPDATA
SUM
COMPUTE SALARY_NON_WE/D10.2M = IF EMPDATA.EMPDATA.DIV EQ 'WE' THEN 0 ELSE EMPDATA.EMPDATA.SALARY; NOPRINT
BY EMPDATA.EMPDATA.DEPT NOPRINT
BY EMPDATA.EMPDATA.DIV NOPRINT
SUM
EMPDATA.EMPDATA.SALARY
MIN.EMPDATA.EMPDATA.HIREDATE/MDYY
BY EMPDATA.EMPDATA.DEPT NOPRINT
BY EMPDATA.EMPDATA.DIV NOPRINT
BY EMPDATA.EMPDATA.DEPT
BY EMPDATA.EMPDATA.LASTNAME
BY EMPDATA.EMPDATA.FIRSTNAME
BY EMPDATA.EMPDATA.DIV
BY EMPDATA.EMPDATA.TITLE
ON EMPDATA.EMPDATA.DEPT SUBFOOT
"*TOTAL <DEPT <+> <+> <ST.SALARY_NON_WE"
" "
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
WHERE ( EMPDATA.EMPDATA.DIV EQ 'NE' ) OR ( EMPDATA.EMPDATA.DIV EQ 'WE' )
ON TABLE SET STYLE *
TYPE=SUBFOOT, HEADALIGN=BODY, $
TYPE=SUBFOOT, ITEM=6, JUSTIFY=RIGHT, $
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
September 26, 2013, 01:02 PM
Danny-SRL
This should do the trick:
-* File a415_01.fex
TABLE FILE CAR
PRINT SALES SEATS
COMPUTE A415/I6=IF SEATS EQ 2 THEN 0 ELSE SALES; NOPRINT
COMPUTE TOTCOUNTRY/A16='Total ' | COUNTRY; NOPRINT
BY COUNTRY
ON COUNTRY SUBFOOT
"<TOTCOUNTRY<ST.A415 "
ON TABLE SET STYLE *
TYPE=SUBFOOT, HEADALIGN=BODY, $
TYPE=SUBFOOT, ITEM=2, JUSTIFY=RIGHT, $
ENDSTYLE
END
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
September 27, 2013, 01:55 PM
a415
Thanks for your help everyone and for the info about not being able to do manipulation with the Stylesheet! You think I would have figured that out
I appreciate all of the help! It's been a busy week, so I'm just getting around to testing. So far, I was able to get Danny-SRL's code to work with mine. Thanks!!