Can we please get this report to include a subtotal by Activity at the bottom of the report? (Output options: html, pdf, and excel)

COURSE SESSION# Activity Activity-Total
------- -------- -------- --------------
Course1 1 Lab 48.00
Course1 2 Lab 48.00
Course2 1 Lab 12.00
Course2 2 Lab 24.00
Course3-1 1 Lecture 2.00
Course3-2 1 Lab 12.00
Course3-2 2 Lab 12.00
Course3-2 3 Lab 12.00
Course3-2 4 Lab 12.00
Course3-2 5 PE 12.00
Course4 1 Lecture 4.00
Course4 2 Lab 10.0
---------------------------------------------
Activity total 208.00

Lab 190.00
Lecture 6.00
PE 12.00
--------------------------
TOTAL 208.00

Thanks for your help!

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

Looks like you need a Compound report. Do a search for some more examples.

  
-DEFAULT &WFFMT = 'HTML'
TABLE FILE CAR
SUM
     DEALER_COST
	 RETAIL_COST
BY COUNTRY
BY CAR
ON TABLE SUBTOTAL AS 'Total'
ON TABLE SET COMPOUND OPEN
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE PCHOLD FORMAT &WFFMT
END

TABLE FILE CAR
SUM
     DEALER_COST
	 RETAIL_COST
BY COUNTRY
ON TABLE SUBTOTAL AS 'Total'
ON TABLE SET COMPOUND CLOSE
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE PCHOLD FORMAT &WFFMT
END

Perhaps something like this will work:

DEFINE FILE CAR
SALES_CV/D10 = IF BODYTYPE EQ 'CONVERTIBLE' THEN SALES ELSE 0;
SALES_HT/D10 = IF BODYTYPE EQ 'HARDTOP' THEN SALES ELSE 0;
SALES_SE/D10 = IF BODYTYPE EQ 'SEDAN' THEN SALES ELSE 0;
SALES_CO/D10 = IF BODYTYPE EQ 'COUPE' THEN SALES ELSE 0;
SALES_RO/D10 = IF BODYTYPE EQ 'ROADSTER' THEN SALES ELSE 0;
END

TABLE FILE CAR
SUM
SALES
BY COUNTRY
BY BODYTYPE

ON TABLE SUBFOOT
"TOTALS"
"CONVERTIBLE: <TOT.SALES_CV"
"HARDTOP: <TOT.SALES_HT"
"SEDAN: <TOT.SALES_SE"
"COPUE: <TOT.SALES_CO"
"ROADSTER: <TOT.SALES_RO"

ON TABLE SET STYLE *
TYPE=TABFOOTING, HEADALIGN=BODY, $
TYPE=TABFOOTING, ITEM=2, JUSTIFY=RIGHT, $
ENDSTYLE
END

Mighty and Francis, thanks your response.
-I have used the Compound concept to include a subtotal by Activity at the bottom of the report successfully.
-I have tried Francis’s code, which also is a solution if all Activities data are known.

Our application uses API to call WF reports so it causes the followings:
1) The subtotal at the bottom of the compound report can be shown on the PDF and EXL2K successfully.
2) The subtotal will be cut off at the bottom of the compound report on the HTML output (However, it will be shown successfully if runs it in WF Developer Studio).
3) The subtotal will cause the entire report be blank on AHTML output (it will show a blank report in WF Developer Studio too)

Do you have any clues for 2) and 3)?

Thanks for you help!

To solve this, you might also want to try the McGyver Technique. See this article I wrote called McGyver Dynamic Subtotals/Subfoots.

This works in EXL2K, HTML, AHTML, DHTML, & PDF.

It's 'Dynamic" because you can have any number of summed rows in the subtotal section. You don't need to know before hand.

The other nice thing is it's only one pass through the data source.

With slight modifications to my original technique, here is an example using the data from your first post:

