In the code below, is there some formatting that will remove/reduce the space between the last row for a country and the line with the subtotal?
SET PAGE=NOLEAD
TABLE FILE CAR
PRINT
DEALER_COST AS DCOST
RETAIL_COST AS RCOST
BY COUNTRY
BY CAR
ON COUNTRY SUBTOTAL DCOST RCOST AS 'TOTAL'
HEADING
"SUMMARY"
FOOTING BOTTOM
"Run on: <+0>&DATEtrMDYY"
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
FONT='TIMES NEW ROMAN',
SIZE=8,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
TOPMARGIN=0.0,
BOTTOMGAP=0.0,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=8,
STYLE=BOLD+UNDERLINE,
$
TYPE=FOOTING,
SIZE=8,
STYLE=NORMAL,
$
TYPE=SUBTOTAL,
BY=1,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=DCOST,
BORDER-TOP=LIGHT,
$
TYPE=SUBTOTAL,
BY=1,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BY=1,
COLUMN=RCOST,
BORDER-TOP=LIGHT,
$
ENDSTYLE
END
-Bethany
WF 7.1.1 DevStudio 7.1.4This message has been edited. Last edited by: Kerry,
Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
March 15, 2007, 04:28 PM
Alan B
I'm not sure how to remove the gap using subtotal, but using subfoot, it seems to look better(?).
SET PAGE=NOLEAD
TABLE FILE CAR
PRINT
DEALER_COST AS DCOST
RETAIL_COST AS RCOST
BY COUNTRY
BY CAR
ON COUNTRY SUBFOOT
"Total <ST.DCOST<ST.RCOST"
HEADING
"SUMMARY"
FOOTING BOTTOM
"Run on: <+0>&DATEtrMDYY"
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
GRID=OFF,
$
TYPE=REPORT,
FONT='TIMES NEW ROMAN',
SIZE=8,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
TOPMARGIN=0.0,
BOTTOMGAP=0.05,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=8,
STYLE=BOLD+UNDERLINE,
$
TYPE=FOOTING,
SIZE=8,
STYLE=NORMAL,
$
TYPE=SUBFOOT,
BY=1,
LINE=1,
ITEM=2,
POSITION=DCOST,
$
TYPE=SUBFOOT,
BY=1,
LINE=1,
ITEM=3,
POSITION=RCOST,
$
TYPE=SUBFOOT,
BY=1,
STYLE=BOLD,
BORDER-TOP=LIGHT,
$
ENDSTYLE
END
See how that works for you, though I've had to change bottomgap as well.
Alan. WF 7.705/8.007
March 15, 2007, 05:10 PM
Bethany
Thanks for the subfoot suggestion, but how can you then put the light top border only above the DCOST and RCOST totals rather than across the entire line?
Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
SET PAGE=NOLEAD
TABLE FILE CAR
PRINT
DEALER_COST AS DCOST
RETAIL_COST AS RCOST
BY COUNTRY
BY CAR
ON COUNTRY SUBTOTAL DCOST RCOST AS 'TOTAL'
HEADING
"SUMMARY"
FOOTING BOTTOM
"Run on: <+0>&DATEtrMDYY"
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
FONT='TIMES NEW ROMAN',
SIZE=8,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
TOPMARGIN=0.0,
BOTTOMGAP=0.05,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=8,
STYLE=BOLD+UNDERLINE,
$
TYPE=FOOTING,
SIZE=8,
STYLE=NORMAL,
$
TYPE=SUBTOTAL,
BY=1,
STYLE=BOLD,
$
TYPE=SUBTOTAL, BY=1, BORDER=LIGHT, BORDER-COLOR='WHITE', $
TYPE=SUBTOTAL, BY=1, COLUMN=DCOST,
BORDER-TOP=LIGHT, BORDER-BOTTOM=OFF, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, BORDER-COLOR='BLACK', $
TYPE=SUBTOTAL, BY=1, COLUMN=RCOST,
BORDER-TOP=LIGHT, BORDER-BOTTOM=OFF, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, BORDER-COLOR='BLACK', $
ENDSTYLE
END
A few other comments... (I realize that some of the code might have been automatically generated by Dev Studio Report Painter)
HTMLCSS is only for HTML reports.
PAGE and PAGE-NUM are synonyms: in your sample program, SET PAGE=NOLEAD is overridden by ON TABLE SET PAGE-NUM OFF.
It appears that the BOTTOMGAP=0.0 styling for REPORT is ignored when BORDER is used - comment the BORDER-TOP styling to see what I mean.
It also looks like BORDER has other effects - using it indents the data lines from other lines (HEADING, SUBTOTAL, etc) - an inherent left gap perhaps, which can be controlled by TYPE=REPORT, LEFTGAP=0.
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
March 15, 2007, 06:19 PM
Alan B
Francis
I was just about to post, and you beat me.
One question,
quote:
It appears that the BOTTOMGAP=0.0 styling for REPORT is ignored when BORDER is used - comment the BORDER-TOP styling to see what I mean.
I'm not sure what this means. The BOTTOMGAP=0.05 stops the border on the subfoot cutting in to the previous row. Or am I missing something. Thanks
Alan. WF 7.705/8.007
March 17, 2007, 07:51 PM
FrankDutch
This was just what I needed.
I'm going to love this site more and more.
Frank
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
March 19, 2007, 09:45 AM
Francis Mariani
quote:
It appears that the BOTTOMGAP=0.0 styling for REPORT is ignored when BORDER is used - comment the BORDER-TOP styling to see what I mean.
Well, ignore that comment for now. While I was playing around with the borders, I found that setting borders seems to impact the gap settings - they were being ignored. I can't reproduce the situation in a hurry, so just fugeddaboutit.
Cheers,
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
March 23, 2007, 02:02 PM
Bethany
Francis,
Thank you again. I hadn't realized that the gap between the data and subtotal could also be fixed with the borders on the subtotal. Works like a charm!
Bethany
Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
September 18, 2007, 10:53 AM
Bethany
Francis,
I hope you'll be able to help me solve this one. With the code below, I need to have the Sales column all the way on the right, but when I use SEQUENCE in the style sheet to do that, I get the dreaded space between the detail and the subtotals.
Any ideas???
SET PAGE=NOLEAD
TABLE FILE CAR
SUM
SALES
BY COUNTRY
BY CAR
SUM
DEALER_COST AS DCOST
RETAIL_COST AS RCOST
COMPUTE COST_RATIO/D12.2 = RCOST/DCOST;
BY COUNTRY
BY CAR
ACROSS BODYTYPE
ON COUNTRY SUBHEAD
"<COUNTRY"
ON COUNTRY RECOMPUTE SALES DCOST RCOST COST_RATIO AS 'TOTAL'
WHERE BODYTYPE EQ 'COUPE' OR 'SEDAN';
HEADING
"SUMMARY"
FOOTING BOTTOM
"Run on: <+0>&DATEtrMDYY"
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
FONT='TIMES NEW ROMAN',
SIZE=8,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
TOPMARGIN=0.0,
BOTTOMGAP=0.05,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=8,
STYLE=BOLD+UNDERLINE,
$
TYPE=FOOTING,
SIZE=8,
STYLE=NORMAL,
$
TYPE=SUBTOTAL,
BY=1,
STYLE=BOLD,
$
TYPE=SUBTOTAL, BY=1, BORDER=LIGHT, BORDER-COLOR='WHITE', $
TYPE=SUBTOTAL, BY=1, ACROSSCOLUMN=DCOST,
BORDER-TOP=LIGHT, BORDER-BOTTOM=OFF, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, BORDER-COLOR='BLACK', $
TYPE=SUBTOTAL, BY=1, ACROSSCOLUMN=RCOST,
BORDER-TOP=LIGHT, BORDER-BOTTOM=OFF, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, BORDER-COLOR='BLACK', $
TYPE=SUBTOTAL, BY=1, ACROSSCOLUMN=COST_RATIO,
BORDER-TOP=LIGHT, BORDER-BOTTOM=OFF, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, BORDER-COLOR='BLACK', $
TYPE=SUBTOTAL, BY=1, COLUMN=SALES,
BORDER-TOP=LIGHT, BORDER-BOTTOM=HEAVY, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, BORDER-COLOR='BLACK', $
TYPE=REPORT,
COLUMN=SALES,
SEQUENCE=10,
$
ENDSTYLE
END
Thanks, Bethany
DevStudio 7.1.4 WebFOCUS 7.1.3
Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
September 18, 2007, 12:09 PM
Francis Mariani
Yikes! I don't know why the SEQUENCE clause would cause the subtotal line to split! Probably something to do with an internal NOPRINT column.
You know how much I hate the split subtotal! It should be banned. I'd give up 10 new features to have this resolved.
Bethany, I'm sorry, I don't think I can help here.
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
September 18, 2007, 01:28 PM
Bethany
Thanks for checking it out Francis....I guess we can still hope that it will get fixed someday....
Bethany
Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
September 18, 2007, 02:26 PM
mgrackin
Bethany,
Try this example:
SET PAGE=NOLEAD TABLE FILE CAR SUM SALES NOPRINT BY COUNTRY BY CAR SUM DEALER_COST AS DCOST RETAIL_COST AS RCOST COMPUTE COST_RATIO/D12.2 = RCOST/DCOST; BY COUNTRY BY CAR ACROSS BODYTYPE COMPUTE NEWSALES/D12=SALES; AS 'SALES' ON COUNTRY SUBHEAD "<COUNTRY" ON COUNTRY RECOMPUTE SALES DCOST RCOST COST_RATIO AS 'TOTAL' WHERE BODYTYPE EQ 'COUPE' OR 'SEDAN'; HEADING "SUMMARY" FOOTING BOTTOM "Run on: <+0>&DATEtrMDYY" ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT PDF ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT, $ TYPE=REPORT, FONT='TIMES NEW ROMAN', SIZE=8, COLOR='BLACK', BACKCOLOR='NONE', STYLE=NORMAL, TOPMARGIN=0.0, RIGHTMARGIN=0.0, BOTTOMGAP=0.05, $ TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=1, SIZE=8, STYLE=BOLD+UNDERLINE, $ TYPE=FOOTING, SIZE=8, STYLE=NORMAL, $ TYPE=SUBTOTAL, BY=1, STYLE=BOLD, $ TYPE=SUBTOTAL, BY=1, BORDER=LIGHT, BORDER-COLOR='WHITE', $ TYPE=SUBTOTAL, BY=1, ACROSSCOLUMN=DCOST, BORDER-TOP=LIGHT, BORDER-BOTTOM=OFF, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, BORDER-COLOR='BLACK', $ TYPE=SUBTOTAL, BY=1, ACROSSCOLUMN=RCOST, BORDER-TOP=LIGHT, BORDER-BOTTOM=OFF, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, BORDER-COLOR='BLACK', $ TYPE=SUBTOTAL, BY=1, ACROSSCOLUMN=COST_RATIO, BORDER-TOP=LIGHT, BORDER-BOTTOM=OFF, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, BORDER-COLOR='BLACK', $ TYPE=SUBTOTAL, BY=1, COLUMN=NEWSALES, BORDER-TOP=LIGHT, BORDER-BOTTOM=HEAVY, BORDER-LEFT=OFF, BORDER-RIGHT=OFF, BORDER-COLOR='BLACK', $ ENDSTYLE END
Thanks!
Mickey
FOCUS/WebFOCUS 1990 - 2011
September 18, 2007, 02:36 PM
Francis Mariani
Very nice Mickey!
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
September 18, 2007, 02:46 PM
mgrackin
Thanks Francis. I was just doing something recently for which I was thinking of using this technique but could not get it to work. Seeing Bethany's post made me take another swing at it and I found the issue I was having with my report.
This technique with a COMPUTE appearing AFTER the ACROSS phrase is documented on page 6-23 of the 7.1 'Creating Reports' manual. The problem I was having is that I was also using 'COLUMNS... AND' to control the sort order on the ACROSS just before the COMPUTE statement. It seems WF cannot deal with the word COMPUTE appearing after a 'COLUMNS...AND' statement.
Thanks!
Mickey
FOCUS/WebFOCUS 1990 - 2011
September 18, 2007, 03:19 PM
Bethany
Thanks!!! Getting rid of those blank lines saves so much space! One last question, the title of the new computed field NEWSALES, is displayed on the same line as the across value (BODYTYPE). Any way to display that on the same line as DCOST, RCOST COST_RATIO?
Thanks again!!!
Bethany
Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
September 18, 2007, 03:25 PM
Francis Mariani
OK, now you're kidding, right?!
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
September 18, 2007, 03:34 PM
mgrackin
Bethany, without going into a whole lot of talk about the internal matrix and how WF treats the NEWSALES computed column, the short answer is 'NO' unless you want to do a whole lot of work using SUBHEAD to redefine ALL your column titles. I know a bit about the internal matrix created on report requests so I understand why WF is putting the AS phrase value in that spot. It makes sense to me. It ain't pretty but I understand why it is doing that.
Sorry we cannot help with this one.
Thanks!
Mickey
FOCUS/WebFOCUS 1990 - 2011
September 18, 2007, 03:38 PM
mgrackin
Just for fun, try running the following and explain why the comma in the AS phrase does not cause a line break in the column title.
TABLE FILE CAR SUM SALES BY COUNTRY BY CAR SUM RCOST BY COUNTRY BY CAR ACROSS SEATS COMPUTE TOTSALES/D12=SALES; AS 'One,Two' END
Thanks!
Mickey
FOCUS/WebFOCUS 1990 - 2011
September 18, 2007, 03:54 PM
Bethany
I had tried putting the comma in to make a line break but of course that didn't work. I figured there wasn't an easy way around this one, so we'll live with what we've got.
Thanks again for all of your help, Bethany
Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
September 24, 2010, 09:41 AM
Wep5622
It still behaves this way in 2010, see below sample:
-* File subtotal-sequence-bug.fex
APP APPENDPATH IBISAMP
-RUN
TABLE FILE CAR
SUM SEATS
RPM
BY MODEL
BY SALES
ON SALES SUMMARIZE
SEATS
RPM
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
$
TYPE=REPORT, COLUMN=N1, SEQUENCE=1,$
ENDSTYLE
END
Comment the TYPE=REPORT style out and see that the blank lines between the totals are gone.
It doesn't seem to have to do with NOPRINT fields, and I didn't even actually change the sequence of that column! It was column #1 to begin with.
Unfortunately, I don't even know where to begin starting working around this issue in the actual report I'm editing, it's quite a bit more complex than above example - it contains various computes that depend on computes in earlier columns for example, so I can't just rearrange my field-list to prevent the use of SEQUENCE. Maybe the only way is to push the results into a HOLD temporarily so that I can fetch the columns pre-computed in the order I want them. Ugh...
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
September 24, 2010, 10:21 AM
Tom Flynn
This is a well-known negative regarding SUBTOTAL, SUB-TOTAL, SUMMARIZE, RECOMPUTE, etc.
You have to "code" and use SUBFOOT, period...
APP PREPENDPATH IBISAMP
-RUN
SET ASNAMES = ON
DEFINE FILE CAR
XMODEL/A30 = 'Total ' | MODEL;
XSALES1/A10 = PTOA(SALES, '(P6C)', XSALES1);
XSALES2/A25 = 'Total Sales: ' | XSALES1;
END
TABLE FILE CAR
SUM
SEATS
RPM
BY MODEL
BY SALES
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON SALES SUBFOOT
"<XSALES2<+0> <+0><ST.SEATS<+0><ST.RPM>"
" "
ON TABLE SET STYLE *
$
TYPE=REPORT, COLUMN=N2, SEQUENCE=1,$
TYPE=REPORT, COLUMN=N1, SEQUENCE=2,$
TYPE=SUBFOOT, HEADALIGN=BODY, STYLE=BOLD, $
TYPE=TITLE, STYLE=BOLD, JUSTIFY=LEFT, $
ENDSTYLE
END
-EXIT