Hi all.

Thanks to this forum, I have recently discovered a multi-verb method of calculating percentages in a single report request.

However, it also results in the Subtotals taking up 2 lines instead of 1, and this isn't acceptable to the client.

(I'm guessing that this is related to the introduction of the 2nd verb, but am happy to be corrected if wrong).

So my question is: is this behaviour inevitable, or can something be done to push the subtotals back onto a single line?

Any advice/tips welcome.

The following code should illustrate the issue:

DEFINE FILE CAR
TOT_SALES/P10.2=SALES;
END
TABLE FILE CAR
HEADING
"Sales for (less-than-symbol)COUNTRY"
FOOTING
" "
SUM TOT_SALES NOPRINT
BY COUNTRY
SUM SALES
COMPUTE PCT/P6.2=SALES/TOT_SALES*100;
BY COUNTRY NOPRINT SUMMARIZE AS 'TOTAL' AND PAGE-BREAK
BY CAR
BY MODEL
IF SALES GT 0
ON TABLE NOTOTAL
END


TIA


Reg
(edit as left-caret(?) in heading interpreted as html)

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

You can use SUBFOOT and position them,

BY COUNTRY NOPRINT SUBFOOT AND PAGE-BREAK
" TOTAL (less-than-symbol)ST.SALES (less-than-symbol)ST.PCT"

Thanks, but AFAIK that won't put the totals into the same column positions as the data, will it?

In reality, this is for a substantial report with over 30 data columns. Output will normally be into PDF or EXL2K.

Some of the columns will be separated by vertical lines, which should also extend into the subtotals.

Reg,

Welcome to the best forum this side of the greenwich meridian!

The problem that you are facing is an old one that stems from way back when .....

If you search (top right link) on "two total lines" then you will find many people have encountered this problem. There are a few things that you can do to alleviate it and there are plenty of ideas contained in the search results that you should receive.

Good luck!

T

Thanks Tony. Which side of the Meridian are you on, anyway? [off-topic, can you PM me, please?]

I had done a couple of searches, but obviously hadn't got the right combination of words. I've quickly looked at a few of the threads, and they seem to be pointing to width as being the issue, but I don't think that's the case here.
- You can define an A3 edit of country and use that, and it still puts out 2 lines, despite being shorter than Car, let alone Car + Model.
- If you remove the first verb (and change the PCT calc) it goes back to 1 line.

I don't mind carrying on the search if I'm likely to find a resolution, but I'm not that confident from what I've read so far.

Sorry to be going on about an issue that's obviously been well-aired in the past, but does anyone know if there's a definitive solution?

Thanks again


Reg

quote:

a definitive solution

Other people seem to think there is a definitive solution, but after many years of looking, I still haven't found one. It's a very annoying trait of WebFOCUS that is because the underlying code is based on mainframe FOCUS using non-proportional fonts (or something like that) This needs to be corrected in the software and in the thousands of reports it currently occurs in.

You can get the subtotal on one line by adding BY CAR to the first verb request - which of course means your COMPUTE will no longer work. Or you can remove the NOPRINT from COUNTRY and that will also fix the problem. Finally, you can add a dummy column per the code below. In this case your subtotal text and heading move into the dummy column, thereby producing a single subtotal line. None of these are ideal solutions. I also tried to do something with TYPE=SUBTOTAL and COLSPAN in the StyleSheet but that also proved fruitless. The easiest solution may be to break it into a TABLE/HOLD-JOIN-TABLE combination in order to avoid the multi-verb situation.

DEFINE FILE CAR
 TOT_SALES/P10.2=SALES;
 BLANK/A1 = '';
END
-*
TABLE FILE CAR
 HEADING
  "Sales for <COUNTRY"
 FOOTING 
  " "
 SUM TOT_SALES NOPRINT
 BY COUNTRY    NOPRINT
 BY BLANK      AS ''

 SUM SALES 
 COMPUTE PCT/P6.2 = SALES / TOT_SALES * 100 ;
 BY COUNTRY NOPRINT
 BY BLANK   AS ''
 BY CAR
 BY MODEL
 WHERE SALES GT 0 ;
 ON COUNTRY SUMMARIZE SALES AND PCT AS 'Total' AND PAGE-BREAK
 ON TABLE NOTOTAL
END

You may be able to eliminate the leading verb by specifying WITHIN.

TABLE FILE CAR
. . .
SUM 
  TOT_SALES WITHIN COUNTRY NOPRINT
  SALES 
  COMPUTE PCT/P6.2 = SALES / TOT_SALES * 100 ;
 BY COUNTRY NOPRINT
 BY ...

-- but that may have unanticipated effects on subtotal/recompute/summarize values.

Francis - I'm inclined to agree that this is a bit of a 'feature' that could do with being addressed.

Dan - I think that any of the available work-arounds are at best a little better than the two-line problem from a user-experience point of view, so I guess we'll end up ditching what looks like an elegant solution for the Table-Hold-Join-Table that's been in use up to now.

Jack - I'll take a closer look at that in the office tomorrow.

Thanks for your input/comments.


Reg

Here's a variation on Jack's suggestion that seems to do the trick.

DEFINE FILE CAR
 SALES/D10.2 = SALES ;
END
-*
TABLE FILE CAR
 HEADING
  "Sales for <COUNTRY"
 FOOTING
  " "
 SUM SALES
     PCT.SALES/D6.2 AS 'Sales %' WITHIN COUNTRY
 BY COUNTRY NOPRINT PAGE-BREAK
 BY CAR
 BY MODEL
 WHERE SALES GT 0 ;
 ON COUNTRY SUMMARIZE AS 'Total'
 ON TABLE NOTOTAL
END

Of course there's always the FML option that is fairly controlable.

T

Jack - Your suggestion does solve the split-line issue beautifully and elegantly, but as you anticipated it does screw up the subtotal, as it becomes an average % (i.e. 100/no-of-lines), as TOT_SALES gets aggregated across lines too.

Dan - Your example works in the CAR example, but I've tried putting it in my complex case, and get "ERROR: MR_UNKNOWN_ERROR -12: Pcb2.: java.io.EOFException". Any ideas on that?

The current % fields in the real report use a user-defined function, but I think PCT. will be OK, and one of the values uses an equivalent of IF COUNTRY NE 'ENGLAND' THEN 0 ELSE pct_fn(SALES,TOT_SALES) - but that can be shifted into a DEFINE and use PCT.D_SALES to overcome that, if I can just get this working.

Is this the point at which I should raise a case with IB?

Cheers



Reg

quote:

ERROR: MR_UNKNOWN_ERROR -12: Pcb2.: java.io.EOFException

Reg,

A search of this forum found this post regarding this error. See if any of the suggestions might help in your case.

http://forums.informationbuild...1057331/m/6681058331

Sorry, I should've thought of that, shouldn't I.

Have had a look at the suggestions, and not found anything useful. I'm tabling direct from a Focus file with no joins or old defines in place. Have also tried removing all styling, and it still crashes. So I guess I'll raise a case for it and see where that gets us.


Reg

quote:

MR_UNKNOWN_ERROR -12: Pcb2.: java.io.EOFException

Reg...you might want to run your own search on the above error using the Advanced Search link at the top of this forum page...there were a few other posts regarding security issues, etc. that might be worth a look...in the end, opening a case with IBI might be necessary.

Yes, I've done that Dan, and haven't found anything useful.

The report runs through until I add a single line with PCT.field /fmt WITHIN group, and then crashes consistently.

Regards


Reg

quote:

1. and this isn't acceptable to the client.

Ah... clients are a tiresome species

quote:

2.The problem that you are facing is an old one that stems from way back when .....

It seems to me that this is a problem that appeared with HTML and then with PDF. If you use the following (which mimics MF/Focus), the 2-line problem disappears:

  
DEFINE FILE CAR
TOT_SALES/D10.2=SALES;
END
TABLE FILE CAR
HEADING
"Sales for <COUNTRY"
SUM TOT_SALES NOPRINT
BY COUNTRY
SUM SALES 
COMPUTE PCT/D6.2%=SALES/TOT_SALES*100;
BY COUNTRY NOPRINT SUMMARIZE AS 'TOTAL' AND PAGE-BREAK
BY CAR
BY MODEL
IF SALES GT 0
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT STANDARD
END

So, yes, I think that IBI should address this problem.

quote:

The report runs through until I add a single line with PCT.field /fmt WITHIN group, and then crashes consistently.

The case raised for this has resulted in a solution to the crash problem. The cause is the space between the field and /fmt elements, and the solution is to write it as PCT.field/fmt.

On the down-side, there are a couple of issues with the approach:

• The subtotals for this approach appear to be a summation of the individual values, and theefore do not always total to 100%
  
• One of my reports shows a combination of sales and forecasts by week over a half-year. The PCT solution works for the weekly version, but there's also a Cumulative version, where columns aggregate through the rows so that final-week value = total of weekly values. The multi-verb approach calculated the phasing accurately (as both Wk and Cumulative versions could reference the same full-half total in the matrix), but of course the simple PCT. approach aggregates all of the component figures.
  

So I guess we've got a partial solution.


Reg

Reg,

Would not the movement of your PCT. to a field in the define be helping with this difficulty? Also the use of double precision value maybe?

Kofi

Kofi

Not sure if I'm misunderstanding you, or you're misunderstanding the context.

The PCT. here is in conjunction with the following WITHIN, and is therefore on a display field and cannot be used within a define.

If you're meaning to move the whole calculation of the percentage into a define, then the problem is in determining the divisor in the calculation without having to table it out in a preceding step (which is kind of where this thread kicks off).

If I'm missing your point, please clarify.

Thanks


Reg

Reg,

I missed WITHIN - my bad

Kofi