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     [CLOSED]Need help on how to calculate sub-total/total for multiple inputs

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED]Need help on how to calculate sub-total/total for multiple inputs
 Login/Join
 
Member
posted
Hi all,

There is a report which picks up a single Customer ID and TopXX number, and then generate the report with TopXX lines detail rows, then sub-total line for all TopXX lines, and at last the Total line for all lines in DB.

For example, if customer ID is set as '33' and topXX as '5', also in the DB there are 10 rows for the customer ID, then the report output is:

Rank Field1
1 1
2 2
3 3
4 4
5 5
Sub-Total 15 <-- its total value of topXX lines shown.
Total (1+..+10)=55 <-- its total value of all lines in DB for the customer.

For a single Customer ID, I have implement the whole report by using multiple HOLD files, calculating sub-total and total value and displaying in report.

But my boss told me to enable multiple customer IDs as input, and each customer has its own table with Sub-Total and Total. Below is the example:

Page 1 (for customer 1)
Rank Field1
1 1
2 2
3 3
4 4
5 5
Sub-Total 15
Total (1+..+10)=55

Page 2 (for customer 2)
Rank Field1
1 2
2 3
3 4
4 5
5 6
Sub-Total 20
Total (2+..+11)=65

I thought of using loop and compound function, but it might cause performance issue because each sub-report for one customer ID would query DB.

So here is the thing: is there method to calculate the sub-total and total at less performance cost for all customers?

This message has been edited. Last edited by: <Emily McAllister>,


WebFOCUS 8105
Windows, All Outputs
 
Posts: 26 | Location: Hefei,China | Registered: March 27, 2016Report This Post
Virtuoso
posted Hide Post
As I understand you will have to perform DB reading one time : to extract detail customer data as per customer selection. You don't need to read DB multiple times.

Then using that hold file, perform your detail data, sub-total and customer total and have the first BY on the customer ID for those three other HOLD files.

You will then result in the same three hold files (detail, sub-total and total) with a BY CUST_ID as the first field.

Merge them the same way as you do.

Finally, produce your report and add:
ON CUST_ID PAGE-BREAK

Sample :
TABLE FILE CAR
SUM SEATS
BY COUNTRY
BY CAR
ON COUNTRY PAGE-BREAK
ON TABLE PCHOLD FORMAT PDF
END


It may do the trick or be close to it.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
Maybe I misunderstand the question, but wouldn't a multi-verb request do what you want?

Something like:

DEFINE FILE XYZ
FIELD2/D12.2=FIELD1;
END

TABLE FILE XYZ
SUM FIELD2
BY CUSTID
SUM FIELD1
BY HIGHEST 5 TOPXX
END


You can use NOPRINT and then a SUBFOOT to display the totals for FIELD1 and FIELD2.

I confess I haven't thought this through fully and you might well have to create a hold file. Another thought is to run it twice and create one hold with the totals and another with the ranked TopXX for all the customers and then join them back together. Then, as Martin suggested, either burst by CUSTID (if you are using ReportCaster) or ON CUSTID PAGEBREAK


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Virtuoso
posted Hide Post
This will work:
-SET &RANK = 8;

TABLE FILE USERDATA
SUM   NUMBER NOPRINT
BY    USERID NOPRINT
PRINT COMPUTE RANK/I3   = IF USERID NE LAST USERID THEN 1 ELSE RANK+1;
      NUMBER
      COMPUTE TOTNUM/I3 = IF USERID NE LAST USERID THEN NUMBER ELSE IF RANK LE &RANK THEN TOTNUM+NUMBER ELSE TOTNUM; NOPRINT
BY    USERID SUBFOOT
"TOTAL USERID <USERID RANKED <TOTNUM"
"TOTAL USERID <USERID DATABASE <NUMBER"
BY    HIGHEST NUMBER NOPRINT
WHERE TOTAL RANK LE &RANK;
END


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
S.G.
Try this:
  
DEFINE FILE CAR
SALES=IF SALES GT 0 THEN SALES ELSE 10000*RDUNIF('D4.3');
SFOOT/A26='Grand Total for ' | COUNTRY;
END
-*
TABLE FILE CAR
SUM COMPUTE CSALES/I6=SALES; NOPRINT
BY COUNTRY
PRINT SALES
COMPUTE L/I2=IF COUNTRY NE LAST COUNTRY THEN 1 ELSE LAST L+1; NOPRINT
BY COUNTRY
BY TOTAL LOWEST 3 L NOPRINT
ON COUNTRY SUBTOTAL 
ON COUNTRY SUBFOOT
"<SFOOT<CSALES"
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
 INCLUDE=ENDEFLT,
$
TYPE=SUBFOOT, HEADALIGN=BODY,
$
TYPE=SUBFOOT, OBJECT=FIELD, 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
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Need help on how to calculate sub-total/total for multiple inputs

Copyright © 1996-2020 Information Builders