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,

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.

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

quote:

...I want to do grand total with 2nd level only...

Take a look at the WHEN phrase.

The WHEN phrase allows you to conditionally display subtotals.

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,

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