![]() |
||||||||||||
Go ![]() | New ![]() | Search ![]() | Notify ![]() | Tools ![]() | Reply ![]() | ![]() |
Member |
Hi All, 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, WebFOCUS 8 Windows, All Outputs | ||
|
Virtuoso |
Please consider showing what the raw data and the final output should look like. It's hard to envision what you're trying to accomplish. WebFOCUS 8206, Unix, Windows | |||
|
Virtuoso |
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007 | |||
|
Master |
Take a look at the WHEN phrase. The WHEN phrase allows you to conditionally display subtotals. | |||
|
Member |
Hi David, 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, WebFOCUS 8 Windows, All Outputs | |||
|
Virtuoso |
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007 | |||
|
Powered by Social Strata |
![]() | Please Wait. Your request is being processed... |
|