-*-----------------------------------------------------------------------------------
-* Build test MFD
-*-----------------------------------------------------------------------------------
FILEDEF COURSEMAS DISK COURSE.MAS
-RUN
-WRITE COURSEMAS FILENAME=course, SUFFIX=FIX    , $
-WRITE COURSEMAS SEGMENT=ONE, SEGTYPE=S0, $
-WRITE COURSEMAS FIELDNAME=COURSENAME,     ALIAS=E1, USAGE=A9,   ACTUAL=A9,$
-WRITE COURSEMAS FIELDNAME=SESSION,        ALIAS=E2, USAGE=A1,   ACTUAL=A1,$
-WRITE COURSEMAS FIELDNAME=ACTIVITY,       ALIAS=E3, USAGE=A7,   ACTUAL=A7,$
-WRITE COURSEMAS FIELDNAME=ACTIVITY_TOTAL, ALIAS=E4, USAGE=D7.2, ACTUAL=A2,$

-*-----------------------------------------------------------------------------------
-* Build test data file
-*-----------------------------------------------------------------------------------
FILEDEF COURSEDAT DISK COURSE.FTM
-RUN
-WRITE COURSEDAT Course1  1Lab    48
-WRITE COURSEDAT Course1  2Lab    48
-WRITE COURSEDAT Course2  1Lab    12
-WRITE COURSEDAT Course2  2Lab    24
-WRITE COURSEDAT Course3-11Lecture2
-WRITE COURSEDAT Course3-21Lab    12
-WRITE COURSEDAT Course3-22Lab    12
-WRITE COURSEDAT Course3-23Lab    12
-WRITE COURSEDAT Course3-24Lab    12
-WRITE COURSEDAT Course3-25PE     12
-WRITE COURSEDAT Course4  1Lecture4
-WRITE COURSEDAT Course4  2Lab    10

-*-----------------------------------------------------------------------------------
-* Set up MacGyver master and data file. 
-*-----------------------------------------------------------------------------------

-*-----------------------------------------------------------------------------------
-* Build MacGyver MFD
-*-----------------------------------------------------------------------------------
FILEDEF MCMAS DISK MCGYV.MAS
-RUN
-WRITE MCMAS FILENAME=mcgyv, SUFFIX=FIX     , $
-WRITE MCMAS   SEGMENT=ONE, SEGTYPE=S0, $
-WRITE MCMAS     FIELDNAME=CONTROL, USAGE=A1, ACTUAL=A1, $
-WRITE MCMAS   SEGMENT=TWO, SEGTYPE=S0, PARENT=ONE, OCCURS=VARIABLE, $
-WRITE MCMAS     FIELDNAME=CHAR1, USAGE=A1, ACTUAL=A1, $
-WRITE MCMAS     FIELDNAME=CTR, ALIAS=ORDER, USAGE=I4, ACTUAL=I4, $
-WRITE MCMAS     DEFINE REPORT_NUMBER/I4  WITH CHAR1=CTR; $

-*-----------------------------------------------------------------------------------
-* Build MacGyver CONTROL file. Set for count of 2 
-*-----------------------------------------------------------------------------------
FILEDEF MCGYV DISK MCGYV.FTM
-RUN
-WRITE MCGYV XAB

-*-----------------------------------------------------------------------------------
-* Join test data file to MacGyver control file
-*-----------------------------------------------------------------------------------
FILEDEF COURSE DISK COURSE.FTM
JOIN CONTROL WITH SESSION IN COURSE TO UNIQUE CONTROL IN MCGYV AS J2

-*-----------------------------------------------------------------------------------
-* Create sort and display fields based on CTR control.
-* If CTR eq 1 display detail, if CTR eq 2 sum by activity.
-*-----------------------------------------------------------------------------------
DEFINE FILE COURSE
CONTROL   /A1 WITH SESSION='X';
COURSENAMEX/A10=IF CTR EQ 1 THEN COURSENAME ELSE
                IF CTR EQ 2 THEN ACTIVITY   ELSE ' ';
SESSIONX/A1    =IF CTR EQ 1 THEN SESSION    ELSE ' ';
ACTIVITYX/A10  =IF CTR EQ 1 THEN ACTIVITY   ELSE ' ';                                         
END

