Hi All

Iam trying to allign subtotals under the Across respective group.I tried COLSPAN and POSITION.

now what iam getting is:

  
      		group1	group2	group3
field1	field2 			
A	B	123	234	345
C	B	123	234	345
F2 SUBTOTAL:690 

what i need is:

              group1   group2 group3
field1    field2 
A          B        123     234     345
C          B        123     234     345
          F2 SUBTOTAL:              690

here is the code:

-SET ECHO='ALL';


SET ASNAMES = ON

TABLE FILE CAR
SUM DEALER_COST AS 'DC'
RETAIL_COST AS 'RC'
BY CAR
BY MODEL
BY COUNTRY
ON TABLE HOLD AS HOLDTOTSALES FORMAT FOCUS
END
-RUN

DEFINE FILE HOLDTOTSALES
DESCR/A10='Orders';
AMOUNT/I9=DC;
END

TABLE FILE HOLDTOTSALES
SUM AMOUNT
BY CAR
BY MODEL
BY COUNTRY
-*BY FISCAL_PERIOD
BY DESCR
ON TABLE HOLD AS HOLD1
END



DEFINE FILE HOLDTOTSALES
DESCR/A10='Sales';
AMOUNT/I9=RC;
END

TABLE FILE HOLDTOTSALES
SUM AMOUNT
BY CAR
BY MODEL
BY COUNTRY
-*BY FISCAL_PERIOD
BY DESCR
ON TABLE HOLD AS HOLD2
END



TABLE FILE HOLD1
PRINT *
ON TABLE HOLD AS HOLD3
MORE
FILE HOLD2
END


DEFINE FILE HOLD3
HEAD_FIELD/A1000 = IF DESCR EQ 'Orders' THEN 'HEAD1' ELSE 'HEAD2';
CAR1/A35='Model Total';
CTY/A25='Car Total';
FMT/A8=IF (DESCR EQ 'Orders') THEN 'D12' ELSE 'D12.1%';
END


SET ASNAMES = ON
TABLE FILE HOLD3
SUM AMOUNT AS ''
BY CAR AS 'CARS'
BY MODEL AS 'MODELS'
BY CAR1 NOPRINT
ACROSS DESCR AS '' NOPRINT
ACROSS HEAD_FIELD AS ''
ACROSS COUNTRY AS ''
ACROSS DESCR AS '' NOPRINT
ON MODEL SUBFOOT 
"<+0> <CAR1 <0><AMOUNT"
ON TABLE COLUMN-TOTAL AS 'GRAND TOTAL'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET GRID OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,     SQUEEZE=ON,     $
TYPE=REPORT, HEADALIGN=BODY,$
TYPE=SUBFOOT,BY=2, LINE=1,OBJECT=FIELD,ITEM=1,POSITION=CAR, JUSTIFY=LEFT,WIDTH=1.000,BACKCOLOR='GREY',$
TYPE=SUBFOOT,BY=2, LINE=1,OBJECT=FIELD,ITEM=2,POSITION=AMOUNT,  JUSTIFY=RIGHT,WIDTH=1.000,BACKCOLOR='GREY',$
ENDSTYLE
END

In this Model Totals should be placed under Model column and subtotals should be placed under respective Country.
And also Contry is the Userinput.

Please Help...

Thanks in Advance
Saikumar.

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

Please post your code between the code tags.

This is with COLSPAN.

-SET ECHO='ALL';
SET ASNAMES = ON

TABLE FILE CAR
SUM DEALER_COST AS 'DC'
RETAIL_COST AS 'RC'
BY CAR
BY MODEL
BY COUNTRY
ON TABLE HOLD AS HOLDTOTSALES FORMAT FOCUS
END
-RUN

DEFINE FILE HOLDTOTSALES
DESCR/A10='Orders';
AMOUNT/I9=DC;
END

TABLE FILE HOLDTOTSALES
SUM AMOUNT
BY CAR
BY MODEL
BY COUNTRY
-*BY FISCAL_PERIOD
BY DESCR
ON TABLE HOLD AS HOLD1
END


DEFINE FILE HOLDTOTSALES
DESCR/A10='Sales';
AMOUNT/I9=RC;
END

