Okay, so as not to reopen again the issue of subtotals appearing on two lines, I thought I would try the

TYPE=SUBFOOT, HEADALIGN=BODY, JUSTIFY=RIGHT, $ 

trick.

After a lot of dicking around with <+0> to make the columns appear in the right place I have a semblance of what I want:

http://i49.tinypic.com/2zqarn9.jpg

However, now all the columns are right-justified and I want the leftmost datafields to be left-justified and read:

TOTAL PRHA
TOTAL CENTRAL AMERICA

not

TOTAL              PRHA
TOTAL   CENTRAL AMERICA

Suggestions (RTFM ??) are welcome ...

This message has been edited. Last edited by: FP Mod Chuck,

George,

quote:

Okay, so as not to reopen again the issue of subtotals appearing on two lines, I thought I would try the

TYPE=SUBFOOT, HEADALIGN=BODY, JUSTIFY=RIGHT, $
trick.

Very good!
Now all you need is

  
TYPE=SUBFOOT, ITEM=(number}, JUSTIFY=LEFT, $ 

for all fields/text you want left-justified.

Try using this:

TYPE=SUBFOOT, HEADALIGN=BODY, JUSTIFY=RIGHT, $
TYPE=SUBFOOT, LINE=1, ITEM=2, JUSTIFY=LEFT, $

Hope this helps.

Edit: Waaa. Danny beat me to it.

Brilliant! - almost there....



Is there a way to make the gap between TOTAL and PRHA smaller? .... I'm going to have to do this for over 150 reports methinks...

Your turn, Anatess

A limitation (?) of the SUBFOOT technique - it doesn't appear to work with multiple ACROSS columns.

Here I have used the regular SUBTOTAL followed by SUBFOOT and HEADALIGN=BODY in the styling.

For the SUBFOOT multiple years I inserted

<ST.FIELD1<ST.FIELD2<ST.FIELD3

repeated as many times as years would fit across the page. However, as you can see, ALL the subtotals are from the final ACROSS year (2012) and are transposed as incorrect values to the previous years:

This message has been edited. Last edited by: George Patton,

quote:

Is there a way to make the gap between TOTAL and PRHA smaller?

Consider: "LEFTGAP=#.#" ... "RIGHTGAP=#.#" ... etc...

Thanks, I'll give it a shot.

quote:

Originally posted by George Patton:
A limitation (?) of the SUBFOOT technique - it doesn't appear to work with multiple ACROSS columns.

Here I have used the regular SUBTOTAL followed by SUBFOOT and HEADALIGN=BODY in the styling.

For the SUBFOOT multiple years I inserted

<ST.FIELD1<ST.FIELD2<ST.FIELD3

repeated as many times as years would fit across the page. However, as you can see, ALL the subtotals are from the final ACROSS year (2012) and are transposed as incorrect values to the previous years

I know you can enumerate ROWTOTAL columns to match different ACROSS columns, perhaps you can do the same here:

<ST.FIELD1(1)<ST.FIELD2(1)<ST.FIELD3(1)<ST.FIELD1(2)<ST.FIELD2(2)<ST.FIELD3(2)...etc.

Tried it already.... WebFOCUS complained bitterly

George,

When confronted with this type of problem, I usually try our good friend McGuyver.

  
-* File georgeP01.fex
JOIN BLANK WITH SALES IN CAR TO BLANK IN FSEQ AS M_
DEFINE FILE CAR
BLANK/A1 WITH SALES = ' ';
XBODY/A12=IF COUNTER EQ 1 THEN BODYTYPE ELSE 'TOTAL';
END

TABLE FILE CAR
SUM 
     CAR.BODY.DEALER_COST AS 'DCOST'
     CAR.BODY.RETAIL_COST AS 'RCOST'
     CAR.BODY.SALES
BY  LOWEST CAR.ORIGIN.COUNTRY
BY COUNTER NOPRINT
BY  LOWEST XBODY
ACROSS LOWEST CAR.BODY.SEATS
ACROSS LOWEST CAR.SPECS.MPG IN-GROUPS-OF 10
IF COUNTER LE 2
WHERE MPG GE 10;
ON COUNTRY SKIP-LINE
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
END

George,

What is the format of your CUST column?