-*-----------------------------------------------------------------------------------
-* Create the report
-*-----------------------------------------------------------------------------------
SET DROPBLNKLINE=ON
SET BYDISPLAY=ON
TABLE FILE COURSE
HEADING
"McGyver Technique with Dynamic SubFoots"
SUM ACTIVITY_TOTAL AS 'TOTAL'
BY CTR NOPRINT 
ON CTR SUBFOOT 
"Activity Total <ST.ACTIVITY_TOTAL </1" 
WHEN CTR EQ 1
ON CTR SUBFOOT
"Total <ST.ACTIVITY_TOTAL" 
WHEN CTR EQ 2
BY COURSENAMEX    AS COURSE,NAME
BY SESSIONX       AS SESSION
BY ACTIVITYX      AS ACTIVITY
ON TABLE NOTOTAL
ON TABLE SET PAGE NOLEAD
ON TABLE PCHOLD FORMAT DHTML
ON TABLE SET STYLE *
GRID=OFF,SQUEEZE=ON,SIZE=9,FONT='ARIAL',$
TYPE=HEADING,SIZE=11,JUSTIFY=CENTER,$
TYPE=TITLE,JUSTIFY=CENTER,STYLE=BOLD,BORDER=LIGHT,$
TYPE=DATA,COLUMN=P2,JUSTIFY=CENTER,$
TYPE=SUBFOOT,STYLE=BOLD,$
ENDSTYLE
END

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

Thanks DavSmith!
Indeed McGyver Technique works in EXL2K, HTML, AHTML, DHTML, & PDF.

My questions are: can a sql be used in the technique? How to combine the sql of the report with this technique?

Glad the example worked!

The McGyver Technique really only works using a WebFocus McGyver MFD and then WebFocus join syntax.

So, I suggest putting your reporting data into a temp hold table using SQL Passthrough syntax and then applying the Technique.

Thanks for your instruction! I have put data of the sql into a temp hold and then applying the technique successfully.

Now I met a difficulty to deal columns with ‘NUMBER’. Please see below: when the report has the following format, two extra 208.00 at the bottom of the report. How to control the columns with ‘NUMBER’ / remove these extra numbers?



ORG – ORG TOT – COURSE – COURSE TOT - SESSION # - ACTIVITY – ACTIVITY TOT
--------
Site1
--------
Org1------- 96.00 ---- Course1--------------96.00 --------- 1 ------------------ Lab ---------------- 48.00
Org1------------------------------------------------------------ 2 ------------------ Lab ---------------- 48.00
Org2------- 36.00 --- Course2 --------------36.00---------- 1 ------------------ Lab ---------------- 12.00
Org2 ------------------ Course2 ------------------------------ 2 ------------------ Lab ---------------- 24.00
Org3 ------ 76.00 -- Course3-1-------------- 2.00 --------- 1 -------------- Lecture ----------------- 2.00
Org3 ---------------- Course3-2 -------------60.00 --------- 1------------------- Lab ---------------- 12.00
Org3 ---------------- Course3-2 ----------------------------- 2 ------------------- Lab ---------------- 12.00
Org3 ---------------- Course3-2 ----------------------------- 3 ------------------- Lab ---------------- 12.00
Org3 ---------------- Course3-2 ----------------------------- 4 ------------------- Lab ---------------- 12.00
Org3 ---------------- Course3-2 ----------------------------- 5 -------------------- PE ---------------- 12.00
Org3 ------------------ Course4 --------------14.00--------- 1--------------- Lecture ----------------- 4.00
Org3 ------------------ Course4 ------------------------------ 2 ------------------- Lab ----------------- 10.0

Site1 208.00
------------------------------------------------------------------------------------------- Activity Total: 208.00



----------- 208.00 --------------------------- 208.00 ----------------------------- Lab ---------------- 190.00
---------------------------------------------------------------------------------- Lecture ------------------- 6.00
--------------------------------------------------------------------------------------- PE ------------------ 12.00

---------------------------------------------------------------------------------------------------TOTAL 208.00

