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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Subtotal count
 Login/Join
 
Platinum Member
posted
Hi,
Is there a way to include a count of a field within the subtotal. I have four fields - Name, date, accnt no. and amount. I want a subtotal off of the name summing up amount which I can already do. However, within that subtotal, I want to count the number of account no. for each name. Here is my code:
  DEFINE FILE REFUNDS
NEWMDY/A10=EDIT(DATE, '99/99/9999');
TYPE/A10=DECODE REFUNDTYPE( 98 'Override' 97 'Bank Error' ELSE 'Other' );
END
TABLE FILE REFUNDS
PRINT
     REFUNDAMT/D10.2M AS 'Refund Amount'
BY FULLNAME AS 'Name'
BY NEWMDY AS 'Date Posted'
BY ACCTNUMBER AS 'Accnt No.'

ON FULLNAME SUBTOTAL
     SUM. REFUNDAMT AS '*TOTAL'
HEADING
"&TEAM<+0>'S<+0> <+0>&TYPE<+0> Refund Report"
"From <+0>&dte<+0> through <+0>&edte"
FOOTING
""
WHERE MARKETTEAM EQ '&TEAM';
WHERE TYPE EQ '&TYPE';
WHERE NEWMDY GE '&dte' AND NEWMDY LE '&edte';
ON TABLE SET PAGE-NUM OFF
ON TABLE SUMMARIZE REFUNDS.REFUNDS.REFUNDAMT AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     PAGESIZE='Letter',
     LEFTMARGIN=0.500000,
     RIGHTMARGIN=0.500000,
     TOPMARGIN=0.500000,
     BOTTOMMARGIN=0.500000,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     FONT='ARIAL',
     SIZE=10,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=ITALIC,
     RIGHTGAP=0.125000,
     TOPGAP=0.013889,
     BOTTOMGAP=0.027778,
$
TYPE=DATA,
     BORDER-LEFT=MEDIUM,
     BORDER-RIGHT=MEDIUM,
     BACKCOLOR='WHITE',
$
TYPE=DATA,
     COLUMN=ROWTOTAL(1),
     STYLE=BOLD+ITALIC,
$
TYPE=TITLE,
     BORDER-TOP=MEDIUM,
     BORDER-BOTTOM=MEDIUM,
     BORDER-LEFT=MEDIUM,
     BORDER-RIGHT=MEDIUM,
     COLOR='WHITE',
     BACKCOLOR='BLACK',
     STYLE=BOLD+ITALIC,
$
TYPE=TITLE,
     COLUMN=ROWTOTAL(1),
     BORDER-TOP=MEDIUM,
     BORDER-BOTTOM=MEDIUM,
     BORDER-LEFT=MEDIUM,
     BORDER-RIGHT=MEDIUM,
     COLOR='WHITE',
     STYLE=BOLD+ITALIC,
$
TYPE=TABHEADING,
     SIZE=16,
     STYLE=BOLD+ITALIC,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD+ITALIC,
$
TYPE=HEADING,
     SIZE=16,
     STYLE=BOLD+ITALIC,
$
TYPE=HEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     FONT='PHOTINA CASUAL BLACK',
     COLOR=RGB(51 102 255),
$
TYPE=HEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=2,
     FONT='PHOTINA CASUAL BLACK',
     COLOR=RGB(51 102 255),
$
TYPE=HEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=3,
     FONT='PHOTINA CASUAL BLACK',
$
TYPE=HEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=4,
     FONT='PHOTINA CASUAL BLACK',
     COLOR='RED',
$
TYPE=HEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=5,
     FONT='PHOTINA CASUAL BLACK',
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=1,
     FONT='PHOTINA CASUAL BLACK',
     SIZE=12,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=2,
     FONT='PHOTINA CASUAL BLACK',
     SIZE=12,
     COLOR='RED',
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=3,
     FONT='PHOTINA CASUAL BLACK',
     SIZE=12,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=4,
     FONT='PHOTINA CASUAL BLACK',
     SIZE=12,
     COLOR='RED',
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD+ITALIC,
$
TYPE=SUBTOTAL,
     STYLE=BOLD+ITALIC,
$
TYPE=ACROSSVALUE,
     ACROSS=1,
     BORDER-TOP=MEDIUM,
     BORDER-BOTTOM=MEDIUM,
     BORDER-LEFT=MEDIUM,
     BORDER-RIGHT=MEDIUM,
     COLOR='WHITE',
     BACKCOLOR='BLACK',
     STYLE=ITALIC,
     TOPGAP=0.013889,
     BOTTOMGAP=0.027778,