TABLE FILE HOLDTOTSALES
SUM AMOUNT
BY CAR
BY MODEL
BY COUNTRY
-*BY FISCAL_PERIOD
BY DESCR
ON TABLE HOLD AS HOLD2
END

TABLE FILE HOLD1
PRINT *
ON TABLE HOLD AS HOLD3
MORE
FILE HOLD2
END

DEFINE FILE HOLD3
HEAD_FIELD/A1000 = IF DESCR EQ 'Orders' THEN 'HEAD1' ELSE 'HEAD2';
CAR1/A35='Model Total';
CTY/A25='Car Total';
FMT/A8=IF (DESCR EQ 'Orders') THEN 'D12' ELSE 'D12.1%';
END

TABLE FILE HOLD3
SUM AMOUNT AS ''
BY DESCR
BY HEAD_FIELD
BY COUNTRY
BY DESCR
ON TABLE HOLD AS ROW_CNT
END

-RUN

-SET &ACR_COLS = &LINES ;

SET ASNAMES = ON
TABLE FILE HOLD3
SUM AMOUNT AS ''
BY CAR AS 'CARS'
BY MODEL AS 'MODELS'
BY CAR1 NOPRINT
ACROSS DESCR AS '' NOPRINT
ACROSS HEAD_FIELD AS ''
ACROSS COUNTRY AS ''
ACROSS DESCR AS '' NOPRINT
ON MODEL SUBFOOT 
" <CAR1<AMOUNT"
ON TABLE COLUMN-TOTAL AS 'GRAND TOTAL'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET GRID OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,     SQUEEZE=ON,     $
TYPE=REPORT, HEADALIGN=BODY,$
TYPE=SUBFOOT,BY=2, LINE=1,OBJECT=FIELD,ITEM=1,JUSTIFY=LEFT,WIDTH=1.000,BACKCOLOR='GREY',$
TYPE=SUBFOOT,BY=2, LINE=1,OBJECT=FIELD,ITEM=2,COLSPAN=&ACR_COLS,  JUSTIFY=RIGHT,BACKCOLOR='GREY',$
ENDSTYLE
END

quote:

subtotals should be placed under respective Country.

Waz...the subtotals are displaying under the last country since the colspan is depends on number of countries...

I need the subtotals should display under respctive country.

Thanks,
Saikumar.

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

exactly subtotals that what you sould be using

try this alternative approach

SET ASNAMES = ON
TABLE FILE HOLD3
SUM AMOUNT AS ''
BY CAR AS 'CARS'
BY MODEL AS 'MODELS' 
BY CAR1 NOPRINT
ACROSS DESCR AS '' NOPRINT
ACROSS HEAD_FIELD AS ''
ACROSS COUNTRY AS ''
ACROSS DESCR AS '' NOPRINT
ON MODEL SUBTOTAL AS 'Model Total' 
ON TABLE COLUMN-TOTAL AS 'GRAND TOTAL'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET GRID OFF
ON TABLE SET HTMLCSS ON
ON TABLE HOLD FORMAT HTMTABLE
ON TABLE SET STYLE *
     UNITS=IN,     SQUEEZE=ON,     $
TYPE=REPORT, HEADALIGN=BODY,$
TYPE=SUBTOTAL,BACKCOLOR='silver',$
ENDSTYLE
END
-RUN
-HTMLFORM BEGIN
<head>
<script>
function subTitle()
{

var cells = document.getElementsByTagName("td");
for (var i = 0; i < cells.length; i++)
{ 
   var cellind= document.getElementsByTagName("td")[i];
   if (cellind.innerHTML.substring(0,11) == 'Model Total')
   {
    cellind.innerHTML='<div align="right">Model Total</div>';
	}
   else
   {
	cellind.innerHTML ;
    }
}
}
</script>  
</head>
<Body onLoad=subTitle();>
!IBI.FIL.HOLD;
</body>
-HTMLFORM END  

quote:

I need the subtotals should display under respctive country

Sorry, just working from your example output.

Thanks JG let me give a try..
If we use HTMLFORM , iam not sure about positions in PDF and EXCEL formats.

