Hello,

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.

Please help!

Thanks,
Aimee

Code:

 TABLE FILE SQLOUT
PRINT 
     ID AS 'CWID'
     NAME AS 'Name'
     AMOUNT/D20.2CM AS 'Amount'
     GIFT_DATE AS 'Gift Date'
     MEMO AS 'Memo'
     TYPE AS 'Type'
BY  DESG AS 'Fund #'
BY  LOWEST DESG_NAME NOPRINT 
BY  LOWEST NAME NOPRINT 
     
ON DESG SUBTOTAL AS '*TOTAL'
     
ON DESG_NAME PAGE-BREAK
HEADING
"<RUN_DATE"
" "
"<TITLE1 to <DESG - <DESG_NAME "
"<TITLE2 "
ON TABLE SET PAGE-NUM ON 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
     PAGESIZE='Letter',
     LEFTMARGIN=0.250000,
     RIGHTMARGIN=0.250000,
     TOPMARGIN=0.250000,
     BOTTOMMARGIN=0.250000,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRAPHCOLOR='GREEN',
$
TYPE=DATA,
     COLUMN=N1,
     SIZE=8,
     BACKCOLOR='NONE',
$
TYPE=DATA,
     COLUMN=N2,
     BACKCOLOR='NONE',
$
TYPE=DATA,
     COLUMN=N8,
     SIZE=8,
     BACKCOLOR='NONE',
$
TYPE=DATA,
     COLUMN=N9,
     SIZE=8,
     BACKCOLOR='NONE',
$
TYPE=DATA,
     COLUMN=N6,
     SIZE=8,
     BACKCOLOR='NONE',
$
TYPE=DATA,
     COLUMN=N7,
     SIZE=8,
     BACKCOLOR='NONE',
$
TYPE=DATA,
     COLUMN=N5,
     SIZE=8,
     BACKCOLOR='NONE',
$
TYPE=DATA,
     COLUMN=N4,
     SIZE=8,
     BACKCOLOR='NONE',
$
TYPE=DATA,
     COLUMN=N3,
     SIZE=8,
     BACKCOLOR='NONE',
$
TYPE=HEADING,
     LINE=1,
     JUSTIFY=LEFT,
$
TYPE=HEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=10,
$
TYPE=HEADING,
     LINE=1,
     OBJECT=FIELD,
     ITEM=1,
     SIZE=11,
$
TYPE=HEADING,
     LINE=2,
     JUSTIFY=LEFT,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=11,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=3,
     SIZE=10,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=4,
     SIZE=10,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=FIELD,
     ITEM=1,
     SIZE=10,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=FIELD,
     ITEM=2,
     SIZE=10,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=FIELD,
     ITEM=3,
     SIZE=10,
$
TYPE=HEADING,
     LINE=3,
     JUSTIFY=LEFT,
$
TYPE=HEADING,
     LINE=3,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=11,
$
TYPE=HEADING,
     LINE=3,
     OBJECT=TEXT,
     ITEM=2,
     SIZE=11,
$
TYPE=HEADING,
     LINE=3,
     OBJECT=TEXT,
     ITEM=3,
     SIZE=11,
$
TYPE=HEADING,
     LINE=3,
     OBJECT=TEXT,
     ITEM=4,
     SIZE=10,
$
TYPE=HEADING,
     LINE=3,
     OBJECT=FIELD,
     ITEM=1,
     SIZE=11,
$
TYPE=HEADING,
     LINE=3,
     OBJECT=FIELD,
     ITEM=2,
     SIZE=11,
$
TYPE=HEADING,
     LINE=3,
     OBJECT=FIELD,
     ITEM=3,
     SIZE=11,
$
TYPE=HEADING,
     LINE=4,
     JUSTIFY=LEFT,
$
TYPE=HEADING,
     LINE=4,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=11,
$
TYPE=HEADING,
     LINE=4,
     OBJECT=FIELD,
     ITEM=1,
     SIZE=11,
$
TYPE=SUBTOTAL,
     BY=1,
     SIZE=8,
	 WHEN=MEMO EQ MISSING,
$
TYPE=SUBTOTAL,
     BY=2,
     SIZE=8,
	 WHEN=MEMO EQ MISSING,
$
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),
$
TYPE=REPORT,
     COLUMN=N1,
     WRAP=0.680556,
$
TYPE=REPORT,
     COLUMN=N2,
     SQUEEZE=0.750000,
$
TYPE=REPORT,
     COLUMN=N7,
     WRAP=0.680556,
$
TYPE=REPORT,
     COLUMN=N5,
     WRAP=1.680556,
$
TYPE=REPORT,
     COLUMN=N4,
     WRAP=0.555556,
$
TYPE=REPORT,
     COLUMN=N3,
     WRAP=0.750000,
$
ENDSTYLE
END
 

This message has been edited. Last edited by: 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

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

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

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

Case closed!