I came up with a method using the code from my previous post.

To make the numeric values only appear in the detail section, you need to transform the numeric columns into alphanumerics. With that, you only display the alphanumeric value when ctr=1. Show ["blank"] when ctr=2. To make your example work, I also needed to add multi-verb logic.

-*-----------------------------------------------------------------------------------
-* Build test MFD
-*-----------------------------------------------------------------------------------
FILEDEF COURSEMAS DISK COURSE.MAS
-RUN
-WRITE COURSEMAS FILENAME=course, SUFFIX=FIX    , $
-WRITE COURSEMAS SEGMENT=ONE, SEGTYPE=S0, $
-WRITE COURSEMAS FIELDNAME=ORG,            ALIAS=E0, USAGE=A4,   ACTUAL=A4,$
-WRITE COURSEMAS FIELDNAME=COURSENAME,     ALIAS=E1, USAGE=A9,   ACTUAL=A9,$
-WRITE COURSEMAS FIELDNAME=SESSION,        ALIAS=E2, USAGE=A1,   ACTUAL=A1,$
-WRITE COURSEMAS FIELDNAME=ACTIVITY,       ALIAS=E3, USAGE=A7,   ACTUAL=A7,$
-WRITE COURSEMAS FIELDNAME=ACTIVITY_TOTAL, ALIAS=E4, USAGE=D7.2, ACTUAL=A2,$
-WRITE COURSEMAS FIELDNAME=ORG_TOTAL,      ALIAS=E4, USAGE=D7.2, ACTUAL=A2,$
-WRITE COURSEMAS FIELDNAME=COURSE_TOTAL,   ALIAS=E4, USAGE=D7.2, ACTUAL=A2,$
-*-----------------------------------------------------------------------------------
-* Build test data file
-*-----------------------------------------------------------------------------------
FILEDEF COURSEDAT DISK COURSE.FTM
-RUN
-WRITE COURSEDAT Org1Course1  1Lab    484848
-WRITE COURSEDAT Org1Course1  2Lab    484848
-WRITE COURSEDAT Org2Course2  1Lab    121212
-WRITE COURSEDAT Org2Course2  2Lab    242424
-WRITE COURSEDAT Org3Course3-11Lecture2 2 2
-WRITE COURSEDAT Org3Course3-21Lab    121212
-WRITE COURSEDAT Org3Course3-22Lab    121212
-WRITE COURSEDAT Org3Course3-23Lab    121212
-WRITE COURSEDAT Org3Course3-24Lab    121212
-WRITE COURSEDAT Org3Course3-25PE     121212
-WRITE COURSEDAT Org3Course4  1Lecture4 4 4
-WRITE COURSEDAT Org3Course4  2Lab    101010

-*-----------------------------------------------------------------------------------
-* Set up MacGyver master and data file. 
-*-----------------------------------------------------------------------------------

-*-----------------------------------------------------------------------------------
-* Build MacGyver MFD
-*-----------------------------------------------------------------------------------
FILEDEF MCMAS DISK MCGYV.MAS
-RUN
-WRITE MCMAS FILENAME=mcgyv, SUFFIX=FIX     , $
-WRITE MCMAS   SEGMENT=ONE, SEGTYPE=S0, $
-WRITE MCMAS     FIELDNAME=CONTROL, USAGE=A1, ACTUAL=A1, $
-WRITE MCMAS   SEGMENT=TWO, SEGTYPE=S0, PARENT=ONE, OCCURS=VARIABLE, $
-WRITE MCMAS     FIELDNAME=CHAR1, USAGE=A1, ACTUAL=A1, $
-WRITE MCMAS     FIELDNAME=CTR, ALIAS=ORDER, USAGE=I4, ACTUAL=I4, $
-WRITE MCMAS     DEFINE REPORT_NUMBER/I4  WITH CHAR1=CTR; $

-*-----------------------------------------------------------------------------------
-* Build MacGyver CONTROL file. Set for count of 2 
-*-----------------------------------------------------------------------------------
FILEDEF MCGYV DISK MCGYV.FTM
-RUN
-WRITE MCGYV XAB

