Focal Point Banner


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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Totals and Subtotals for Excel report

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Totals and Subtotals for Excel report
 Login/Join
 
Platinum Member
posted
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,


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Virtuoso
posted Hide Post
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.


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: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
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!


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
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,


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Platinum Member
posted Hide Post
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!


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Virtuoso
posted Hide Post
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.


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: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
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!


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Platinum Member
posted Hide Post
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!


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Virtuoso
posted Hide Post
List only the fields to include in the total.
Such as
ON COUNTRY SUBTOTAL RETAIL_COST AS 'Sub-Total'


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: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
Using MacGuyver


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Platinum Member
posted Hide Post
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!


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Platinum Member
posted Hide Post
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


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Platinum Member
posted Hide Post
ALso, how do I get Amount to display as 0 when the flag is zero for both Accepts & Rejects?


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Platinum Member
posted Hide Post
Anyone can help me out here please?


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Virtuoso
posted Hide Post
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..


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2128 | Location: Customer Support | Registered: April 12, 2005Report This Post
Virtuoso
posted Hide Post
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.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Totals and Subtotals for Excel report

Copyright © 1996-2020 Information Builders