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] Printing the BY field

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[Closed] Printing the BY field
 Login/Join
 
Gold member
posted
Hi All,

I have two sort fields on my report. see example below

 TABLE FILE TABLE_A
SUM
COL1
BY COL3
BY COL4
BY COL2
ON COL4 SUBTOTAL AS 'SUB-TOTAL YEAR'
ON COL3 SUBTOTAL AS ''
ON TABLE PCHOLD FORMAT HTML
END 



I want o/p something like

  
COL3   COL4   COL2   COL1
----   ----   ----   ----
USD    2001   ABC    $50
USD    2001   XYZ    $30

USD  SUB-TOTAL       $80
     YEAR 2001

USD    2003   DEF    $20

USD  SUB-TOTAL       $20
     YEAR 2003

USD  GRAND TOTAL     $100

AUD    2001   ABC    $70

AUD  SUB-TOTAL       $70
     YEAR 2001





How to achieve that style format. I am getting it like below

 

COL3   COL4   COL2   COL1
----   ----   ----   ----
USD    2001   ABC    $50
              XYZ    $30

SUB-TOTAL YEAR 2001  $80
     

       2003   DEF    $20

SUB-TOTAL YEAR 2003  $20
      

USD                  $100

AUD    2001   ABC    $70

SUB-TOTAL YEAR 2001  $70
     


 

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


In Focus since 2008
WebFOCUS 8.2.0.1
Windows 7 - IE,Chrome,Firefox
Excel, PDF, HTML, AHTML, XML
JavaScript, jQuery, D3.js, Highcharts
 
Posts: 79 | Location: New York | Registered: February 04, 2010Report This Post
Virtuoso
posted Hide Post
To get that kind of control over the presentation you will need to use SUBFOOT instead of SUBTOTAL; you can then put your text and field elements anywhere you need (including in multiples lines). Keep in mind that you'll most certainly need to use spot markers (<+0>Wink to properly style each element.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Expert
posted Hide Post
If you want to repeat the BY values, simply add this to the beginning of your focexec:
SET BYDISPLAY=ON

For the subtotal, you'll need to look at the previous post for a possible solution.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
Also, the standard functionality of using a BY field is to only print the sort field when the value changes. If you want to display them you can use the setting ON TABLE SET BYDISPLAY ON. You could also just include them in your print statement:
TABLE FILE TABLE_A
SUM
COL3 COL4 COL2
COL1
BY COL3 NOPRINT
BY COL4 NOPRINT
BY COL2 NOPRINT
ON COL4 SUBTOTAL AS 'SUB-TOTAL YEAR'
ON COL3 SUBTOTAL AS ''
ON TABLE PCHOLD FORMAT HTML
END


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Virtuoso
posted Hide Post
The mock-up code you provided does not seem consistent with the output you're getting (COL2 should be printed to the left of COL1 for example).

Could you work an example with an IBI-provided table of file anyone here can use? The CAR table comes to mind. That way we can be better equipped to help.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Gold member
posted Hide Post
quote:
The mock-up code you provided does not seem consistent with the output you're getting (COL2 should be printed to the left of COL1 for example).


you are right, I have just modified my original posting now.
Can you help from there


In Focus since 2008
WebFOCUS 8.2.0.1
Windows 7 - IE,Chrome,Firefox
Excel, PDF, HTML, AHTML, XML
JavaScript, jQuery, D3.js, Highcharts
 
Posts: 79 | Location: New York | Registered: February 04, 2010Report This Post
Virtuoso
posted Hide Post
Did you try any of the ideas that Ginny and Darin provided you with yet? Did SUBFOOT make any difference?



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Gold member
posted Hide Post
BYDISPLAY did work for me ..

I have to try the subfoot option now


In Focus since 2008
WebFOCUS 8.2.0.1
Windows 7 - IE,Chrome,Firefox
Excel, PDF, HTML, AHTML, XML
JavaScript, jQuery, D3.js, Highcharts
 
Posts: 79 | Location: New York | Registered: February 04, 2010Report This Post
Gold member
posted Hide Post
SUBFOOT is working for the style i need, but it is not calculating the sum feild