$
TYPE=ACROSSVALUE,
     COLUMN=ROWTOTAL(1),
     BACKCOLOR='WHITE',
     STYLE=BOLD+ITALIC,
$
TYPE=ACROSSTITLE,
     COLOR='WHITE',
     BACKCOLOR='BLACK',
     STYLE=BOLD+ITALIC,
     TOPGAP=0.013889,
     BOTTOMGAP=0.027778,
$
TYPE=ACROSSTITLE,
     ACROSS=1,
     BORDER-TOP=MEDIUM,
     BORDER-BOTTOM=MEDIUM,
     BORDER-LEFT=MEDIUM,
     BORDER-RIGHT=MEDIUM,
     STYLE=BOLD+ITALIC,
     TOPGAP=0.013889,
     BOTTOMGAP=0.027778,
$
TYPE=GRANDTOTAL,
     COLOR='WHITE',
     BACKCOLOR='BLACK',
     STYLE=BOLD+ITALIC,
     TOPGAP=0.013889,
     BOTTOMGAP=0.027778,
$
ENDSTYLE
END


I appreciate any help on this.

Thanks!


WebFocus 7.7.02 WinXP
 
Posts: 236 | Registered: May 12, 2006Report This Post
Virtuoso
posted Hide Post
quote:
PRINT
REFUNDAMT/D10.2M AS 'Refund Amount'
BY FULLNAME AS 'Name'
BY NEWMDY AS 'Date Posted'


You might try inserting a computed field with noprint option and then print it in the subfoot

COMPUTE ACCTS/I4 = IF FULLNAME EQ LAST FULLNAME THEN LAST AACTS + 1 ELSE 1; NOPRINT


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Platinum Member
posted Hide Post
Thanks for the reply. I get these errors:
0 ERROR AT OR NEAR LINE 13 IN PROCEDURE newrefundteamroll.
(FOC281) ALPHA ARGUMENTS IN PLACE WHERE NUMERIC ARE CALLED FOR
0 ERROR AT OR NEAR LINE 13 IN PROCEDURE newrefundteamroll
(FOC125) RECAP CALCULATIONS MISSING
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

This seems this should be so simple. All I want to do is to place somewhere the number of accounts to fullname next to the amount total.

Joe


WebFocus 7.7.02 WinXP
 
Posts: 236 | Registered: May 12, 2006Report This Post
Guru
posted Hide Post
Joe,

If you want to count the different account numbers, try CNT.DST.Acctnumber otherwise try
CNT.Acctnumber on your subtotal line.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Virtuoso
posted Hide Post
Joe,
For each FULLNAME you can have many NEWMDY. And for each NEWMDY you can have many ACCTNUMBER. What do you want to count? The number of lines per FULLNAME? The number of distinct ACCTNUMBERs per FULLNAME?

If you want to count the number of lines per FULLNAME, here is a possibility (example with the CAR file):
  
-* File Joe.fex
TABLE FILE CAR
PRINT SALES
COMPUTE CC/I3=1; NOPRINT
BY COUNTRY
BY BODYTYPE
BY MODEL
ON COUNTRY RECAP 
Count/I3=CC;
ON COUNTRY SUBTOTAL 
END


However if you want to count the number of distinct ACCTNUMBERs per FULLNAME:
  
TABLE FILE CAR
SUM CNT.CAR NOPRINT
BY COUNTRY
PRINT SALES
BY COUNTRY
BY BODYTYPE
BY CAR
ON COUNTRY RECAP 
Count/I3=CNT.CAR;
ON COUNTRY SUBTOTAL
ON TABLE NOTOTAL 
END


This works well because the CAR file is a hierarchy. If your file is not, some extra coding might be necessary.

Good luck!

This message has been edited. Last edited by: Danny-SRL,


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
Expert
posted Hide Post
What Danny states is possible, so use a multi verb request such as this -
APP PREPENDPATH IBISAMP IBINCCEN
TABLE FILE CAR
WRITE CNT.MODEL
   BY COUNTRY
WRITE CNT.MODEL
   BY COUNTRY
   BY CAR
  SUM CNT.MODEL
      RCOST
      DCOST
   BY COUNTRY
   BY CAR
   BY MODEL
ON TABLE SUMMARIZE
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF, SIZE=9, $
ENDSTYLE
END
-RUN

Notice that, in this example, providing you apply the count to the correct fieldname it doesn't matter about the position. Just make sure that you know and understand your data first.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders