I'm looking to create a sort of summary grand-total on a BY field at the end of my report just before the grandtotal, but I can't seem to figure out how. An example explains best what I want I think.

In below procedure, I want to add a subtotal for Coffee, Food and for Gifts before the Grandtotal - sort of a Grand-subtotal:

TABLE FILE GGSALES
SUM 
     GGSALES.SALES01.UNITS
     GGSALES.SALES01.DOLLARS
     GGSALES.SALES01.BUDUNITS
     GGSALES.SALES01.BUDDOLLARS
BY  LOWEST GGSALES.SALES01.CATEGORY
BY  LOWEST GGSALES.SALES01.CITY
BY  LOWEST GGSALES.SALES01.PRODUCT
     
ON GGSALES.SALES01.CITY SUBTOTAL AS '*TOTAL'
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
END

Meaning, just before the line with the Grand total I need lines:

TOTAL Coffee ... ... ...
TOTAL Food ... ... ...
TOTAL Gifts ... ... ...

Sounds easy, right?

This message has been edited. Last edited by: Wep5622,

read up on RECAP

I don't quite see how RECAP would help here? That information seems to go in between the sort groups (in this case on category) regardless.

I need them at the end, one row for each category - with a variable number of categories in my actual case.

How about a dummy subfoot and your create the items for the subfoot before the table request.

  
-SET &ECHO = 'ALL';
SET ASNAMES = ON
SET HOLDLIST = PRINTONLY

DEFINE FILE GGSALES
QUOTE/A1 = '"';
TOTL/A7  = '*Total ';
SPT_MRK/A4 = '<+0>';
DUMMY/A1 = ' ';
END

TABLE FILE GGSALES
SUM 
     QUOTE
     TOTL
     CATEGORY
     SPT_MRK
     UNITS
     SPT_MRK
     DOLLARS
     SPT_MRK
     BUDUNITS
     SPT_MRK
     BUDDOLLARS
     QUOTE

BY CATEGORY NOPRINT
ON TABLE HOLD AS HCATLIST FORMAT ALPHA
END
-RUN

TABLE FILE GGSALES
SUM 
     UNITS
     DOLLARS
     BUDUNITS
     BUDDOLLARS

BY  DUMMY NOPRINT
BY  LOWEST CATEGORY
BY  LOWEST CITY
BY  LOWEST PRODUCT
     
ON CITY SUBTOTAL AS '*TOTAL'
ON DUMMY SUBFOOT
-INCLUDE hcatlist.ftm
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=SUBFOOT,HEADALIGN=BODY,JUSTIFY=RIGHT,$
TYPE=SUBFOOT,ITEM=1,COLSPAN=3,JUSTIFY=LEFT,$
ENDSTYLE
END

Okay, so that's not really possible then, unless you jump through a lot of hoops to get an average result...

I ended up including a new TABLE request with just the category (sub-)totals and a grand total. It adds a new page to the report, but that's better than the alternatives.

Wep,

This is a job for... Mcguyver!

  
-* File wep06.fex

JOIN BLANK WITH CATEGORY IN GGSALES TO BLANK IN FSEQ AS B_
-*
DEFINE FILE GGSALES
BLANK/A1 WITH CATEGORY=' ';
XCATEGORY/A20=IF COUNTER EQ 2 THEN 'Total ' | CATEGORY ELSE CATEGORY;
XCITY/A20=IF COUNTER EQ 2 THEN ' ' ELSE CITY;
XPRODUCT/A20=IF COUNTER EQ 2 THEN ' ' ELSE PRODUCT;
CATEGORYSORT/A1=IF COUNTER EQ 2 THEN ' ' ELSE '1';
END


TABLE FILE GGSALES
SUM 
     UNITS
     DOLLARS
     BUDUNITS
     BUDDOLLARS
BY HIGHEST CATEGORYSORT NOPRINT
BY XCATEGORY AS CATEGORY
BY XCITY AS CITY
BY XPRODUCT AS PRODUCT
WHERE REGION EQ 'Midwest';
WHERE COUNTER LE 2;
ON XCITY SUBTOTAL AS '*TOTAL' MULTILINE
ON CATEGORYSORT SUBTOTAL AS 'Grand Total'
   WHEN CATEGORYSORT EQ ' ';
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET LINES 999999
END

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

I'm happy with the results I have, I feel no need to apply McGuyver to that report.

I've been wondering about why we need the McGuyver technique at all?
It's a rather complicated way of doing something fairly basic, isn't it? What it does, shouldn't that ultimately be possible with native FOCUS commands? Why don't we have those?

Do we really have to play cool attitudes when talking about "the McGuyver technique" so that we can show off to each other how much of an expert we are? (I'm just as guilty of that, I have no intention to deal out blame here). I think we're a bit too focused on the beauty of the complexity of the solution instead of the beauty of the solution itself - on close inspection, it's really more of a horror IMHO.

Wep,

What you are doing is making 2 passes on the Data.
However, what McGuyver proposes is making only 1 pass on the data, doubling the records and sort/sum-ing them accordingly.
It is an elegant solution and it would be intersting to examine which of the 2 solutions is more economical.

MightyMax, your code works great when you want a single set of sub-totals at the end of the report right before the Grand-Total. Doesn't work well when you need multiple sets of sub-totals for major sort groups. I found this out when I received webfocus agent crashes doing too many INCLUDES on the subfoot. Talk about complicated code...and it was slow to run.

Danny, am I wrong in thinking you're missing some code up top? This doesn't work out of the box for me on 7.6.11. I can make it work by adding standard McGuyver code but maybe there is something new in 8.0/7.7 having to do with fseq?

The "McGuyver Technique" is not some esoteric classroom coding trick. It's needed because it solves several complex coding problems that WebFocus cannot perform easily using standard code. I wish I had learned it back in the Eighties when it was first published in the Focus Sysytems Journals as I would have employed it on many of the coding challenges I faced through the years. I used it again last week becuase it solved another business reporting requirement, that would have taken more code and been less efficient if done another way.

And to all the coders who like to show off (and those who don't), keep it coming! I appreciate your efforts. When talking to potential WebFocus customers, one of the first things I talk positively about (of many) is this Forum, BECAUSE of all of the friendly and knowledgeable help that is offered.

Dave,
I see what you mean.
My FSEQ master has a DATASET attribute which points to the FSEQ data.
Master:

  
FILE=FSEQ, SUFFIX=FIX, DATASET=FOCALPOINT/FSEQ.DAT, $
  SEGNAME=SEG1
   FIELD=CONTROL, BLANK , A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=WHATEVER, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$

Data

  
 SOMECHARACTERSFORTHEFSEQFILENOTICETHEFIRSTCHARACTERISASPACE