-*-----------------------------------------------------------------------------------
-* Join test data file to MacGyver control file
-*-----------------------------------------------------------------------------------
FILEDEF COURSE DISK COURSE.FTM
JOIN CONTROL WITH SESSION IN COURSE TO UNIQUE CONTROL IN MCGYV AS J2

-*-----------------------------------------------------------------------------------
-* Create sort and display fields based on CTR control.
-* If CTR eq 1 display detail, if CTR eq 2 sum by activity.
-*-----------------------------------------------------------------------------------
DEFINE FILE COURSE
CONTROL   /A1 WITH SESSION='X';
ORGX/A4        =IF CTR EQ 1 THEN ORG        ELSE ' ';
COURSENAMEX/A10=IF CTR EQ 1 THEN COURSENAME ELSE ' ';
SESSIONX/A1    =IF CTR EQ 1 THEN SESSION    ELSE ' ';
ACTIVITYX/A10  =IF CTR EQ 1 THEN ACTIVITY   ELSE ' '; 
END

-*-----------------------------------------------------------------------------------
-* Create the report
-*-----------------------------------------------------------------------------------
SET DROPBLNKLINE=ON
SET BYDISPLAY=ON
TABLE FILE COURSE
HEADING
"McGyver Technique with Dynamic SubFoots"
SUM ORGX           AS ORG  
COMPUTE ORGTOT/A10 =IF FST.CTR EQ 2 THEN ' ' ELSE PTOA(ORG_TOTAL,'(P8.2C)','A10'); 
BY CTR             NOPRINT 
-*
SUM COURSENAMEX    AS COURSE,NAME 
COMPUTE CRSTOT/A10 =IF FST.CTR EQ 2 THEN ' ' ELSE PTOA(COURSE_TOTAL,'(P8.2C)','A10'); 
BY CTR             NOPRINT 
BY ORGX            NOPRINT
BY COURSENAMEX     
-*
SUM   SESSIONX       AS SESSION
      ACTIVITY       AS ACTIVITY
      ACTIVITY_TOTAL AS 'TOTAL'
BY CTR NOPRINT 
ON CTR SUBFOOT 
"Activity Total <ST.ACTIVITY_TOTAL </1" 
WHEN CTR EQ 1
ON CTR SUBFOOT
"Total <ST.ACTIVITY_TOTAL" 
WHEN CTR EQ 2
BY ORGX           NOPRINT            
BY COURSENAMEX    NOPRINT 
BY ACTIVITY       NOPRINT
BY SESSIONX       NOPRINT 
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
GRID=OFF,SQUEEZE=ON,SIZE=9,FONT='ARIAL',$
TYPE=REPORT,HEADALIGN=BODY,$
TYPE=HEADING,SIZE=11,JUSTIFY=CENTER,COLSPAN=7,$
TYPE=TITLE,JUSTIFY=CENTER,STYLE=BOLD,BORDER=LIGHT,$
TYPE=DATA,COLUMN=P2,JUSTIFY=CENTER,$
TYPE=SUBFOOT,STYLE=BOLD,$
TYPE=SUBFOOT,BY=1,ITEM=1,COLOR=RED,COLSPAN=6,JUSTIFY=RIGHT,$
TYPE=SUBFOOT,BY=1,ITEM=2,COLOR=BLUE,COLSPAN=1,JUSTIFY=RIGHT,$
ENDSTYLE
END

Try taking things a little easy. You do not actually need to resort to the McGyver technique and can achieve this in a single pass.