I've thrown a quick mock-up of your data and I do not get the "two line" subtotal problem. Perhaps I'm not doing the same as yours?

FWIW, this is the code that I am using. Obviously the data columns will be different etc. but if gives me a chance of trying things out.

EX -LINES 6 EDAPUT MASTER,GEODATA,CV,MEM,FILENAME=GEODATA, SUFFIX=FOC,$
FIELDNAME=CUST , ,A4 ,A4 ,$
FIELDNAME=YEAR , ,A4YY ,A4 ,$
FIELDNAME=FOB , ,D12.2 ,D12.2 ,$
FIELDNAME=KG , ,I9 ,I9 ,$
FIELDNAME=UN , ,I9 ,I9 ,$
-RUN
CREATE FILE GEODATA
MODIFY FILE GEODATA
FREEFORM CUST YEAR FOB KG UN
DATA
BABE,2009,689538.93,294228,16013,$
BABE,2010,385511.27,94841,17077,$
BABE,2011,493086.46,121021,25702,$
BABE,2012,200308.56,43298,8208,$
BACM,2009,246.25,125,0,$
BACM,2010,666.25,325,0,$
BACM,2011,6185.72,18519,0,$
BACM,2012,562.50,250,0,$
BAHA,2009,837902.10,540330,0,$
BAHA,2010,458235.63,252016,0,$
BAHA,2011,395499.40,338198,1,$
BAHA,2012,69732.56,40406,-1,$
BATR,2009,668858.32,547422,2144,$
BATR,2010,694805.69,403593,4958,$
BATR,2011,641214.94,393109,215,$
BATR,2012,268413.91,161076,3296,$
END
DEFINE FILE GEODATA
  CUSTPREF/A2 = EDIT(CUST,'99$');
END
TABLE FILE GEODATA
   SUM FOB
       KG
	     UN
    BY CUSTPREF NOPRINT
	  BY CUST
ACROSS YEAR     AS ''
ON CUSTPREF SUB-TOTAL AS 'TOTAL'
-*ON CUSTPREF SUBFOOT
-*"TOTAL<ST.FOB<ST.KG<ST.UN<ST.FOB<ST.KG<ST.UN<ST.FOB<ST.KG<ST.UN<ST.FOB<ST.KG<ST.UN"
HEADING CENTER
"<CUSTPREF"
ON TABLE SET HTMLCSS ON
ON TABLE SET LINES 666666
ON TABLE SET PAGE NOLEAD
ON TABLE NOTOTAL
ON TABLE SET STYLE *
  GRID=OFF, FONT="TAHOMA", SIZE=8, SQUEEZE=ON, $
  TYPE=HEADING, BORDER=LIGHT, BACKCOLOR=RGB(000 128 255), COLOR=WHITE, $
  TYPE=TITLE, BORDER=LIGHT, BACKCOLOR=RGB(000 128 255), COLOR=WHITE, JUSTIFY=CENTER, $
  TYPE=ACROSS, BORDER=LIGHT, BACKCOLOR=RGB(000 128 255), COLOR=WHITE, $
  TYPE=SUBTOTAL, BACKCOLOR=RGB(180 180 180), COLOR=BLACK, $
  TYPE=SUBFOOT, HEADALIGN=BODY, BACKCOLOR=RGB(000 128 255), COLOR=WHITE, $
ENDSTYLE
END
-RUN

T

This message has been edited. Last edited by: Tony A,

Interestingly, if I add the CUST data column to the subtotal line, it just give TOTAL without the CUST value!

ON CUSTPREF SUB-TOTAL AS 'TOTAL <CUST'

T

Hi Tony,

You've done an awful lot of work reproducing almost exactly what I have, and I'm grateful for the effort.

Although all the customer codes I show happen to be 4 characters, the field is actually A6 ..

The 2-letter country code is indeed the first two characters of the customer.

KG and UN are actually P9, but that won't make a difference. The YEAR is a DEFINed field YEAR/YY=BLDATE;

The only real difference that I can see beyond the length of the CUST field is that I'm doing a PCHOLD FORMAT PDF.

If I use your code with SUBTOTAL and holding PDF I get:


If I use

ON CUSTPREF SUBFOOT
"TOTAL<ST.FOB<ST.KG<ST.UN<ST.FOB<ST.KG<ST.UN<ST.FOB<ST.KG<ST.UN<ST.FOB<ST.KG<ST.UN"

