Hello,

I am sure this must be a very common question and I tried finding answers to my question before posting here; but basically I have a subtotal kinda question.

I am trying to do 2 subtotals in my report:
For example:

Cust_Name    Date    Cust_Grp   T_Code   Desc   Batch#  Amount  User_dept
ABCD        06/19     1111       09      XYZZ    1443    50       Sales
BATCH TOTAL                                              50

AKJFKJ      06/19     2222       11      JHNFJD  5678    40       JFHDJF
IERE        06/19     2222       12      JDHFJK  5678    32       RURIW
BATCH TOTAL                                              72

Cust_Grp Totals       1111                               50
                      2222                               72
                     
Total for the file                                       122  

I know how I can the Batch Total Line - it's subtotal on BATCH#
The total for the file line is - On Table Column-total (please correct me if I am wrong here)

But basically I am not sure how do I get the Cust_Grp total line where I can have the various groups lined up with their respective total ammounts ..

Can you all please help me with that?

Thanks in advance!

This message has been edited. Last edited by: FP Mod Chuck,

As far as I can understand, Cust_Grp total is just another sub-total at that level.
You can put as many sub-total as you want until is based on a BY field.

Nova27,

So you have a BY BATCH# command in your TABLE request.
Now you want to have at the end of your report a summary by Cust_Grp, am I right?
If so, turn to MacGuyver!

Example of MacGuver with the CAR file

  
-SET &ECHO=ALL;
-*File nova27_01
-* McGuyver technique: using a flat file
-* Create the MacGuyver Master and data file on the fly
-*
SET MSG=ON
EX -LINES 7 EDAPUT MASTER,FSEQ,C,MEM
FILENAME=FSEQ,SUFFIX=FIX
SEGNAME=CHAR1,SEGTYPE=S0
FIELDNAME=BLANK,BLANK,A1,A1,$
SEGNAME=CHARS,SEGTYPE=S0,PARENT=CHAR1,OCCURS=VARIABLE
FIELDNAME=CHAR,CHARS,A1,A1,$
FIELDNAME=COUNTER,ORDER,I2,I4,$
-RUN
FILEDEF FSEQ DISK FSEQ.FTM
-RUN
-WRITE FSEQ  FILEFORMACGUYVERFILEFORMACGUYVERFILEFORMACGUYVER
-RUN
-*
-* Example
JOIN BLANK WITH BODYTYPE IN CAR TO BLANK IN FSEQ AS B_
DEFINE FILE CAR
BLANK/A1 WITH BODYTYPE=' ';
XCOUNTRY/A10=IF COUNTER EQ 2 THEN ' ' ELSE COUNTRY;
XHEAD/A16=IF COUNTER EQ 2 THEN 'Bodytype Totals' ELSE 'Country Totals';
END
TABLE FILE CAR

SUM SALES XHEAD NOPRINT
BY COUNTER NOPRINT PAGE-BREAK REPAGE
BY XCOUNTRY AS COUNTRY
BY BODYTYPE
ON XCOUNTRY SUBFOOT
" "
WHEN COUNTER EQ 1
ON XCOUNTRY SUBTOTAL MULTILINES AS ' '
WHERE COUNTER LE 2
ON TABLE NOTOTAL
HEADING
"<XHEAD"
END
-RUN

This message has been edited. Last edited by: Danny-SRL,

Hi,

Thanks for all your help - so I can get the 2 subtotal lines like I want, but the Cust_Grp column is showing up as a sum(total) in the Subtotal line which I would like to hide/appear invisible.

For example:

Cust_Name    Date    Cust_Grp   T_Code   Desc   Batch#  Amount  User_dept
ABCD        06/19     1111       09      XYZZ    1443    50       Sales
BATCH TOTAL                                              50

AKJFKJ      06/19     2222       11      JHNFJD  5678    40       JFHDJF
IERE        06/19     2222       12      JDHFJK  5678    32       RURIW
BATCH TOTAL                                              72

Cust_Grp Totals       1111                               50
                      2222                               72
                     
Total for the file                                       122  