FILEDEF TWOTOTS DISK TWOTOTS.FTM
EX -LINES 7 EDAPUT MASTER, TWOTOTS, C, MEM, FILE=TWOTOTS,SUFFIX=FIX
SEGNAME=SEG01
FIELDNAME=ORG       ,A5      ,A5     , $
FIELDNAME=COURSE    ,A12     ,A12    , $
FIELDNAME=SESSION   ,I2      ,I2     , $
FIELDNAME=ACTIVITY  ,A12     ,A12    , $
FIELDNAME=ACT_TOT   ,D8.2    ,D8.2   , $
-RUN
-WRITE TWOTOTS Org3 Course1      1Lab            48.00
-WRITE TWOTOTS Org3 Course1      2Lab            48.00
-WRITE TWOTOTS Org3 Course2      1Lab            12.00
-WRITE TWOTOTS Org3 Course2      2Lab            24.00
-WRITE TWOTOTS Org3 Course3-1    1Lecture         2.00
-WRITE TWOTOTS Org3 Course3-2    1Lab            12.00
-WRITE TWOTOTS Org3 Course3-2    2Lab            12.00
-WRITE TWOTOTS Org3 Course3-2    3Lab            12.00
-WRITE TWOTOTS Org3 Course3-2    4Lab            12.00
-WRITE TWOTOTS Org3 Course3-2    5PE             12.00
-WRITE TWOTOTS Org3 Course4      1Lecture         4.00
-WRITE TWOTOTS Org3 Course4      2Lab            10.00
-RUN
DEFINE FILE TWOTOTS
  TOT1/D8.2 = IF ACTIVITY EQ 'Lab'     THEN ACT_TOT ELSE 0;
  TOT2/D8.2 = IF ACTIVITY EQ 'Lecture' THEN ACT_TOT ELSE 0;
  TOT3/D8.2 = IF ACTIVITY EQ 'PE'      THEN ACT_TOT ELSE 0;
END
TABLE FILE TWOTOTS
  SUM TOT1 NOPRINT
      TOT2 NOPRINT
      TOT3 NOPRINT
  SUM ACT_TOT      AS 'Total'
   BY ORG
  SUM ACT_TOT      AS 'Total'
   BY ORG
   BY COURSE
  SUM ACT_TOT      AS 'Total'
   BY ORG
   BY COURSE
   BY SESSION
   BY ACTIVITY
ON TABLE NOTOTAL
FOOTING
"Total   <+0> <+0> <+0> <+0> <+0> <ACT_TOT"
" "
"Lab     <+0> <+0> <+0> <+0> <+0> <TOT1"
"Lecture <+0> <+0> <+0> <+0> <+0> <TOT2"
"PE      <+0> <+0> <+0> <+0> <+0> <TOT3"
"Total   <+0> <+0> <+0> <+0> <+0> <ACT_TOT"
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  GRID=OFF, SIZE=10, $
  TYPE=FOOTING, HEADALIGN=BODY, $
  TYPE=FOOTING, ITEM=7, JUSTIFY=RIGHT, $
END

The text in the footing can be moved into different locations if needs be.

T

This message has been edited. Last edited by: Tony A,

Hi Tony,

Nice job on the code

Agreed McGyver not needed if you know ahead of time what your Activity values are and the list is relatively small. If your list is large to infinite, the McGyver Technique is what I recommend. The main problem is the footing/subfooting when the list is variable and/or large. I ran into a problem a year ago with a report that had variable numbers of rows in the subfoot. The McGyver technique was the only way I found that could handle it without crashing the agent.

Thanks and Happy Holidays!

David

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

Variation on Tony's post, that avoids hard-coding the activity sub-total values:

