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,

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> to properly style each element.

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.

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

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.

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

Did you try any of the ideas that Ginny and Darin provided you with yet? Did SUBFOOT make any difference?

BYDISPLAY did work for me ..

I have to try the subfoot option now

SUBFOOT is working for the style i need, but it is not calculating the sum feild

Try <ST.COL1 in your SUBFOOT to get the aggregated value of COL1 for each instance of your inner BY field.

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
      
 

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.

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 *

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"  

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.