Hi,

I currently have a report which sums the field NET_WEIGHT which then has subtotals as dictated by the BY fields. However, i wish to exclude certain data from one of the subtotals. I have tried using a WHEN statement but to no avail. I have copied in my code below. How can i exclude a particular RTMCODE from the subtotal?

TABLE FILE FD_DESPATCH_STOCKS
SUM
NET_WEIGHT AS 'Free To Go(Kt)'
AVE.DAYS_AS_COIL AS ' Avg FTG Coil Dwell (Days)'
CNT.DAYS_AS_COIL AS 'No Of Coils FTG > 5 Days Dwell'
MAX.DAYS_AS_COIL AS ' Top 20 Oldest Coils (Days)'
BY CURRENT_SITE_CD AS 'Site'
BY BAYGROUP AS 'Area'
BY RTMCODE AS 'RTM (Supply Chain)'
ON CURRENT_SITE_CD SUBTOTAL NET_WEIGHT AS '*TOTAL excl Unallocated' WHEN RTMCODE NE 'Unallocated';
ON BAYGROUP SUBTOTAL AS '*TOTAL'
HEADING
"Free To Go Stock Report"
"Last Updated WHERE COILSTATUS EQ '1.Free To Go';
WHERE DAYS_AS_COIL GT 5;
WHERE DAYS_AS_COIL LE 20;
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
FONT='ARIAL',
SIZE=8,
$
TYPE=DATA,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
FONT='ARIAL',
SIZE=8,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
BORDER-TOP-STYLE=SOLID,
BORDER-BOTTOM-STYLE=SOLID,
BORDER-LEFT-STYLE=SOLID,
BORDER-RIGHT-STYLE=SOLID,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=HEADING,
SIZE=10,
BACKCOLOR=RGB(255 204 0),
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=2,
OBJECT=TEXT,
ITEM=1,
SIZE=8,
$
TYPE=HEADING,
LINE=2,
OBJECT=FIELD,
ITEM=1,
SIZE=8,
$
TYPE=SUBTOTAL,
BY=1,
BACKCOLOR=RGB(0 156 155),
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BY=2,
BACKCOLOR=RGB(255 204 0),
STYLE=BOLD,
$
ENDSTYLE
END


cheers

Stu.

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

WebFocus 7.6.5
Windows
HTML (some Excel and PDF)

Stu,

Outside of using a SUBFOOT, hth:

TABLE FILE FD_DESPATCH_STOCKS
SUM
NET_WEIGHT AS 'Free To Go(Kt)'
AVE.DAYS_AS_COIL AS ' Avg FTG Coil Dwell (Days)'
CNT.DAYS_AS_COIL AS 'No Of Coils FTG > 5 Days Dwell'
MAX.DAYS_AS_COIL AS ' Top 20 Oldest Coils (Days)'
COMPUTE FLAG1/A1 = IF RTMCODE EQ 'Unallocated' THEN 'N' ELSE 'Y'; NOPRINT
BY CURRENT_SITE_CD AS 'Site'
BY BAYGROUP AS 'Area'
BY RTMCODE AS 'RTM (Supply Chain)'
ON CURRENT_SITE_CD SUBTOTAL NET_WEIGHT AS '*TOTAL excl Unallocated' WHEN FLAG1 EQ 'Y';
ON BAYGROUP SUBTOTAL AS '*TOTAL'

Is this what you want?

DEFINE FILE FD_DESPATCH_STOCKS
NEWNET/D12 = IF RTMCODE EQ 'Unallocated' THEN 0 ELSE NET_WEIGHT;
END

Then use NEWNET as your field.

Hi Tom...that looks better.

I have tried both suggestions and neither seem to work.
Tom - when i run the fex using your code it does not display the subtotal for '*TOTAL excl Unallocated'.....
any suggestions??

Stu.

Stu,

I missed the 2nd one:

This works, see if it does on your platform:

 
TABLE FILE CAR
SUM
   RETAIL_COST
   DEALER_COST
     COMPUTE FLAG1/A1 = IF COUNTRY EQ 'JAPAN' THEN 'N' ELSE 'Y'; NOPRINT
 BY COUNTRY SUBTOTAL DEALER_COST AS '*Total' WHEN FLAG1 EQ 'Y';
 BY CAR AS '*Total' WHEN FLAG1 EQ 'Y';
 BY MODEL
