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.
In my report, Table select, we do have an 3 levels of records. I want to do grand total with 2nd level only. But, I wanna to display all level records in my report. At the end to we need to display grandtotal for 2nd level. is it possible?.
please suggest and share samples codes.This message has been edited. Last edited by: FP Mod Chuck,
Here is a manual way to do it. You may not need files H2 and H3 since your data already has different type of records. The goal here is to create the detailed file (H1+H2+H3) and a total file (H2T) with only total on what you want. In your case you will probably need a WHERE clause on record level 2 to create the total file. Finally you will merge them together. The result may look stupid, but just to illustrate the technic. The important thing is that you need same field's name/format and number to be able to merge (MORE) files together.
TABLE FILE CAR
SUM SALES/D8.2 AS 'SLS'
BY TOTAL COMPUTE TOTID /I1 = 0;
BY TOTAL COMPUTE ROWID /I1 = 1;
BY TOTAL COMPUTE ROWTXT/A30V = 'Sales';
BY COUNTRY
ON TABLE HOLD AS H1
END
-RUN
TABLE FILE CAR
SUM SALES NOPRINT
COMPUTE SLS/D8.2 = C1 / 365;
BY TOTAL COMPUTE TOTID /I1 = 0;
BY TOTAL COMPUTE ROWID /I1 = 2;
BY TOTAL COMPUTE ROWTXT/A30V = 'Avg Daily Sales';
BY COUNTRY
ON TABLE HOLD AS H2
END
-RUN
TABLE FILE CAR
SUM SALES NOPRINT
COMPUTE SLS/D8.2 = C1 / 365;
BY TOTAL COMPUTE TOTID /I1 = 1;
BY TOTAL COMPUTE ROWID /I1 = 2;
BY TOTAL COMPUTE ROWTXT/A30V = 'Avg Daily Sales';
BY TOTAL COMPUTE COUNTRY/A10 = 'Total';
ON TABLE HOLD AS H2T
END
-RUN
TABLE FILE CAR
SUM SALES NOPRINT
COMPUTE SLS/D8.2 = C1 / 12;
BY TOTAL COMPUTE TOTID /I1 = 0;
BY TOTAL COMPUTE ROWID /I1 = 3;
BY TOTAL COMPUTE ROWTXT/A30V = 'Avg Mth Sales';
BY COUNTRY
ON TABLE HOLD AS H3
END
-RUN
TABLE FILE H1
SUM SLS/D8.2CM AS ''
BY TOTID NOPRINT
BY COUNTRY
BY ROWID NOPRINT
BY ROWTXT AS ''
MORE
FILE H2
MORE
FILE H2T
MORE
FILE H3
END
-RUN
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, 2013
I tried with the below conditional ON phrase for grand total - 2nd level.
TABLE FILE EMPLOYEE
SUM GROSS DED_AMT AND COMPUTE
DG_RATIO/F4.2=DED_AMT/GROSS;
BY HIGHEST PAY_DATE BY DEPARTMENT
BY BANK_ACCT
WHERE BANK_ACCT NE 0
ON DEPARTMENT SUMMARIZE MULTILINES AS '' WHEN DEPARTMENT EQ 'MIS'
END
The above mentioned code, In subtotal value is getting applied condition as based on department EQ 'MIS' record. But Similar condition, I want to apply for GrandTotal.
Any other way to use WHEN condition for GrandTotal.This message has been edited. Last edited by: Veeramani Karuthapandian,
You cannot apply a condition on a Grand Total since, per definition, a Grand Total is applied on the whole data. What you are requesting is a Sub-Total which is the total of a sub-part of the data. A condition can be applied at this level as already stated.
As far as I can understand your need, you want the 2nd level total placed at the end of the report. And this is why you call this a Grand Total which is not. To perform such a thing, you should use the technic that I've posted already.
Here the whole code for you
TABLE FILE EMPLOYEE
SUM GROSS DED_AMT
COMPUTE DG_RATIO/F4.2 = DED_AMT / GROSS;
BY TOTAL COMPUTE ROWID /I2 = 1;
BY PAY_DATE
BY DEPARTMENT
BY BANK_ACCT
WHERE BANK_ACCT NE 0;
ON TABLE HOLD AS DETDATA
END
-RUN
TABLE FILE EMPLOYEE
SUM GROSS DED_AMT
COMPUTE DG_RATIO/F4.2 = DED_AMT / GROSS;
BY TOTAL COMPUTE ROWID /I2 = 99;
BY TOTAL COMPUTE PAY_DATE /I6YMD = 0;
BY DEPARTMENT
BY TOTAL COMPUTE BANK_ACCT /I9S = 0;
WHERE BANK_ACCT NE 0;
WHERE DEPARTMENT EQ 'MIS';
ON TABLE HOLD AS TOTDATA
END
-RUN
TABLE FILE DETDATA
SUM GROSS DED_AMT
DG_RATIO
BY ROWID
BY PAY_DATE
BY DEPARTMENT
BY BANK_ACCT
ON TABLE HOLD AS MRGDATA
MORE
FILE TOTDATA
END
-RUN
DEFINE FILE MRGDATA
PAY_DATEX /A11 = IF PAY_DATE NE 0 THEN FPRINT(PAY_DATE, 'I6YMD', 'A11') ELSE 'Total';
END
TABLE FILE MRGDATA
SUM GROSS DED_AMT
DG_RATIO
BY ROWID NOPRINT
BY HIGHEST PAY_DATE NOPRINT
BY PAY_DATEX
BY DEPARTMENT
BY BANK_ACCT
END
-RUN
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, 2013