Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Dynamically alligning subfoot calculation

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Dynamically alligning subfoot calculation
 Login/Join
 
Silver Member
posted
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,


WebFOCUS 8103 Appstudio, Dev Studio MRE
Client Windows 7, Serveron Unix.
Excel, PDF, HTML,AHTML
 
Posts: 38 | Registered: January 24, 2008Report This Post
Expert
posted Hide Post
Please post your code between the code tags.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
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


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Silver Member
posted Hide Post
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,


WebFOCUS 8103 Appstudio, Dev Studio MRE
Client Windows 7, Serveron Unix.
Excel, PDF, HTML,AHTML
 
Posts: 38 | Registered: January 24, 2008Report This Post
<JG>
posted
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  
 
Report This Post
Expert
posted Hide Post
quote:
I need the subtotals should display under respctive country


Sorry, just working from your example output.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Silver Member
posted Hide Post
Thanks JG let me give a try..
If we use HTMLFORM , iam not sure about positions in PDF and EXCEL formats.


WebFOCUS 8103 Appstudio, Dev Studio MRE
Client Windows 7, Serveron Unix.
Excel, PDF, HTML,AHTML
 
Posts: 38 | Registered: January 24, 2008Report This Post
<JG>
posted
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.
 
Report This Post
Virtuoso
posted Hide Post
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


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Silver Member
posted Hide Post
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.


WebFOCUS 8103 Appstudio, Dev Studio MRE
Client Windows 7, Serveron Unix.
Excel, PDF, HTML,AHTML
 
Posts: 38 | Registered: January 24, 2008Report This Post
Expert
posted Hide Post
Nice Thread Please ensure that you only traverse the sourse final once, or as feww tims as really required, and work from the hold file...
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Dynamically alligning subfoot calculation

Copyright © 1996-2020 Information Builders