END
-EXIT
 

EDIT:

Stu, you need to bring RTMCODE in:

  
SUM
NET_WEIGHT AS 'Free To Go(Kt)'
AVE.DAYS_AS_COIL AS ' Avg FTG Coil Dwell (Days)'
CNT.DAYS_AS_COIL AS 'No Of Coils FTG > 5 Days Dwell'
MAX.DAYS_AS_COIL AS ' Top 20 Oldest Coils (Days)'
   --->         RTMCODE NOPRINT
COMPUTE FLAG1/A1 = IF RTMCODE EQ 'Unallocated' THEN 'N' ELSE 'Y'; NOPRINT
BY CURRENT_SITE_CD AS 'Site'
BY BAYGROUP AS 'Area'
BY RTMCODE AS 'RTM (Supply Chain)'
ON CURRENT_SITE_CD SUBTOTAL NET_WEIGHT AS '*TOTAL excl Unallocated' WHEN FLAG1 EQ 'Y'; 
ON BAYGROUP SUBTOTAL AS '*TOTAL' WHEN FLAG1 EQ 'Y';

This message has been edited. Last edited by: Tom Flynn,

Tom,

That didnt work, i got the following error,

0 ERROR AT OR NEAR LINE 7 IN PROCEDURE rep_srtest
(FOC777) OPTION FOR 'ON' PHRASE IS NOT SUPPORTED WITH 'WHEN' CLAUSE
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

Any ideas?

Stu.

Not knowing the specs, I would try this road:

hth

 
DEFINE FILE FD_DESPATCH_STOCKS
  NET_WT_1/D12.2 = IF RTMCODE EQ 'Unallocated' THEN NET_WEIGHT ELSE 0;
  NET_WT_2/D12.2 = IF RTMCODE NE 'Unallocated' THEN NET_WEIGHT ELSE 0;
END

TABLE FILE FD_DESPATCH_STOCKS
SUM
NET_WEIGHT AS 'Free To Go(Kt)'
AVE.DAYS_AS_COIL AS ' Avg FTG Coil Dwell (Days)'
CNT.DAYS_AS_COIL AS 'No Of Coils FTG > 5 Days Dwell'
MAX.DAYS_AS_COIL AS ' Top 20 Oldest Coils (Days)'
   NET_WT_1 NOPRINT
   NET_WT_2 NOPRINT
BY CURRENT_SITE_CD AS 'Site' 
BY BAYGROUP AS 'Area' SUBTOTAL AS '*TOTAL'
BY RTMCODE AS 'RTM (Supply Chain)'
  
ON CURRENT_SITE_CD RECAP
  NET_WT_X/D12.2 = NET_WT_2 - NET_WT_1;
  
ON CURRENT_SITE_CD SUBFOOT
"*TOTAL excl Unallocated<+0><ST.NET_WT_X<+0> <+0> <+0> <+0> <+0> <+0>"
  
HEADING
"Free To Go Stock Report"
"Last Updated WHERE COILSTATUS EQ '1.Free To Go';
WHERE DAYS_AS_COIL GT 5;
WHERE DAYS_AS_COIL LE 20;
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON 
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
FONT='ARIAL',
SIZE=8,
$

TYPE=SUBFOOT, HEADALIGN=BODY, STYLE=BOLD, BACKCOLOR=RGB(0 156 155),$

TYPE=DATA,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
FONT='ARIAL',
SIZE=8,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
BORDER-TOP-STYLE=SOLID,
BORDER-BOTTOM-STYLE=SOLID,
BORDER-LEFT-STYLE=SOLID,
BORDER-RIGHT-STYLE=SOLID,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=HEADING,
SIZE=10,
BACKCOLOR=RGB(255 204 0),
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=2,
OBJECT=TEXT,
ITEM=1,
SIZE=8,
$
TYPE=HEADING,
LINE=2,
OBJECT=FIELD,
ITEM=1,
SIZE=8,
$
TYPE=SUBTOTAL,
BY=1,
BACKCOLOR=RGB(0 156 155),
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BY=2,
BACKCOLOR=RGB(255 204 0),
STYLE=BOLD,
$
ENDSTYLE
END
 

This message has been edited. Last edited by: Tom Flynn,