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:
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)
Page 2 (for customer 2)
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>,
Windows, All Outputs
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
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
Maybe I misunderstand the question, but wouldn't a multi-verb request do what you want?
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
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
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
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
|Powered by Social Strata|