Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Conditional Subtotal

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Conditional Subtotal
 Login/Join
 
Silver Member
posted
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,


8009
Windows 7
Excel/HTML/AHTML/PDF
 
Posts: 34 | Location: San Francisco, CA | Registered: July 25, 2011Report This Post
Silver Member
posted Hide Post
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
 
Posts: 34 | Location: San Francisco, CA | Registered: July 25, 2011Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
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

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Silver Member
posted Hide Post
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 Smiler

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


8009
Windows 7
Excel/HTML/AHTML/PDF
 
Posts: 34 | Location: San Francisco, CA | Registered: July 25, 2011Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Conditional Subtotal

Copyright © 1996-2020 Information Builders