FILEDEF TWOTOTS DISK TWOTOTS.FTM
-*
EX -LINES 7 EDAPUT MASTER, TWOTOTS, C, MEM, FILE=TWOTOTS,SUFFIX=FIX
SEGNAME=SEG01
FIELDNAME=ORG       ,A5      ,A5     , $
FIELDNAME=COURSE    ,A12     ,A12    , $
FIELDNAME=SESSION   ,I2      ,I2     , $
FIELDNAME=ACTIVITY  ,A12     ,A12    , $
FIELDNAME=ACT_TOT   ,D8.2    ,D8.2   , $
-RUN
-*
-WRITE TWOTOTS Org1 Course1      1Lab            48.00
-WRITE TWOTOTS Org1 Course1      2Lab            48.00
-WRITE TWOTOTS Org2 Course2      1Lab            12.00
-WRITE TWOTOTS Org2 Course2      2Lab            24.00
-WRITE TWOTOTS Org3 Course3-1    1Lecture         2.00
-WRITE TWOTOTS Org3 Course3-2    1Lab            12.00
-WRITE TWOTOTS Org3 Course3-2    2Lab            12.00
-WRITE TWOTOTS Org3 Course3-2    3Lab            12.00
-WRITE TWOTOTS Org3 Course3-2    4Lab            12.00
-WRITE TWOTOTS Org3 Course3-2    5PE             12.00
-WRITE TWOTOTS Org3 Course4      1Lecture         4.00
-WRITE TWOTOTS Org3 Course4      2Lab            10.00
-RUN
-*
DEFINE FILE TWOTOTS
  COLUMN_SPACE/A30 = '<+0> <+0> <+0> <+0> <+0> <+0> ';
  DOUBLE_QUOTE/A1  = '"';
END
-*
TABLE FILE TWOTOTS
  SUM
   COLUMN_SPACE
   ACT_TOT
   FST.DOUBLE_QUOTE
  BY DOUBLE_QUOTE
  BY ACTIVITY
  ON TABLE SAVE AS SUBTOTS
END
-RUN
-*
TABLE FILE TWOTOTS
  SUM ACT_TOT      AS 'Total'
   BY ORG
  SUM ACT_TOT      AS 'Total'
   BY ORG
   BY COURSE
  SUM ACT_TOT      AS 'Total'
   BY ORG
   BY COURSE
   BY SESSION
   BY ACTIVITY
 ON TABLE NOTOTAL
 FOOTING
 "Total   <+0> <+0> <+0> <+0> <+0> <TOT.ACT_TOT"
 " "
-INCLUDE SUBTOTS
 "Total   <+0> <+0> <+0> <+0> <+0> <TOT.ACT_TOT"
 ON TABLE SET HTMLCSS ON
 ON TABLE SET PAGE NOLEAD
 ON TABLE SET STYLE *
  GRID=OFF, SIZE=10, $
  TYPE=FOOTING, HEADALIGN=BODY, $
  TYPE=FOOTING, ITEM=7, JUSTIFY=RIGHT, $
 ENDSTYLE
END

David, also to you, Happy Holidays. I have relatives in N.W. Dallas and will probably be over there next year "resting"

Dan, I knew that I could rely upon you to add that finishing touch whilst I was asleep - saved me doing it this morning

There is always some way of achieving what is needed, it's just a case of thinking outside of the box. With such a plethora of techniques on the forum, I would wager that the majority of required techniques are covered at least once!

T

Hi Dan, Nice!

It solves the problem of the variable summarized row issue when only one subfoot/footing is needed.

But what to do when there is a need to display more than one subfoot in a single report?

That was the nut I was trying top crack more that 2 years ago on the report I spoke of earlier. The report was spec'ed to show for all employees per restaurant, time-card detail and then show a summary of time by job worked over a 2 week pay period.

I tried every WF coding technique/trick I could think of...including a variation on yours, Dan, to which I added -LOOPS, -READs and -WRITES, multiple HOLDS and -INCLUDES, COMPOUNDS, etc. I was running into performance issues and then when the number of -LOOPS reached a certain count, WF Server Agent crashes started ocurring.

Searching high and low for ideas, I listened to a webinar on the McGyver Technique almost exactly 2 years ago. I then read all the old articles from the Focus Journal posted on IBI's website. A light bulb went off...If I'd only had this in my toolbox earlier!

If worked beautifully for this report. Performace improved dramatically because of no -LOOPs, no -READs or -WRITEs, no pre-counting, no pre-holds, no COMPOUNDS, no breaking apart alphabetically. Just one pass through the data and the number of subfoots needed didn't matter.

So, my Holiday recommendation for coding subfoots/footings with summarized rows is:
1) When values/rows are fixed with one or mulitple subfoots, use Tony's example.
2) When values/rows are variable with one subfoot/footing, use Dan's technique.
3) When values/rows are variable with multiple subfoots, use McGyver!