No PDF or Excel, They do not understand JS.

If you want to do it for all formats then your only option is to generate the required subtotals
and grand total as extra hold files,
Assign artifical sort keys for each hold step and use those for your sorting and reporting.

If you use Dialogue Manager instead of JavaScript to modify the HTMTABLE hold file, it will work for both HTML and Excel output formats - but not PDF.

TABLE FILE HOLD3
 SUM AMOUNT AS ''
 BY CAR AS 'CARS'
 BY MODEL AS 'MODELS'
 BY CAR1 NOPRINT
 ACROSS DESCR AS '' NOPRINT
 ACROSS HEAD_FIELD AS ''
 ACROSS COUNTRY AS ''
 ACROSS DESCR AS '' NOPRINT
 ON MODEL SUBTOTAL AS '*Subtotal'
 ON TABLE COLUMN-TOTAL AS 'GRAND TOTAL'
 ON TABLE SET PAGE-NUM OFF
 ON TABLE SET GRID OFF
 ON TABLE SET HTMLCSS ON
 ON TABLE HOLD AS RHOLD1 FORMAT HTMTABLE
 ON TABLE SET STYLE *
  UNITS=IN, SQUEEZE=ON, $
  TYPE=SUBTOTAL, JUSTIFY=RIGHT, BACKCOLOR='SILVER', $
 ENDSTYLE
END
-*
APP FILEDEF RHOLD2 DISK rhold2.ftm
-RUN
-*
-SET &IORETURN = 0 ;
-REPEAT ENDREPEAT1 WHILE &IORETURN EQ 0 ;
-READ RHOLD1, &STRING1
-IF &IORETURN NE 0 GOTO ENDREPEAT1 ;
-IF &STRING1 OMITS '*Subtotal' GOTO WRITESTRING ;
-SET &STRING1 = 'Model Totals</TD>';
-WRITESTRING
-WRITE RHOLD2 &STRING1
-ENDREPEAT1
-*
SET HTMLFORMTYPE=XLS
-RUN
-*
-HTMLFORM BEGIN
 !IBI.FIL.RHOLD2 ;
-HTMLFORM END

Thanks All for your time ,

As per JG advise here is the solution...

  

-SET ECHO='ALL';

DEFINE FILE CAR
CTY/A25=COUNTRY;
CARR/A35=CAR;
MDL/A45=MODEL;
CTYSORT/I1=1;
CARSORT/I1=1;
END

TABLE FILE CAR
SUM  'CAR.BODY.SALES'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
BY CTY
BY CTYSORT
BY CARR
BY CARSORT
BY MDL
BY SEATS
ON TABLE HOLD AS A
END


DEFINE FILE CAR
CTYT1/A25=COUNTRY | ' Total';
CART1/A35='';
MDLT1/A45='';
CTYTSORT1/I1=2;
CARTSORT1/I1=2;
END


TABLE FILE CAR
SUM  'CAR.BODY.SALES'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
BY CTYT1
BY CTYTSORT1
BY CART1
BY CARTSORT1
BY MDLT1
-*ACROSS SEATS
BY SEATS
ON TABLE HOLD AS B
END


DEFINE FILE CAR
CTYT2/A25=COUNTRY;
CART2/A35=CAR | ' Total';
MDLT2/A45='';
CTYTSORT2/I1=2;
CARTSORT2/I1=2;
END

TABLE FILE CAR
SUM  'CAR.BODY.SALES'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
BY CTYT2
BY CTYTSORT2
BY CART2
BY CARTSORT2
BY MDLT2
BY SEATS
ON TABLE HOLD AS C
END


TABLE FILE A
PRINT *
ON TABLE HOLD AS D
MORE
FILE B
MORE
FILE C
END



TABLE FILE D
SUM
SALES
DEALER_COST
RETAIL_COST
-*BY CTYSORT
-*BY CARSORT
-*BY SORT NOPRINT
BY CTY
BY CARR
ACROSS SEATS
END

Thanks,
Saikumar.

Please ensure that you only traverse the sourse final once, or as feww tims as really required, and work from the hold file...