I have this query below and I'm trying to get the average of avgYTDSchedDays but it's showing totals instead. I tried using the RECOMPUTE and it works for some of the columns such as YTDLHRev and AVESCHDAYS but not this one and YTDAveRevTrlrDay.

TABLE FILE HOLDRAILDETAIL
PRINT
market AS 'Market'
obLoads AS 'O/B LDS'
ibLoads AS 'I/B LDS'
obTrailers AS 'O/B TRLRS'
ibTrailers AS 'I/B TRLRS'
OBempties AS 'O/B MT MOVES'
IBempties AS 'I/B MT MOVES'
trailerBalance AS 'TRLR BALANCE'
sixWkOB AS '6 WK O/B'
sixWkIB AS '6 WK I/B'
sixWkTrailerBalance AS '6 WK TRLR BALANCE'
YTDob AS 'YTD O/B'
YTDib AS 'YTD I/B'
YTDTrailerBalance AS 'YTD TRLR BALANCE'
totalRev/D20.2!d AS 'LH REV'
COMPUTE LHREV/D12.2!D = totalRev / obLoads; AS 'AVE REV/LD'
COMPUTE AVESCHEDDAYS/D12.2 = ( avgSchedDays * obLoads ) / obLoads;
AS 'AVE SCHED DAYS'
expectedINMDLLoads AS 'EXPECTED IMDL LDS'
COMPUTE AVESCHDAYS/D12.2!D = avgRev / avgSchedDays;
AS 'AVE REV PER TRLR/DAY'
COMPUTE YTDLHRev/D12.2!D = YTDtotalRev / YTDobLoads;
COMPUTE AVEYTDSchedDays/D12.2 = ( avgYTDSchedDays * YTDobLoads ) / YTDobLoads;
AS 'YTD AVE SCHED DAYS'
YTDtotalRev
YTDobLoads
avgYTDSchedDays
COMPUTE YTDAveRevTrlrDay/D12.2!D = YTDLHRev / avgYTDSchedDays;
AS 'YTD AVE REV PER TRLR/DAY'
BY rampRegion AS 'Rail Ramp Region'

ON rampRegion RECOMPUTE
obLoads
ibLoads
obTrailers
ibTrailers
OBempties
trailerBalance
sixWkOB
sixWkIB
sixWkTrailerBalance
totalRev
LHREV
expectedINMDLLoads
AVESCHDAYS
YTDLHRev
AVEYTDSchedDays
avgYTDSchedDays
YTDAveRevTrlrDay AS '*TOTAL'
HEADING
"RAMPS/MARKETS<+0> VOLUMES/BALANCE<+0> REVENUE AND COSTS"
FOOTING
" "
" "
"Column<+0>---------------------Description"
"O/B LDS --------------------------<+0> #Loads picking up in a market"
"I/B LDS----------------------------#Loads delivering to a market"
"O/B TRLRS------------------------#Loads with their first ramp ingate in a market + OB MT MOVES"
"I/B TRLRS-------------------------#Loads with their last ramp outgate in a market"
"O/B MT MOVES------------------#Empty trailer moves with their first ramp ingate in a market"
"TRLR BALANCE-------------------OB TRLRS - IB TRLRS"
"6 WK O/B--------------------------Same as above but for previous 6 weeks"
"6 WK I/B-------------------------- Same as above but for previous 6 weeks"
"6 WK TRLR BALANCE------------ Same as above but for previous 6 weeks"
"LH REV-----------------------------Load Revenue (same method as Previous Loads)"
"AVE REV/LD---------------------- Average of Load Revenue, including 0-rated loads"
"AVE SCHED DAYS--------------- Average time from actual pickup (macro time) to scheduled final delivery"
"EXPECTED IMDL LDS----------- Market-Level commitment for OB INMDL shipper codes"
"AVE REV PER TRLR/DAY------ (LH REV/OB LDS)/AVE SCHED DAYS"
" "
" Zero Rated Moves are all loads which have not been rated (but are included in the report) as of the report running (10:00 am Monday)"
ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL' obLoads ibLoads obTrailers ibTrailers OBempties IBempties trailerBalance sixWkOB sixWkIB sixWkTrailerBalance YTDob YTDib YTDTrailerBalance totalRev
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
LEFTMARGIN=0.500000,
RIGHTMARGIN=0.500000,
TOPMARGIN=0.500000,
BOTTOMMARGIN=0.500000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
TITLETEXT='data',
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=11,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=DATA,
COLUMN=N22,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(255 255 153),
STYLE=NORMAL,
$
TYPE=DATA,
COLUMN=N8,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N13,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N14,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N15,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N4,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N5,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N6,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N2,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(255 255 153),
$
TYPE=DATA,
COLUMN=N9,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N10,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N11,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N12,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N3,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N1,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(255 255 153),
$
TYPE=DATA,
COLUMN=N16,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(255 255 153),
$
TYPE=DATA,
COLUMN=N17,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(255 255 153),
$
TYPE=DATA,
COLUMN=N18,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(255 255 153),
$
TYPE=DATA,
COLUMN=N19,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(255 255 153),
$
TYPE=DATA,
COLUMN=N7,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N20,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(255 255 153),
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TITLE,
COLUMN=N22,
FONT='CALIBRI',
SIZE=11,
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=TITLE,
COLUMN=N8,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=TITLE,
COLUMN=N13,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N14,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N15,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N4,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N5,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N6,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N2,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
$
TYPE=TITLE,
COLUMN=N9,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N10,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N11,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N12,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N3,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N1,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
$
TYPE=TITLE,
COLUMN=N16,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
$
TYPE=TITLE,
COLUMN=N17,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
$
TYPE=TITLE,
COLUMN=N18,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
$
TYPE=TITLE,
COLUMN=N19,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
$
TYPE=TITLE,
COLUMN=N7,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N20,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
BACKCOLOR=RGB(245 250 165),
STYLE=BOLD,
HEADALIGN=BODY,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
COLSPAN=2,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=2,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
COLSPAN=13,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=3,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
COLSPAN=6,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=4,
COLSPAN=1,
JUSTIFY=LEFT,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=5,
COLSPAN=1,
JUSTIFY=LEFT,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=11,
COLSPAN=2,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=2,
FONT='CALIBRI',
COLSPAN=2,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=3,
FONT='CALIBRI',
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=4,
FONT='CALIBRI',
SIZE=11,
$
TYPE=FOOTING,
LINE=2,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=11,
COLSPAN=2,
$
TYPE=FOOTING,
LINE=2,
OBJECT=TEXT,
ITEM=2,
FONT='CALIBRI',
COLSPAN=2,
$
TYPE=FOOTING,
LINE=2,
OBJECT=TEXT,
ITEM=3,
FONT='CALIBRI',
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=2,
OBJECT=TEXT,
ITEM=4,
FONT='CALIBRI',
$
TYPE=FOOTING,
LINE=2,
OBJECT=TEXT,
ITEM=5,
FONT='CALIBRI',
SIZE=11,
$
TYPE=FOOTING,
LINE=2,
OBJECT=TEXT,
ITEM=6,
FONT='CALIBRI',
SIZE=11,
$
TYPE=FOOTING,
LINE=3,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=11,
$
TYPE=FOOTING,
LINE=3,
OBJECT=TEXT,
ITEM=2,
FONT='CALIBRI',
$
TYPE=FOOTING,
LINE=3,
OBJECT=FIELD,
ITEM=1,
FONT='CALIBRI',
$
TYPE=FOOTING,
LINE=4,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=4,
OBJECT=TEXT,
ITEM=2,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=5,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=6,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=7,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=8,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=9,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=10,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=11,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=12,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=13,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=14,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=15,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=16,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=17,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=19,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
COLOR='RED',
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=SUBTOTAL,
BY=1,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N8,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N13,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N14,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N15,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N4,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N5,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N6,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N10,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N11,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N12,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N3,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N7,
BACKCOLOR=RGB(204 255 255),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N8,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N13,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N14,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N15,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N4,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N5,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N6,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N2,
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N9,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N10,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N11,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N12,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N3,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N1,
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N16,
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N17,
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N18,
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N19,
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N7,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
ENDSTYLE
END

I appreciate any input.

Thanks!

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

in order to get an average in the subtotal you have two options. One is to create a subfoot and put all of your subtotals and averages in their as seperate fields. This is a lot more cumbersome of the two options though.

Your other option would be to use the AVE. prefix in your recompute statement that you have below. But you have to make sure that you declare a SUM. on the other fields. webFOCUS will continue using the last prefix declared on the remaining fields.

quote:

ON rampRegion RECOMPUTE
obLoads
ibLoads
obTrailers
ibTrailers
OBempties
trailerBalance
sixWkOB
sixWkIB
sixWkTrailerBalance
totalRev
LHREV
expectedINMDLLoads
AVESCHDAYS
YTDLHRev
AVEYTDSchedDays
avgYTDSchedDays
YTDAveRevTrlrDay AS '*TOTAL'

I would change it as such

  ON rampRegion RECOMPUTE
SUM. obLoads
SUM. ibLoads
SUM. obTrailers
SUM. ibTrailers
SUM. OBempties
SUM. trailerBalance
SUM. sixWkOB
SUM. sixWkIB
SUM. sixWkTrailerBalance
SUM. totalRev
SUM. LHREV
SUM. expectedINMDLLoads
AVE. AVESCHDAYS
SUM. YTDLHRev
AVE. AVEYTDSchedDays
AVE. avgYTDSchedDays
SUM. YTDAveRevTrlrDay AS '*TOTAL'

If you do a print it will never give you the average on one line unless the values you are basing that average upon are in that line.
You are trying to compute the average by deviding two fields. That will only give your the average of that one line.
When you make subtotals and on those subtotalsyou want average value you have to SUM the values and devided that result by the number of records.
I am not sure if thatis what you are up to.

Thanks for the help, but I'm still not getting the real average i'm looking for...to explain it further, I need to get the true average of the subtotal for AVEYTDSchedDays.

If I do it in Excel, this is how it should be calculated.

AVEYTDSchedDays*YTDobLoads (for each row) / SUM(YTDobLoads)

Example:
YTDobLoads AVEYTDSchedDays
257 5.25
429 5.87
765 5.05
---- -------
1451 5.327863542


Thanks!

I would use a multi-verb request
sum
YTDDobLoads as SumYTDDobLoads
sum
...
compute
rowsum/p15.9 = (AVEYTDSchedDays * YTDDobLoads)/SumYTDDobLoads;
...
on ... recompute

The following code is to produce the weighted average on the total lines. See if that applies to your case.

TABLE FILE HOLDRAILDETAIL
SUM
...
YTDobLoads
AVEYTDSchedDays
COMPUTE ST_Loads/D20.2 = YTDobLoads * AVEYTDSchedDays; NOPRINT
COMPUTE TT_Loads/D20.2 = YTDobLoads * AVEYTDSchedDays;NOPRINT

BY BY rampRegion

ON rampRegion RECAP
ST_YTDobLoads/D20.2 = YTDobLoads;
ST_AVEYTDSchedDays/D20.2 = ST_Loads/ST_YTDobLoads;

ON rampRegion SUBFOOT
"&ST_YTDobLoads &ST_AVEYTDSchedDays"

ON TABLE RECAP
TT_YTDobLoads/D20.2 = YTDobLoads;
TT_AVEYTDSchedDays/D20.2 = TT_Loads/TT_YTDobLoads;


ON TABLE SUBFOOT
"&TT_YTDobLoads &TT_AVEYTDSchedDays"

Note: instead of the & before the variables, it should be the less than symbol.

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

Yes, this applies to my case...i did add this to my report but the only thing is, it wiped out all my other subtotals for other columns. i'm now trying to figure out how to put this subtotal under the right column instead of the rampRegion and diplay the rest of the subtotals....


Thanks!

You need to do RECAP on each column you need in the subtotal line, similar to the sample I sent and give them a different variable (in my case I give them a prefix). Then you need to list them in the SUBFOOT line (between the double quote in the order of the column showing in your detail line). To align them you may need add style statements for each variable as below,

TYPE=SUBFOOT,BY=rampRegion ,LINE=1,OBJECT=FIELD,ITEM=1,POSITION='N3',$

Thanks again for the help. I got the values for all the columns I need and I have been working on the formatting so it lines up on the respective columns. For some reason, the subfoot values did not move position at all. I tried it on a couple of columns. Here's what I got below.

-* File rail_recap_weekly1.fex

TABLE FILE HOLDRAILDETAIL
PRINT
market AS 'Market'
obLoads AS 'O/B LDS'
ibLoads AS 'I/B LDS'
obTrailers AS 'O/B TRLRS'
ibTrailers AS 'I/B TRLRS'
OBempties AS 'O/B MT MOVES'
IBempties AS 'I/B MT MOVES'
trailerBalance AS 'TRLR BALANCE'
sixWkOB AS '6 WK O/B'
sixWkIB AS '6 WK I/B'
sixWkTrailerBalance AS '6 WK TRLR BALANCE'
YTDob AS 'YTD O/B'
YTDib AS 'YTD I/B'
YTDTrailerBalance AS 'YTD TRLR BALANCE'
totalRev/D20.2!d AS 'LH REV'
COMPUTE LHREV/D12.2!D = totalRev / obLoads; AS 'AVE REV/LD'
COMPUTE AVESCHEDDAYS/D12.2 = ( avgSchedDays * obLoads ) / obLoads;
AS 'AVE SCHED DAYS'
expectedINMDLLoads AS 'EXPECTED IMDL LDS'
COMPUTE AVESCHDAYS/D12.2!D = avgRev / avgSchedDays;
AS 'AVE REV PER TRLR/DAY'
COMPUTE YTDLHRev/D12.2!D = YTDtotalRev / YTDobLoads;NOPRINT
avgYTDSchedDays NOPRINT
YTDtotalRev NOPRINT
YTDobLoads NOPRINT
COMPUTE AVEYTDSchedDays/D12.2 = ( avgYTDSchedDays * YTDobLoads ) / YTDobLoads;
AS 'YTD AVE SCHED DAYS'
COMPUTE YTDAveRevTrlrDay/D12.2!D = YTDLHRev / AVEYTDSchedDays;
AS 'YTD AVE REV PER TRLR/DAY'
COMPUTE ST_LHREV/D12.2!D = totalRev / obLoads; NOPRINT
COMPUTE ST_Loads/D20.2 = obLoads * AVESCHEDDAYS; NOPRINT
COMPUTE TT_Loads/D20.2 = obLoads * AVESCHEDDAYS; NOPRINT
COMPUTE ST_YTDAveRevTrlrDay/D12.2!D = YTDLHRev / AVEYTDSchedDays;NOPRINT
COMPUTE ST_YTDLoads/D20.2 = YTDobLoads * AVEYTDSchedDays; NOPRINT
COMPUTE TT_YTDLoads/D20.2 = YTDobLoads * AVEYTDSchedDays;NOPRINT
BY rampRegion AS 'Rail Ramp Region'
ON rampRegion RECAP
ST_obLoads/I11 = obLoads;
ST_ibLoads/I11 = ibLoads;
ST_obTrailers/I11 = obTrailers;
ST_ibTrailers/I11 = ibTrailers;
ST_OBempties/I11 = OBempties;
ST_trailerBalance/I11 = trailerBalance;
ST_sixWkOB/I11 = sixWkOB;
ST_sixWkIB/I11 = sixWkIB;
ST_sixWkTrailerBalance/I11 = sixWkTrailerBalance;
ST_totalRev/D20.2!d = totalRev;
ST_LHREV/D12.2!D = ST_LHREV;
ST_AVESCHEDDAYS/D20.2 = ST_Loads/ST_obLoads;
ST_expectedINMDLLoads/I11 = expectedINMDLLoads;
ST_YTDAveRevTrlrDay/D12.2!D= ST_YTDAveRevTrlrDay;
ST_YTDobLoads/D20.2 = YTDobLoads;
ST_AVEYTDSchedDays/D20.2 = ST_YTDLoads/ST_YTDobLoads;
ON rampRegion SUBFOOT
"*TOTAL -*ON TABLE RECAP
-*TT_obLoads = obLoads;
-*TT_totalRev = totalRev;
-*TT_YTDobLoads/D20.2 = YTDobLoads;
-*TT_AVEYTDSchedDays/D20.2 = TT_YTDLoads/TT_YTDobLoads;
-*TT_LHREV/D12.2!D = TT_totalRev/TT_obLoads;
-*ON TABLE SUBFOOT
-*"&HEADING
"RAMPS/MARKETS<+0> VOLUMES/BALANCE<+0> REVENUE AND COSTS"
FOOTING
" "
" "
"Column<+0>---------------------Description"
"O/B LDS --------------------------<+0> #Loads picking up in a market"
"I/B LDS----------------------------#Loads delivering to a market"
"O/B TRLRS------------------------#Loads with their first ramp ingate in a market + OB MT MOVES"
"I/B TRLRS-------------------------#Loads with their last ramp outgate in a market"
"O/B MT MOVES------------------#Empty trailer moves with their first ramp ingate in a market"
"TRLR BALANCE-------------------OB TRLRS - IB TRLRS"
"6 WK O/B--------------------------Same as above but for previous 6 weeks"
"6 WK I/B-------------------------- Same as above but for previous 6 weeks"
"6 WK TRLR BALANCE------------ Same as above but for previous 6 weeks"
"LH REV-----------------------------Load Revenue (same method as Previous Loads)"
"AVE REV/LD---------------------- Average of Load Revenue, including 0-rated loads"
"AVE SCHED DAYS--------------- Average time from actual pickup (macro time) to scheduled final delivery"
"EXPECTED IMDL LDS----------- Market-Level commitment for OB INMDL shipper codes"
"AVE REV PER TRLR/DAY------ (LH REV/OB LDS)/AVE SCHED DAYS"
" "
" Zero Rated Moves are all loads which have not been rated (but are included in the report) as of the report running (10:00 am Monday)"
ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL' obLoads ibLoads obTrailers ibTrailers OBempties IBempties trailerBalance sixWkOB sixWkIB sixWkTrailerBalance YTDob YTDib YTDTrailerBalance totalRev expectedINMDLLoads
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
LEFTMARGIN=0.500000,
RIGHTMARGIN=0.500000,
TOPMARGIN=0.500000,
BOTTOMMARGIN=0.500000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
TITLETEXT='data',
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=11,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=SUBFOOT,
BY=rampRegion,
LINE=1,
ITEM=1,
POSITION='N3',
OBJECT=FIELD,
FONT='CALIBRI',
SIZE=8,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBFOOT,
BY=rampRegion,
LINE=1,
ITEM=2,
POSITION='N4',
OBJECT=FIELD,
FONT='CALIBRI',
SIZE=8,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N22,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(255 255 153),
STYLE=NORMAL,
$
TYPE=DATA,
COLUMN=N8,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N13,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N14,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N15,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N4,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N5,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N6,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N2,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(255 255 153),
$
TYPE=DATA,
COLUMN=N9,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N10,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N11,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N12,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N3,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N1,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(255 255 153),
$
TYPE=DATA,
COLUMN=N16,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(255 255 153),
$
TYPE=DATA,
COLUMN=N17,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(255 255 153),
$
TYPE=DATA,
COLUMN=N18,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(255 255 153),
$
TYPE=DATA,
COLUMN=N19,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(255 255 153),
$
TYPE=DATA,
COLUMN=N7,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=DATA,
COLUMN=N20,
FONT='CALIBRI',
SIZE=9,
BACKCOLOR=RGB(255 255 153),
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TITLE,
COLUMN=N22,
FONT='CALIBRI',
SIZE=11,
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=TITLE,
COLUMN=N8,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=TITLE,
COLUMN=N13,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N14,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N15,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N4,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N5,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N6,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N2,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
$
TYPE=TITLE,
COLUMN=N9,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N10,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N11,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N12,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N3,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N1,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
$
TYPE=TITLE,
COLUMN=N16,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
$
TYPE=TITLE,
COLUMN=N17,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
$
TYPE=TITLE,
COLUMN=N18,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
$
TYPE=TITLE,
COLUMN=N19,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
$
TYPE=TITLE,
COLUMN=N7,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
$
TYPE=TITLE,
COLUMN=N20,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
BACKCOLOR=RGB(245 250 165),
STYLE=BOLD,
HEADALIGN=BODY,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
COLSPAN=2,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=2,
FONT='CALIBRI',
BACKCOLOR=RGB(204 255 255),
COLSPAN=13,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=3,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
COLSPAN=6,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=4,
COLSPAN=1,
JUSTIFY=LEFT,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=5,
COLSPAN=1,
JUSTIFY=LEFT,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=11,
COLSPAN=2,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=2,
FONT='CALIBRI',
COLSPAN=2,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=3,
FONT='CALIBRI',
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=4,
FONT='CALIBRI',
SIZE=11,
$
TYPE=FOOTING,
LINE=2,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=11,
COLSPAN=2,
$
TYPE=FOOTING,
LINE=2,
OBJECT=TEXT,
ITEM=2,
FONT='CALIBRI',
COLSPAN=2,
$
TYPE=FOOTING,
LINE=2,
OBJECT=TEXT,
ITEM=3,
FONT='CALIBRI',
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=2,
OBJECT=TEXT,
ITEM=4,
FONT='CALIBRI',
$
TYPE=FOOTING,
LINE=2,
OBJECT=TEXT,
ITEM=5,
FONT='CALIBRI',
SIZE=11,
$
TYPE=FOOTING,
LINE=2,
OBJECT=TEXT,
ITEM=6,
FONT='CALIBRI',
SIZE=11,
$
TYPE=FOOTING,
LINE=3,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=11,
$
TYPE=FOOTING,
LINE=3,
OBJECT=TEXT,
ITEM=2,
FONT='CALIBRI',
$
TYPE=FOOTING,
LINE=3,
OBJECT=FIELD,
ITEM=1,
FONT='CALIBRI',
$
TYPE=FOOTING,
LINE=4,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=4,
OBJECT=TEXT,
ITEM=2,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=5,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=6,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=7,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=8,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=9,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=10,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=11,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=12,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=13,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=14,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=15,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=16,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=17,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
STYLE=NORMAL,
$
TYPE=FOOTING,
LINE=19,
OBJECT=TEXT,
ITEM=1,
FONT='CALIBRI',
SIZE=9,
COLOR='RED',
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=SUBTOTAL,
BY=1,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N8,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N13,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N14,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N15,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N4,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N5,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N6,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N9,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N10,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N11,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N12,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N3,
BACKCOLOR=RGB(204 255 255),
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=N7,
BACKCOLOR=RGB(204 255 255),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
FONT='CALIBRI',
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N8,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N13,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N14,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N15,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N4,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N5,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N6,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N2,
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N9,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N10,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N11,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N12,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N3,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N1,
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N16,
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N17,
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N18,
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N19,
BACKCOLOR=RGB(255 255 153),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
COLUMN=N7,
BACKCOLOR=RGB(204 255 255),
STYLE=BOLD,
$
ENDSTYLE
END

Thanks!

Winnie

Webfocus 7.7.3

Try this:

ON rampRegion SUBFOOT
"$ST_obLoads $ST_ibLoads $ST_obTrailers $ST_ibTrailers ... $ST_YTDobLoads $ST_AVEYTDSchedDays"

-*(all the fields you need on the subtotal line, replace $ by less than symbol), then add the style code as below for each corresponding field.

TYPE=SUBFOOT,
BY=rampRegion,
LINE=1,
ITEM-1,
POSITION=obLoads,
$
TYPE=SUBFOOT,
BY=rampRegion,
LINE=1,
ITEM-2,
POSITION=ibLoads,
$
...

TYPE=SUBFOOT,
BY=rampRegion,
LINE=1,
ITEM-16,
POSITION=AVEYTDSchedDays,
$

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

I'm actually using "less than" symbol on my subfoot for the the fields and have the code below for my SUBFOOT properties, but Dev Studio doesn't seem to recognize it. Seems like all the subtotals are wrapped together...Here's what I got...

...ON rampRegion SUBFOOT
"*TOTAL
...

$
TYPE=SUBFOOT,
BY=1,
LINE=1,
OBJECT=TEXT,
ITEM=1,
BACKCOLOR=RGB(255 255 153),
JUSTIFY=LEFT,
WIDTH=1.000,
POSITION=obLoads,
$
TYPE=SUBFOOT,
BY=1,
LINE=1,
OBJECT=TEXT,
ITEM=2,
BACKCOLOR=RGB(255 255 153),
JUSTIFY=LEFT,
WIDTH=1.000,
POSITION=ibLoads,
$
TYPE=SUBFOOT,
BY=1,
LINE=1,
OBJECT=TEXT,
ITEM=3,
BACKCOLOR=RGB(204 255 255),
JUSTIFY=LEFT,
WIDTH=1.000,
$
TYPE=SUBFOOT,
BY=1,
LINE=1,
OBJECT=TEXT,
ITEM=4,
JUSTIFY=LEFT,
WIDTH=1.000,
$
TYPE=SUBFOOT,
BY=1,
LINE=1,
OBJECT=TEXT,
ITEM=5,
JUSTIFY=LEFT,
WIDTH=1.000,
$
TYPE=SUBFOOT,
BY=1,
LINE=1,
OBJECT=TEXT,
ITEM=6,
JUSTIFY=LEFT,
WIDTH=1.000,
$
TYPE=SUBFOOT,
BY=1,
LINE=1,
OBJECT=TEXT,
ITEM=7,
JUSTIFY=LEFT,
WIDTH=1.000,
$
TYPE=SUBFOOT,
BY=1,
LINE=1,
OBJECT=TEXT,
ITEM=8,
JUSTIFY=LEFT,
WIDTH=1.000,
$

Thanks!

Winnie

Webfocus 7.7.3

Sorry, I forgot to mention the OBJECT parameter, you should use FIELD for identifying the variables. TEXT is use for hardcoded values.

TYPE=SUBFOOT,
BY=1,
LINE=1,
OBJECT=FIELD,
ITEM=1,
BACKCOLOR=RGB(255 255 153),
JUSTIFY=LEFT,
WIDTH=1.000,
POSITION=obLoads,
$

I actually added the OBJECT=FIELD instead of TEXT on my other SUBFOOT properties and just changed all to use FIELD, but I'm having no luck with it...

I even tried going into the report painter and change each cells as far as the color and font size, but somehow it gets overriden when I modify it in text view...

The version I use is 768...could that be an issue, too?

Thanks!

I don't see there is any difference in the version. It's just frustrating sometimes to format the output lines. Try one more time
TYPE=SUBFOOT,
BY=rampRegion,
LINE=1,
OBJECT=FIELD,
ITEM=1,
BACKCOLOR=RGB(255 255 153),
JUSTIFY=LEFT,
WIDTH=1.000,
POSITION=obLoads,
$

If it still doesn't work, you may try this

POSITION='N3'

where N3 is the 3rd column on the report counting all print and noprint columns including sort fields. So, in your case rampRegion is N1, market is N2...

no such luck...tried both name of columns and i even took out other columns to see if 1 or 2 columns work...

i was just looking at the code you gave me the other day with the recap and subfoot...was there a reason why you added the last set of recap/subfoot below?

TABLE FILE HOLDRAILDETAIL
SUM
...
YTDobLoads
AVEYTDSchedDays
COMPUTE ST_Loads/D20.2 = YTDobLoads * AVEYTDSchedDays; NOPRINT
COMPUTE TT_Loads/D20.2 = YTDobLoads * AVEYTDSchedDays;NOPRINT

BY BY rampRegion

ON rampRegion RECAP
ST_YTDobLoads/D20.2 = YTDobLoads;
ST_AVEYTDSchedDays/D20.2 = ST_Loads/ST_YTDobLoads;

ON rampRegion SUBFOOT
"&ST_YTDobLoads &ST_AVEYTDSchedDays"

ON TABLE RECAP
TT_YTDobLoads/D20.2 = YTDobLoads;
TT_AVEYTDSchedDays/D20.2 = TT_Loads/TT_YTDobLoads;


ON TABLE SUBFOOT
"&TT_YTDobLoads &TT_AVEYTDSchedDays"

The last set ON TABLE RECAP/SUBFOOT just in case you want a grand total for the report.

The ON sortfiled RECAP/SUBFOOT should pair with the TYPE=SUBFOOT,BY= statement.

Use the Text View and edit the RECAP, SUBFOOT and STYLE code as below and see how it come out:

  
ON rampRegion RECAP 
ST_obLoads/I11 = obLoads;
ST_ibLoads/I11 = ibLoads;
ST_obTrailers/I11 = obTrailers;
ST_ibTrailers/I11 = ibTrailers;
ST_OBempties/I11 = OBempties;
ST_trailerBalance/I11 = trailerBalance;
ST_sixWkOB/I11 = sixWkOB;
ST_sixWkIB/I11 = sixWkIB;
ST_sixWkTrailerBalance/I11 = sixWkTrailerBalance;
ST_totalRev/D20.2!d = totalRev;
ST_LHREV/D12.2!D = ST_LHREV;
ST_AVESCHEDDAYS/D20.2 = ST_Loads/ST_obLoads;
ST_expectedINMDLLoads/I11 = expectedINMDLLoads;
ST_YTDAveRevTrlrDay/D12.2!D= ST_YTDAveRevTrlrDay;
ST_YTDobLoads/D20.2 = YTDobLoads;
ST_AVEYTDSchedDays/D20.2 = ST_YTDLoads/ST_YTDobLoads;

-* The variable list below ON rampRegion SUBFOOT is all in one line, I used _LT_ in the list below for the "less than" sign

ON rampRegion SUBFOOT
"TOTAL<+0>ST_obLoads <ST_ibLoads <ST_obTrailers <ST_ibTrailers <ST_OBempties <ST_trailerBalance <ST_sixWkOB <ST_sixWkIB <ST_sixWkTrailerBalance <ST_totalRev <ST_LHREV <ST_AVESCHEDDAYS <ST_expectedINMDLLoads <ST_YTDAveRevTrlrDay <ST_YTDobLoads <ST_AVEYTDSchedDays"

-* The first word 'TOTAL' is a label (OBJECT=TEXT)
-* Any item with the "_LT_" below prefix is a variable (OBJECT=FIELD), 
-* Note that label and variable are count separately starting from 1, left to right.

TYPE=SUBFOOT,BY=rampRegion,LINE=1,OBJECT=TEXT,ITEM=1,COLOR=SILVER,POSITION=rampRegion,$
TYPE=SUBFOOT,BY=rampRegion,LINE=1,OBJECT=FIELD,ITEM=1,COLOR=RED,POSITION=obLoads,$
TYPE=SUBFOOT,BY=rampRegion,LINE=1,OBJECT=FIELD,ITEM=2,COLOR=BLUE,POSITION=ibLoads,$
TYPE=SUBFOOT,BY=rampRegion,LINE=1,OBJECT=FIELD,ITEM=3,COLOR=GREEN,POSITION=obTrailers,$

-* I put the COLOR= in the style statement, just to see if the code really pick the right component
-* Repeat the style line for the rest of the field in the SUBFOOT line

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

Here's what I got...very similar to your query, but it took the _LT_ as a literal string, instead of the less than 1 sign...therefore, there were no real values...

ON rampRegion RECAP
ST_obLoads/I11 = obLoads;
ST_ibLoads/I11 = ibLoads;
ST_obTrailers/I11 = obTrailers;
ST_ibTrailers/I11 = ibTrailers;
ST_OBempties/I11 = OBempties;
ST_trailerBalance/I11 = trailerBalance;
ST_sixWkOB/I11 = sixWkOB;
ST_sixWkIB/I11 = sixWkIB;
ST_sixWkTrailerBalance/I11 = sixWkTrailerBalance;
ST_totalRev/D20.2!d = totalRev;
ST_LHREV/D12.2!D = ST_LHREV;
ST_AVESCHEDDAYS/D20.2 = ST_Loads / ST_obLoads;
ST_expectedINMDLLoads/I11 = expectedINMDLLoads;
ST_YTDAveRevTrlrDay/D12.2!D = ST_YTDAveRevTrlrDay;
ST_YTDobLoads/D20.2 = YTDobLoads;
ST_AVEYTDSchedDays/D20.2 = ST_YTDLoads / ST_YTDobLoads;
ON rampRegion SUBFOOT
"*TOTAL _LT_ST_obLoads _LT_ST_ibLoads _LT_ST_obTrailers"

$
TYPE=SUBFOOT,
BY=rampRegion,
LINE=1,
OBJECT=TEXT,
ITEM=1,
BACKCOLOR=RGB(255 255 153),
JUSTIFY=LEFT,
WIDTH=1.000,
COLOR=SILVER,
POSITION=rampRegion,
$
TYPE=SUBFOOT,
BY=rampRegion,
LINE=1,
OBJECT=FIELD,
ITEM=2,
BACKCOLOR=RGB(204 255 255),
JUSTIFY=LEFT,
WIDTH=1.000,
COLOR=RED,
POSITION=obLoads,
$
TYPE=SUBFOOT,
BY=rampRegion,
LINE=1,
OBJECT=FIELD,
ITEM=3,
BACKCOLOR=RGB(204 255 255),
JUSTIFY=LEFT,
WIDTH=1.000,
COLOR=BLUE,
POSITION=ibLoads,
$
TYPE=SUBFOOT,
BY=rampRegion,
LINE=1,
OBJECT=FIELD,
ITEM=4,
BACKCOLOR=RGB(204 255 255),
JUSTIFY=LEFT,
WIDTH=1.000,
COLOR=GREEN,
POSITION=obTrailers,
$

Replace _LT_ with the less than sign

  

ON rampRegion SUBFOOT
"TOTAL<+0>ST_obLoads <ST_ibLoads <ST_obTrailers <ST_ibTrailers <ST_OBempties <0X
<ST_trailerBalance <ST_sixWkOB <ST_sixWkIB <ST_sixWkTrailerBalance <ST_totalRev <0X
<ST_LHREV <ST_AVESCHEDDAYS <ST_expectedINMDLLoads <ST_YTDAveRevTrlrDay <ST_YTDobLoads <ST_AVEYTDSchedDays"

and
Literal TEXT and variable FIELD hve separate count all starting from 1

TYPE=SUBFOOT,
BY=rampRegion,
LINE=1,
OBJECT=FIELD,
ITEM=1,
BACKCOLOR=RGB(204 255 255),
JUSTIFY=LEFT,
WIDTH=1.000,
COLOR=RED,
POSITION=obLoads,

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

If both of you would put your code between the RED </> tags on the upper-right of the toolbar, you wouldn't have to do LT, *, _, and all the other stuff!!!

Thanks, Tom

 <+0> 

thanks for the post and i think we're getting somewhere but not quite there yet...with the code i have so far, the formatting on the font color is now showing, but the SUBFOOT columns are still not lined up with the columns.

here's the sample code:

ON rampRegion RECAP
ST_obLoads/I11 = obLoads;
ST_ibLoads/I11 = ibLoads;
ST_obTrailers/I11 = obTrailers;
ST_ibTrailers/I11 = ibTrailers;
ST_OBempties/I11 = OBempties;
ST_trailerBalance/I11 = trailerBalance;
ST_sixWkOB/I11 = sixWkOB;
ST_sixWkIB/I11 = sixWkIB;
ST_sixWkTrailerBalance/I11 = sixWkTrailerBalance;
ST_totalRev/D20.2!d = totalRev;
ST_LHREV/D12.2!D = ST_LHREV;
ST_AVESCHEDDAYS/D20.2 = ST_Loads / ST_obLoads;
ST_expectedINMDLLoads/I11 = expectedINMDLLoads;
ST_YTDAveRevTrlrDay/D12.2!D = ST_YTDAveRevTrlrDay;
ST_YTDobLoads/D20.2 = YTDobLoads;
ST_AVEYTDSchedDays/D20.2 = ST_YTDLoads / ST_YTDobLoads;
ON rampRegion SUBFOOT
"*TOTAL "
$
TYPE=SUBFOOT,
LINE=1,
OBJECT=TEXT,
ITEM=1,
COLOR='SILVER',
BACKCOLOR=RGB(255 255 153),
POSITION='N2',
JUSTIFY=LEFT,
WIDTH=1.000,
$
TYPE=SUBFOOT,
LINE=1,
OBJECT=FIELD,
ITEM=2,
COLOR='RED',
BACKCOLOR=RGB(204 255 255),
POSITION='N3',
JUSTIFY=LEFT,
WIDTH=1.000,
$
TYPE=SUBFOOT,
LINE=1,
OBJECT=FIELD,
ITEM=3,
COLOR='BLUE',
BACKCOLOR=RGB(204 255 255),
POSITION='N4',
JUSTIFY=LEFT,
WIDTH=1.000,
$
TYPE=SUBFOOT,
LINE=1,
OBJECT=FIELD,
ITEM=4,
COLOR='GREEN',
BACKCOLOR=RGB(204 255 255),
POSITION='',
JUSTIFY=LEFT,
WIDTH=1.000,
$

Thanks,

Winnie

Webfocus 7.7.3

I guess you totals fields are off by 1, just minus 1 from all the OBJECT=FIELD, should start with 1.

[CODE]ON rampRegion RECAP
ST_obLoads/I11 = obLoads;
ST_ibLoads/I11 = ibLoads;
ST_obTrailers/I11 = obTrailers;
ST_ibTrailers/I11 = ibTrailers;
ST_OBempties/I11 = OBempties;
ST_trailerBalance/I11 = trailerBalance;
ST_sixWkOB/I11 = sixWkOB;
ST_sixWkIB/I11 = sixWkIB;
ST_sixWkTrailerBalance/I11 = sixWkTrailerBalance;
ST_totalRev/D20.2!d = totalRev;
ST_LHREV/D12.2!D = ST_LHREV;
ST_AVESCHEDDAYS/D20.2 = ST_Loads / ST_obLoads;
ST_expectedINMDLLoads/I11 = expectedINMDLLoads;
ST_YTDAveRevTrlrDay/D12.2!D = ST_YTDAveRevTrlrDay;
ST_YTDobLoads/D20.2 = YTDobLoads;
ST_AVEYTDSchedDays/D20.2 = ST_YTDLoads / ST_YTDobLoads;
ON rampRegion SUBFOOT
"*TOTAL <... "
$
TYPE=SUBFOOT,
LINE=1,
OBJECT=TEXT,
ITEM=1,
COLOR='SILVER',
BACKCOLOR=RGB(255 255 153),
POSITION='N2',
JUSTIFY=LEFT,
WIDTH=1.000,
$
TYPE=SUBFOOT,
LINE=1,
OBJECT=FIELD,
ITEM=1,
COLOR='RED',
BACKCOLOR=RGB(204 255 255),
POSITION='N3',
JUSTIFY=LEFT,
WIDTH=1.000,
$
TYPE=SUBFOOT,
LINE=1,
OBJECT=FIELD,
ITEM=2,
COLOR='BLUE',
BACKCOLOR=RGB(204 255 255),
POSITION='N4',
JUSTIFY=LEFT,
WIDTH=1.000,
$
TYPE=SUBFOOT,
LINE=1,
OBJECT=FIELD,
ITEM=3,
COLOR='GREEN',
BACKCOLOR=RGB(204 255 255),
POSITION='',
JUSTIFY=LEFT,
WIDTH=1.000,
$[/quote]

The font color part worked, but i still have an issue with the SUBFOOT not lining up with columns...i have changed the POSITION to reference either the name of the column or the generic name (ex. 'N1'...). I did open a case in IBI to see if there's something I'm missing in my code..hopefully, there's a resolution to this...seems so simple but it's kicking my butt!

Thanks!

Based on your original codes:

TABLE FILE HOLDRAILDETAIL
PRINT
market AS 'Market'
obLoads AS 'O/B LDS'
ibLoads AS 'I/B LDS'
obTrailers AS 'O/B TRLRS'
ibTrailers AS 'I/B TRLRS'
OBempties AS 'O/B MT MOVES'
IBempties AS 'I/B MT MOVES'
trailerBalance AS 'TRLR BALANCE'
sixWkOB AS '6 WK O/B'
sixWkIB AS '6 WK I/B'
sixWkTrailerBalance AS '6 WK TRLR BALANCE'
YTDob AS 'YTD O/B'
YTDib AS 'YTD I/B'
YTDTrailerBalance AS 'YTD TRLR BALANCE'
totalRev/D20.2!d AS 'LH REV'
COMPUTE LHREV/D12.2!D = totalRev / obLoads; AS 'AVE REV/LD'
COMPUTE AVESCHEDDAYS/D12.2 = ( avgSchedDays * obLoads ) / obLoads;
AS 'AVE SCHED DAYS'
expectedINMDLLoads AS 'EXPECTED IMDL LDS'
COMPUTE AVESCHDAYS/D12.2!D = avgRev / avgSchedDays;
AS 'AVE REV PER TRLR/DAY'
COMPUTE YTDLHRev/D12.2!D = YTDtotalRev / YTDobLoads;NOPRINT
avgYTDSchedDays NOPRINT
YTDtotalRev NOPRINT
YTDobLoads NOPRINT
COMPUTE AVEYTDSchedDays/D12.2 = ( avgYTDSchedDays * YTDobLoads ) / YTDobLoads;
AS 'YTD AVE SCHED DAYS'
COMPUTE YTDAveRevTrlrDay/D12.2!D = YTDLHRev / AVEYTDSchedDays;
AS 'YTD AVE REV PER TRLR/DAY'
COMPUTE ST_LHREV/D12.2!D = totalRev / obLoads; NOPRINT
COMPUTE ST_Loads/D20.2 = obLoads * AVESCHEDDAYS; NOPRINT
COMPUTE TT_Loads/D20.2 = obLoads * AVESCHEDDAYS; NOPRINT
COMPUTE ST_YTDAveRevTrlrDay/D12.2!D = YTDLHRev / AVEYTDSchedDays;NOPRINT
COMPUTE ST_YTDLoads/D20.2 = YTDobLoads * AVEYTDSchedDays; NOPRINT
COMPUTE TT_YTDLoads/D20.2 = YTDobLoads * AVEYTDSchedDays;NOPRINT
BY rampRegion AS 'Rail Ramp Region'

The corresponding POSITION as below: (NOTE: you need to count NOPRINT as well)
rampRegion is POSITION=N1
market is POSITION=N2
obLoads is POSITION=N3
ibLoads is POSITION=N4
obTrailers is POSITION=N5
ibTrailers is POSITION=N6
OBempties is POSITION=N7
IBempties is POSITION=N8
trailerBalance is POSITION=N9
sixWkOB is POSITION=N10
sixWkIB is POSITION=N11
sixWkTrailerBalance is POSITION=N12
YTDob is POSITION=N13
YTDib is POSITION=N14
YTDTrailerBalance is POSITION=N15
totalRev/D20.2!d is POSITION=N16
COMPUTE LHREV/D12.2!D = totalRev / obLoads; is POSITION=N17
COMPUTE AVESCHEDDAYS/D12.2 = ( avgSchedDays * obLoads ) / obLoads; is POSITION=N18
expectedINMDLLoads is POSITION=N19
COMPUTE AVESCHDAYS/D12.2!D = avgRev / avgSchedDays; is POSITION=N20
COMPUTE YTDLHRev/D12.2!D = YTDtotalRev / YTDobLoads;NOPRINT =N21
avgYTDSchedDays NOPRINT =N22
YTDtotalRev NOPRINT =N23
YTDobLoads NOPRINT =N24
COMPUTE AVEYTDSchedDays/D12.2 = ( avgYTDSchedDays * YTDobLoads ) / YTDobLoads; is POSITION=N25
COMPUTE YTDAveRevTrlrDay/D12.2!D = YTDLHRev / AVEYTDSchedDays; is POSITION=N26
COMPUTE ST_LHREV/D12.2!D = totalRev / obLoads; NOPRINT
COMPUTE ST_Loads/D20.2 = obLoads * AVESCHEDDAYS; NOPRINT
COMPUTE TT_Loads/D20.2 = obLoads * AVESCHEDDAYS; NOPRINT
COMPUTE ST_YTDAveRevTrlrDay/D12.2!D = YTDLHRev / AVEYTDSchedDays;NOPRINT
COMPUTE ST_YTDLoads/D20.2 = YTDobLoads * AVEYTDSchedDays; NOPRINT
COMPUTE TT_YTDLoads/D20.2 = YTDobLoads * AVEYTDSchedDays;NOPRINT

Thanks and I am aware of the NOPRINT; my issue is the SUBFOOT is all wrapped in one cell...i can specify the position but these aren't getting separated at all...In my example, I removed all the SUBFOOT columns except for the 3 columns. Here's an example of my output.

Rail Ramp Region Market O/B LDS I/B LDS O/B TRLRS I/B TRLRS
Arizona AZ 0 4 11 4
*TOTAL 0 4 11

Thanks!

POSITION is used for PDF.

TYPE=SUBFOOT, HEADALIGN=BODY, STYLE=BOLD,$

PUT YOUR CODE BETWEEN THE RED CODE TAGS </>

I got it to finally work! All I needed to do in my code is to put the HEADALIGN=BODY in my SUBFOOT property and everything else lined up...

Thanks so much for all the help!