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.4

This message has been edited. Last edited by: Kerry,

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.

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?

Mmm. I wondered that myself!

Bethany,

You may remember this:
Border Trouble

Here's an example of what you want to do:

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

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

This was just what I needed.

I'm going to love this site more and more.

Frank

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,

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

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

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.

Thanks for checking it out Francis....I guess we can still hope that it will get fixed someday....

Bethany

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

Very nice Mickey!

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!!! 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

OK, now you're kidding, right?!

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.

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

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

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...

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