Here, in the Cust_Grp totals line apart from seeing a "total" of the Amount column, I also see a total of the Cust_Grp column which I don't want to see.. The only total I would like to see is for the Amount column..
I think the Cust_Grp column is numeric and maybe that's why it is automatically being summed up - is there a way I can hide or omit the sum for Cust_Grp from the subtotal line?

Thanks!

Nova, this is not a Total of Cust_Grp, it looks more as the libelling for the total.
If it would be a total, it would have been displayed 4444 and not 2222 on the second row.

I am sorry I didn't update the example correctly... but basically that is what it does, prints 4 in the subtotal line if I have 4 lines of Cust_Grp = 1

Hope that helps!

Any idea how I can get rid of the sum of Cust_Grp from the subtotal line and only keep sum of Amount visible?

Thanks!

List only the fields to include in the total.
Such as

ON COUNTRY SUBTOTAL RETAIL_COST AS 'Sub-Total'

Hi everyone,
I have some more questions about this same topic - My subtotals are coming up correctly as expected, thank you all for your help!

Now I have additional subtotals to be displayed based on a Accept/Reject flag I defined for this report.

So for the example below, I need to show Accepted & Rejected on the Subtotal line for each Cust_Grp:

Cust_Name    Date    Cust_Grp   T_Code   Desc   Batch#  Amount  User_dept
ABCD        06/19     1111       09      XYZZ    1443    50       Sales

BATCH TOTAL                                              50


AKJFKJ      06/19     2222       11      JHNFJD  5678    40       JFHDJF
IERE        06/19     2222       12      JDHFJK  5678    32       RURIW

BATCH TOTAL                                              72

CUST_GRP TOTAL      
Accepted              1111                               00
                      2222                               72

Rejected              1111                               50
                      2222                               00

                     
Total for the file                                       122

I somehow can't get the Accepted/Rejected totals showing up under CUST_GRP total (like explained in example above).

I defined the flags in the DEFINE FILE statement in my code like this:

DEFINE FILE TEST
ACCPT_FLG/I2 = IF REJECTED = 'Y' AND SYSTEM_STATUS = 'PROCESSED' THEN 1 ELSE 0;
REJ_FLG/I2 = IF REJECTED = ' ' AND SYSTEM_STATUS = 'PROCESSED' THEN 1 ELSE 0;
END

TABLE FILE TEST
PRINT
Cust_name
Date
Cust_Grp
T_Code
Desc
Batch#
Amount
User_Dept
BY Cust_Grp NOPRINT
BY ACCPT_FLG NOPRINT
BY REJ_FLG NOPRINT
BY BATCH# NOPRINT
HEADING
"Test Report"
ON Cust_Grp SUBTOTAL Amount AS 'Cust Grp Total '
ON ACCPT_FLG SUBTOTAL Amount AS 'Accepted Total '
ON REJ_FLG SUBTOTAL Amount AS 'Rejected Total '
ON BATCH# SUBTOTAL Amount AS 'Batch Total '
ON TABLE COLUMN-TOTAL AS 'Total for File'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE PCHOLD FORMAT EXL07
END

Do I need to change the order in which I am sorting my result set (BY statements) or the ON SUBTOTAL statements?
I am not sure how to get the Accept Reject totals for each Cust_Grp like shown above.

Thanks for all the help!

Small correction to my DEFINE statement:

DEFINE FILE TEST
ACCPT_FLG/I2 = IF REJECTED EQ '' AND SYSTEM_STATUS EQ 'PROCESSED' THEN 1 ELSE 0;
REJ_FLG/I2 = IF REJECTED EQ 'Y' AND SYSTEM_STATUS EQ 'PROCESSED' THEN 1 ELSE 0;
END

ALso, how do I get Amount to display as 0 when the flag is zero for both Accepts & Rejects?

Anyone can help me out here please?

Hi Nova27

I am thinking a COMPUTE of the AMOUNT with an IF condition on both ACCPT_FLG and REJ_FLG equal to 0 else the AMOUNT field. Untested but an idea..

Nova,

I don't understand how you can get your output with the code you show.
Your output shows 2 passes on the data.
So either you create a HOLD file with duplicate data and different sort values or use the MacGuyver technique to create duplicate data on the fly.