[CLOSED] How Can We Align the SUBTOTAL Text to a Field
Here's the code which produces the following results. What I get and what I want is displayed in the output samples below the code (note the positioning of the "Sub Total:" text):
TABLE FILE CAR
SUM DCOST AS 'Dealer'
RCOST AS 'Retail'
BY COUNTRY
BY CAR
BY MODEL
ON COUNTRY SUBTOTAL DCOST RCOST AS 'Sub Total: '
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
UNITS=IN, PAGESIZE='LETTER', SQUEEZE=ON, ORIENTATION=LANDSCAPE,$
TYPE=REPORT, FONT='VERDANA', SIZE=8,$
TYPE=TITLE, STYLE=BOLD,$
TYPE=SUBTOTAL, BACKCOLOR=RGB(234 234 234),$
ENDSTYLE
END
What I GET with the above code is:
COUNTRY CAR MODEL Dealer Retail
ENGLAND JAGUAR V12XKE AUTO 7,427 8,878
XJ12L AUTO 11,194 13,491
JENSEN INTERCEPTOR III 14,940 17,850
TRIUMPH TR7 4,292 5,100
Sub Total: ENGLAND 37,853 45,319
FRANCE PEUGEOT 504 4 DOOR 4,631 5,610
Sub Total: FRANCE 4,631 5,610 ...and so on...
What I WANT with the above code is:
COUNTRY CAR MODEL Dealer Retail
ENGLAND JAGUAR V12XKE AUTO 7,427 8,878
XJ12L AUTO 11,194 13,491
JENSEN INTERCEPTOR III 14,940 17,850
TRIUMPH TR7 4,292 5,100
Sub Total: ENGLAND 37,853 45,319
FRANCE PEUGEOT 504 4 DOOR 4,631 5,610
Sub Total: FRANCE 4,631 5,610 ...and so on...
This message has been edited. Last edited by: Doug,
In FOCUS Since 1983 ~ from FOCUS to WebFOCUS. Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
April 07, 2010, 05:29 PM
njsden
I don't think you can get that type of output with a SUBTOTAL. To achieve what you need and to have more control over your output you may prefer to use SUBFOOT instead.
SUBFOOT usually requires more details in your code as nothing happens automatically and you have to resort to spotmarkers in order to properly position each "total" value below its corresponding detail column, particularly when using PDF output.
I am sure there are a few good examples in this forum regarding the use of SUBFOOT and spotmarkers to properly apply style sheet definition to it.
TABLE FILE CAR
SUM DCOST AS 'Dealer'
RCOST AS 'Retail'
BY COUNTRY
BY CAR
BY MODEL
ON COUNTRY SUBTOTAL DCOST RCOST AS 'Sub Total: '
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
UNITS=IN, PAGESIZE='LETTER', SQUEEZE=ON, ORIENTATION=LANDSCAPE,$
TYPE=REPORT, FONT='VERDANA', SIZE=8,$
TYPE=TITLE, STYLE=BOLD,$
TYPE=SUBTOTAL, BACKCOLOR=RGB(234 234 234), JUSTIFY=RIGHT,$
ENDSTYLE
END
-EXIT
The following sample uses a SUBTOTAL rendering the output as you'd generally get as well as a SUBFOOT which attempts to produce what you need.
To keep it simple, I make use of HEADLIGN=BODY which makes each element within the SUBFOOT align naturally under each table column in the report body but this works only in HTML/EXL2K. PDF will require a slightly different approach for which you can find some examples in the forum.
TABLE FILE CAR
SUM DCOST AS 'Dealer'
RCOST AS 'Retail'
COMPUTE SUBT_LBL/A50 = 'Sub Total: ' | COUNTRY; NOPRINT
BY COUNTRY
BY CAR
BY MODEL
ON COUNTRY SUBTOTAL DCOST RCOST AS 'Sub Total: '
ON COUNTRY SUBFOOT
" <0+> <+0><SUBT_LBL<ST.DCOST<ST.RCOST"
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
UNITS=IN, PAGESIZE='LETTER', SQUEEZE=ON, ORIENTATION=LANDSCAPE,$
TYPE=REPORT, FONT='VERDANA', SIZE=8,$
TYPE=TITLE, STYLE=BOLD,$
TYPE=SUBTOTAL, BACKCOLOR=RGB(234 234 234),$
TYPE=SUBFOOT, BY=COUNTRY, HEADALIGN=BODY, BACKCOLOR='SILVER', JUSTIFY=RIGHT, $
TYPE=SUBFOOT, BY=COUNTRY, OBJECT=FIELD, ITEM=1, JUSTIFY=LEFT, $
ENDSTYLE
END
Tom Flynn: How's the air in Michigan??? A little fun!!!
AAARRRERrrrgggg... Love The Fun
April 07, 2010, 05:47 PM
Doug
quote:
Originally posted by njsden: ... SUBTOTAL rendering the output as you'd generally get as well as a SUBFOOT ...
Not a real option at this point... Been There... Done That... I like your solution... But, this is a mere sample / POC for a much larger "Structured Adhoc" report via a complex launch page...
April 07, 2010, 05:50 PM
Tom Flynn
Doug,
9 times out of 10 I use the RECAP, SUBFOOT and HEADALIGN process, just as Neftali suggests. I'm sure your POC is more complex then CAR. But, if it is a GUI POC, I think you can still do what Neftali suggests via the GUI.
Doug, in the GUI, highlight your subfoot, then activitate the alignment grid via right click. You can do what you want there. However, to run it in either pdf or html, your output will vary. This has been fixed in 7.6.11 so that everything lines up regardless of output selected.
Kathy Phillips Web FOCUS 8.2.05.14, 8.1.05, 8.08, 8.0.7, 8.0.5,8.0.2m, 7.6.10,7.7.03 Windows
April 08, 2010, 10:41 AM
Francis Mariani
quote:
This has been fixed in 7.6.11 so that everything lines up regardless of output selected
- It's a miracle!
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
April 08, 2010, 10:54 AM
Doug
Thanks Tom and Neftali,
I've been told that 7.6.11 (or 7.7) has this ability (kathy: ... It's been fixed... / Francis: A Miracle)
I can do as suggested for now and wait for more...
April 08, 2010, 10:57 AM
Tom Flynn
Hey Doug,
Yep, BUT, JUSTIFY=RGHT for the SUBTOTAL did do what you wanted in your example...
I hear you loud and clear... I'm Not a GUI person either... But, the GUI can do some stuff... The JUSTIFY=RIGHT will work for now... Thanks...
April 08, 2010, 12:44 PM
Doug
OK, Tom, Now I remember why the JUSTIFY=RIGHT won't work... It's because the placement of the "Sub Total:" line varies based on the field name used in the "ON [fieldname] SUBTOTAL". So, sometimes it's justified to the MODEL, and sometimes justified to the CAR or COUNTRY columns...
April 08, 2010, 01:31 PM
Tom Flynn
OK, Doug, I'm sure there are other caveat's, although, this works in 7.6.10 environment. Don't know which one you're on since your signature keeps changing!
TABLE FILE CAR
SUM DCOST AS 'Dealer'
RCOST AS 'Retail'
BY COUNTRY
BY CAR SUB-TOTAL AS 'Sub Total: '
BY MODEL
-*ON COUNTRY SUBTOTAL DCOST RCOST AS 'Sub Total: '
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
UNITS=IN, PAGESIZE='LETTER', SQUEEZE=ON, ORIENTATION=LANDSCAPE,$
TYPE=REPORT, FONT='VERDANA', SIZE=8,$
TYPE=TITLE, STYLE=BOLD,$
TYPE=SUBTOTAL, BACKCOLOR=RGB(234 234 234), JUSTIFY=RIGHT,$
TYPE=GRANDTOTAL, BACKCOLOR='LIGHT BLUE', JUSTIFY=RIGHT,$
ENDSTYLE
END
-EXIT
That would give him a subtotal line for each of the BY fields above, so he would get a subtotal on CAR as well as a subtotal on COUNTRY.
My understanding is that he needs a SUBTOTAL on COUNTRY but it should be displayed under the MODEL column, but with the "Sub-Total:" label being LEFT-justified at all times.
I'm not sure that can be accomplished with a regular SUBTOTAL a/o SUB-TOTAL and that's why I suggested the use of SUBFOOT which in my experience is the only one that provides such a flexibility although you won't get BORDERS in between each item in the SUBFOOT or at least not in PDF. Well, there are always "cons" with each approach
Now I remember why the JUSTIFY=RIGHT won't work... It's because the placement of the "Sub Total:" line varies based on the field name used in the "ON [fieldname] SUBTOTAL". So, sometimes it's justified to the MODEL, and sometimes justified to the CAR or COUNTRY columns...
Isn't that what he said were the caveat's. I also said 9 times out of 10, I use RECAP, SUBFOOT and HEADALIGN.
"BASED on THE EXAMPLE", the code works, and, it displays "exactly" as he described in his 1st thread. Try running the code...
Yep, Tom, that's right... thanks for keeping this in perspective...
April 11, 2010, 06:39 PM
Dan Satchell
This is the sledge-hammer approach, but you can use the non-breaking space character to push the SUBTOTAL text to the right.
-SET &XTAB = '&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;' ||
- '&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;' ||
- '&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;';
-*
TABLE FILE CAR
SUM DCOST AS 'Dealer'
RCOST AS 'Retail'
BY COUNTRY
BY CAR
BY MODEL
ON COUNTRY SUBTOTAL DCOST RCOST AS '&XTAB Sub Total: '
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
UNITS=IN, PAGESIZE='LETTER', SQUEEZE=ON, ORIENTATION=LANDSCAPE,$
TYPE=REPORT, FONT='VERDANA', SIZE=8,$
TYPE=TITLE, STYLE=BOLD,$
TYPE=SUBTOTAL, BACKCOLOR=RGB(234 234 234), $
ENDSTYLE
END