I get:

As you can see, the totals are wrong.

I'll have a go at MacGyver

Hi George,

PDF is the key, if I output to PDF then I see what you are getting in the subtotal, other formats appear to be OK.

I am sure that Noreen went to great pains to explain all of this behaviour quite a while ago in some publication / summit presentation. If someone has that reference then it may help you out. Failing that at least you'll have a self contained repro if you decide to get the tech heads on support involved!

Good luck

T

Hi George,

The thinking hat helped on this, so try the following. The worst bit would be a variable number of Years in your across but sizing might limit that to 4 anyway (given your data and letter / A4 sized output?).

EX -LINES 6 EDAPUT MASTER,GEODATA,CV,MEM,FILENAME=GEODATA, SUFFIX=FOC,$
FIELDNAME=CUST , ,A6 ,A6 ,$
FIELDNAME=YEAR , ,A4YY ,A4 ,$
FIELDNAME=FOB , ,D12.2 ,D12.2 ,$
FIELDNAME=KG , ,P9 ,P9 ,$
FIELDNAME=UN , ,P9 ,P9 ,$
-RUN
CREATE FILE GEODATA
MODIFY FILE GEODATA
FREEFORM CUST YEAR FOB KG UN
DATA
BABE            ,2009,689538.93,294228,16013,$
BABE            ,2010,385511.27,94841,17077,$
BABE            ,2011,493086.46,121021,25702,$
BABE            ,2012,200308.56,43298,8208,$
BACM            ,2009,246.25,125,0,$
BACM            ,2010,666.25,325,0,$
BACM            ,2011,6185.72,18519,0,$
BACM            ,2012,562.50,250,0,$
BAHA            ,2009,837902.10,540330,0,$
BAHA            ,2010,458235.63,252016,0,$
BAHA            ,2011,395499.40,338198,1,$
BAHA            ,2012,69732.56,40406,-1,$
BATR            ,2009,668858.32,547422,2144,$
BATR            ,2010,694805.69,403593,4958,$
BATR            ,2011,641214.94,393109,215,$
BATR            ,2012,268413.91,161076,3296,$
CABE            ,2009,689538.93,294228,16013,$
CABE            ,2010,385511.27,94841,17077,$
CABE            ,2011,493086.46,121021,25702,$
CABE            ,2012,200308.56,43298,8208,$
CACM            ,2009,246.25,125,0,$
CACM            ,2010,666.25,325,0,$
CACM            ,2011,6185.72,18519,0,$
CACM            ,2012,562.50,250,0,$
DAHA            ,2009,837902.10,540330,0,$
DAHA            ,2010,458235.63,252016,0,$
DAHA            ,2011,395499.40,338198,1,$
DAHA            ,2012,69732.56,40406,-1,$
DATR            ,2009,668858.32,547422,2144,$
DATR            ,2010,694805.69,403593,4958,$
DATR            ,2011,641214.94,393109,215,$
DATR            ,2012,268413.91,161076,3296,$
END
DEFINE FILE GEODATA
  COUNTRY/A2 WITH CUST = EDIT(CUST,'99$');
END
TABLE FILE GEODATA
   SUM FOB      AS STFOB
       KG       AS STKG
       UN       AS STUN
    BY COUNTRY
ACROSS YEAR     AS ''
ON TABLE HOLD AS SUBTOTS FORMAT FOCUS INDEX COUNTRY
END
-RUN
JOIN CLEAR *
JOIN COUNTRY WITH CUST IN GEODATA TO COUNTRY IN SUBTOTS TAG T1 AS J1
DEFINE FILE GEODATA
  COUNTRY/A2 WITH CUST = EDIT(CUST,'99$');
END
TABLE FILE GEODATA
   SUM FOB      AS 'FOB'
       KG       AS 'QTY,(KG)'
       UN       AS 'QTY,(UN)'
    BY COUNTRY NOPRINT
    BY CUST     AS 'CUST          '
