Is it possible to have subtotals for a and b only. Example:

Product,Revenue,Units
xxxx nnnn nnn
vvvv nnnn nnn
cccc nnnn nnn

Subtotals:
Flag A nnnn nnn
Flag B nnnn nnn

Grand totals nnn nnn

Flag is a field not displayed on the main output but only in the subtoal. Thanks for any assistance!

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

if you try to explain your question based on the CAR database then it might help

in the men time you should take a look at the mcguyver solution, that might help you

Joe,

If the number of subtotals change at every sort break and you don't know ahead of run-time how many there are, check out my article MacGyver Technique for Dynamic Number of Grouped Subtotals/Subfoots.

This techniques works in any version, but works best in version 7.6.11 or higher because of the new set command, DROPBLNKLINES. It removes the blank line WebFocus adds right above the subtotal.

Hi,

Below you'll find another technique of how these kind of reports can be made.
With this, one can create very flexible reports.
(in this example, things could ofcourse also be accomplished with DEFINE's, but
just for this example I used TABLE-requests).

-Fred-

-* ------------------------------------------------------------------
-* Go to database only once to get records
-* ------------------------------------------------------------------
TABLE FILE CAR
  SUM
    DEALER_COST
  BY COUNTRY
  BY CAR
  BY BODYTYPE
  BY SEATS
  ON TABLE HOLD AS HLDSOURCE FORMAT ALPHA
END
-RUN

-* ------------------------------------------------------------------
-* Append records for the following TABLE-requests to same HOLD-file
-* Make sure all TABLE-requests produce the same kind of HOLD file,
-* ------------------------------------------------------------------
FILEDEF hldtotal DISK hldtotal.ftm (APPEND
-RUN

-* ------------------------------------------------------------------
-* Get all records
-* ------------------------------------------------------------------
TABLE FILE HLDSOURCE
  SUM
    COMPUTE SRT/I1 = 1;
    DEALER_COST
  BY COUNTRY
  BY CAR
  BY BODYTYPE
  BY SEATS
  ON TABLE HOLD AS hldtotal FORMAT ALPHA
END
-RUN

-* ------------------------------------------------------------------
-* Add subtotal for only Italy and Japan
-* ------------------------------------------------------------------
TABLE FILE HLDSOURCE
  SUM
    COMPUTE FLD1/A10 = 'Total';
    COMPUTE FLD2/A16 = 'Japan / Italy';
    COMPUTE FLD3/A12 = '';
    COMPUTE FLD4/I3S = 0;
    COMPUTE SRT/I1   = 2;
    DEALER_COST
  WHERE COUNTRY IN('JAPAN','ITALY');
  ON TABLE SAVE AS hldtotal FORMAT ALPHA
END
-RUN

-* ------------------------------------------------------------------
-* Add subtotal for only 4-SEATS cars
-* ------------------------------------------------------------------
TABLE FILE HLDSOURCE
  SUM
    COMPUTE FLD1/A10 = 'Total';
    COMPUTE FLD2/A16 = 'all 4 seats';
    COMPUTE FLD3/A12 = '';
    COMPUTE FLD4/I3S = 0;
    COMPUTE SRT/I1   = 3;
    DEALER_COST
  WHERE SEATS EQ 4;
  ON TABLE SAVE AS hldtotal FORMAT ALPHA
END
-RUN

-* ------------------------------------------------------------------
-* Add subtotal for only SEDAN's
-* ------------------------------------------------------------------
TABLE FILE HLDSOURCE
  SUM
    COMPUTE FLD1/A10 = 'Total';
    COMPUTE FLD2/A16 = 'all sedans';
    COMPUTE FLD3/A12 = '';
    COMPUTE FLD4/I3S = 0;
    COMPUTE SRT/I1   = 4;
    DEALER_COST
  WHERE BODYTYPE EQ 'SEDAN';
  ON TABLE SAVE AS hldtotal FORMAT ALPHA
END
-RUN

-* ------------------------------------------------------------------
-* The end result
-* ------------------------------------------------------------------
TABLE FILE HLDTOTAL
  SUM
    DEALER_COST
  BY SRT NOPRINT
  BY COUNTRY
  ON COUNTRY SUBTOTAL AS 'Total'
  WHEN SRT EQ 1
  ON COUNTRY SUBFOOT
  " "
  WHEN SRT EQ 1
  BY CAR
  BY BODYTYPE
  ON TABLE SET HTMLCSS ON
  ON TABLE NOTOTAL
  ON TABLE SET STYLE *
  TYPE=REPORT, BORDER=1, BORDER-COLOR=RGB(230 230 230), FONT=VERDANA , SIZE=10,$
  TYPE=SUBTOTAL, BACKCOLOR=RGB(245 245 245) ,$
  TYPE=DATA, BACKCOLOR=RGB(225 225 225), WHEN=SRT EQ 2 ,$
  TYPE=DATA, BACKCOLOR=RGB(200 200 200), WHEN=SRT EQ 3 ,$
  TYPE=DATA, BACKCOLOR=RGB(175 175 175), WHEN=SRT EQ 4 ,$
  ENDSTYLE
END

This message has been edited. Last edited by: <FreSte>,

Hi Fred, I agree your approach will work when the number of subtotals are known and you only have one layer of subtotals/totals you want displayed at the end of the report. A complex series of DEFINES could also work in place of separate TABLES. And you could also create REPEAT/LOOPS for appending data which is what I used in the past successfully and again in my early attempts for my current report. But once I saw the MacGyver Webinar, it became clear to me that the number of subtotals or number of differents views on the data could be dynamically handled through only one pass of the data. Which is really important if you are working with large tables.

David

Fred's example worked well. Thanks! The only problem I see with it is when there is a drill down field. It appears that the subtotals inheret the same formats and as what the main body of data has which I assumed would happen. I guess there's not a way to format around this? Thanks!

Joe,

I think there is; just add a condition in the stylesheet for the drill-down column like:

...
TYPE=DATA, COLUMN=CAR, FOCEXEC=WHATEVER(), WHEN=SRT EQ 1,$
....


-Fred-

Thanks! I thin that will work.