In Focus since 2008
WebFOCUS 8.2.0.1
Windows 7 - IE,Chrome,Firefox
Excel, PDF, HTML, AHTML, XML
JavaScript, jQuery, D3.js, Highcharts
 
Posts: 79 | Location: New York | Registered: February 04, 2010Report This Post
Virtuoso
posted Hide Post
Try <ST.COL1 in your SUBFOOT to get the aggregated value of COL1 for each instance of your inner BY field.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Gold member
posted Hide Post
for the code below

 ON COL4 SUBTOTAL AS 'SUB-TOTAL YEAR' 


I am getting the value in year field next to the subtotal title

like SUB-TOTAL YEAR 2001


I want to get that year next to it and i want the aggregation done on the sum fields but shown in their respective fields

Please look at the required o/p
  
COL3   COL4   COL2   COL1
----   ----   ----   ----
USD    2001   ABC    $50
USD    2001   XYZ    $30

USD  SUB-TOTAL       $80
     YEAR 2001

USD    2003   DEF    $20

USD  SUB-TOTAL       $20
     YEAR 2003





I am getting something like this now when i use this code
ON COL4 SUBFOOT "SUB-TOTAL YEAR <+0><ST.COL1"


COL3   COL4   COL2   COL1
----   ----   ----   ----
USD    2001   ABC    $50
USD    2001   XYZ    $30

SUB-TOTAL YEAR $80
     

USD    2003   DEF    $20

SUB-TOTAL YEAR $20
      
 


In Focus since 2008
WebFOCUS 8.2.0.1
Windows 7 - IE,Chrome,Firefox
Excel, PDF, HTML, AHTML, XML
JavaScript, jQuery, D3.js, Highcharts
 
Posts: 79 | Location: New York | Registered: February 04, 2010Report This Post
Virtuoso
posted Hide Post
For an HTML or EXL2K report try something like:
TABLE FILE blah
SUM ..
COMPUTE YEAR_MSG/A10 = 'YEAR ' || EDIT(COL4); NOPRINT
BY ...
ON COL4 SUBFOOT 
"<COL3 SUB-TOTAL <ST.COL1"
" <+0> <YEAR_MSG"
..
ON TABLE SET STYLE *
TYPE=SUBFOOT, BY=COL4, HEADALIGN=BODY, $
TYPE=SUBFOOT, BY=COL4, LINE=1, OBJECT=TEXT, ITEM=1, COLSPAN=2, $
...


I'm working on air here since you haven't provided a workable code to play with but that should give you something to make some progress.

For PDF, HEADALIGN and COLSPAN are not applicable and therefore you'll need to resort to using POSITION to place each element. Please check the documentation about those and other style sheet keywords in the Creating Report with WebFOCUS Language manual.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Gold member
posted Hide Post
I get this error

quote:
(FOC096) WARNING. NO TEXT SUPPLIED BELOW SUBHEAD OR SUBFOOT
0 ERROR AT OR NEAR LINE 204 IN PROCEDURE ADHOCRQ FOCEXEC *


In Focus since 2008
WebFOCUS 8.2.0.1
Windows 7 - IE,Chrome,Firefox
Excel, PDF, HTML, AHTML, XML
JavaScript, jQuery, D3.js, Highcharts
 
Posts: 79 | Location: New York | Registered: February 04, 2010Report This Post
Virtuoso
posted Hide Post
ON COL4 SUBFOOT "SUB-TOTAL YEAR <+0>
Do not put that all on one line.

make it this

ON COL4 SUBFOOT
 "SUB-TOTAL YEAR <+0><ST.COL1"  
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Virtuoso
posted Hide Post
quote:
WARNING. NO TEXT SUPPLIED BELOW SUBHEAD OR SUBFOOT

As the error message clearly says, the text that forms the SUBFOOT or SUBHEAD was not found below the "ON .. SUBFOOT" keyword where it's supposed to go. As Prarie indicated, you put the text right after the SUBFOOT keyword, even though I had provided you with an example as to how to create your SUBFOOT but it seems that the message was not reached as expected.

Anyway, I would still advise you to go over the "Creating Reports with WebFOCUS Language" manual. It will help you get a stronger foundation in WebFOCUS development which will be a must as soon as you get involved in more complex assignments.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 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] Printing the BY field

Copyright © 1996-2020 Information Builders