Focal Point
[Closed] Printing the BY field

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/337101154

May 26, 2010, 10:58 AM
nsk110483
[Closed] Printing the BY field
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
May 26, 2010, 11:07 AM
njsden
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.
May 26, 2010, 11:15 AM
GinnyJakes
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
May 26, 2010, 11:18 AM
Darin Lee
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
May 26, 2010, 11:18 AM
njsden
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.
May 26, 2010, 11:28 AM
nsk110483
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
May 26, 2010, 11:51 AM
njsden
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.
May 26, 2010, 12:08 PM
nsk110483
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
May 26, 2010, 12:21 PM
nsk110483
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
May 26, 2010, 12:23 PM
njsden
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.
May 26, 2010, 12:40 PM
nsk110483
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
May 26, 2010, 01:07 PM
njsden
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.
May 27, 2010, 05:33 PM
nsk110483
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
May 27, 2010, 05:46 PM
Prarie
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"  

May 28, 2010, 11:08 AM
njsden
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.