ACROSS YEAR     AS ''
-* ON COUNTRY SUB-TOTAL AS 'TOTAL'
ON COUNTRY SUBFOOT
"TOTAL<T1.E02<T1.E03<T1.E04<T1.E05<T1.E06<T1.E07<T1.E08<T1.E09<T1.E10<T1.E11<T1.E12<T1.E13"
HEADING CENTER
"<COUNTRY"
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET LINES 666666
ON TABLE SET PAGE NOLEAD
ON TABLE NOTOTAL
ON TABLE SET STYLE *
  GRID=OFF, FONT="ARIAL", SIZE=8, SQUEEZE=ON, ORIENTATION=LANDSCAPE, LEFTGAP=0.12, RIGHTGAP=0.12, $
  TYPE=HEADING, BORDER=LIGHT, BACKCOLOR=RGB(000 128 255), COLOR=WHITE, STYLE=+BOLD, SIZE=12, $
  TYPE=TITLE, BORDER=LIGHT, BACKCOLOR=RGB(000 128 255), COLOR=WHITE, JUSTIFY=CENTER, STYLE=+BOLD, $
  TYPE=ACROSS, BORDER=LIGHT, BACKCOLOR=RGB(000 128 255), COLOR=WHITE, STYLE=+BOLD, $
  TYPE=SUBTOTAL, BACKCOLOR=RGB(180 180 180), COLOR=BLACK, STYLE=+BOLD, $
  TYPE=SUBFOOT, HEADALIGN=BODY, BORDER=LIGHT, BACKCOLOR=RGB(000 128 255), COLOR=WHITE, STYLE=+BOLD, $
  TYPE=DATA, COLUMN=CUST, JUSTIFY=LEFT, STYLE=+BOLD, $
ENDSTYLE
END
-RUN

T

This message has been edited. Last edited by: Tony A,

Tony,

Very ingenious. However, how do you know that there will be 13 fields in the created focus file?

Doesn't McGuyver provide just that? You create dynamically the subtotal line and make it appear under the report lines.

quote:

how do you know that there will be 13 fields in the created focus file?

Danny,

That is why I made the following comment

quote:

The worst bit would be a variable number of Years in your across

I know that you know how I knew, but for others, I just added CMD TYPE SUBTOTS.MAS and -EXIT.

The other reason for doing it this way is that by joining on each COUNTRY, we get a matching output for the subtotals.

T

Wow, thanks for all the input on this. I hope it will be useful to others as well. HEADALIGN=BODY does restrict ACROSS fields to what will fit on one page, so in this case about 3 or 4 years. I have to leave on a business trip tomorrow, but I may get a chance to play around with it while I'm away. My real fear is that it will work too darn well - and I'll have around 100 reports to modify with this improved technique.....

Maybe you've already tried this, but making your CUST field wide enough may eliminate the two-line SUBTOTAL in PDF format. At least it worked for me using Tony's sample code with PDF output. I added this to the StyleSheet:

TYPE=REPORT, COLUMN=CUST, WRAP=0.5, $

This message has been edited. Last edited by: Dan Satchell,

quote:

Originally posted by Danny-SRL:
Very ingenious. However, how do you know that there will be 13 fields in the created focus file?

I used the technique that involved first loading a variable with the number of expected ACROSS fields. (Done by means of a TABLE FILE and then using &LINES.) Then I just did this:

  
	-IF &NBR_OF_ACROSS_COLUMNS_NOT_COUNTING_TOTALS NE 1 GOTO NOT1GT;
	"<DUMMY_1<GRANDTA<HEAD2<R9.E03<R9.E12"
	"<DUMMY_1<DUMMY_2<HEAD3<R9.E05<R9.E14"
	"<DUMMY_1<DUMMY_2<HEAD4<R9.E09<R9.E18"
	-NOT1GT
	-IF &NBR_OF_ACROSS_COLUMNS_NOT_COUNTING_TOTALS NE 2 GOTO NOT2GT;
	"<DUMMY_1<GRANDTA<HEAD2<R9.E03<R9.E12<R9.E21"
	"<DUMMY_1<DUMMY_2<HEAD3<R9.E05<R9.E14<R9.E23"
	"<DUMMY_1<DUMMY_2<HEAD4<R9.E09<R9.E18<R9.E27"
	-NOT2GT
	-IF &NBR_OF_ACROSS_COLUMNS_NOT_COUNTING_TOTALS NE 3 GOTO NOT3GT;
.
. and so forth, as needed.
.