Kerry,

Since you are being very generous in pointing us in the right direction to techniques and solutions, could you, perhaps, please tell us the definitive method on avoiding total lines showing up on two lines. I think it has something to do with the internal matrix and the width of the BY column being subtotaled but I haven't found the definitive method of correcting this.

Thank you,

Francis.

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

The TOTAL line is split into 2 lines when the label text "TOTAL sort value" is longer than the length of the BY field values combined. Try running the following two examples:

TABLE FILE CAR
SUM RCOST DCOST SALES
BY COUNTRY SUBTOTAL
ON TABLE SET STYLEMODE FIXED
END

TABLE FILE CAR
SUM RCOST DCOST SALES
BY COUNTRY SUBTOTAL
BY CAR
ON TABLE SET STYLEMODE FIXED
END

In the first example, the word "TOTAL" and the COUNTRY value are LONGER than the total length of the sort field values for COUNTRY so the SUBTOTAL values appear on a second line.

In the second example, the word "TOTAL" and the COUNTRY value are SHORTER than the total length of the sort field values for COUNTRY and CAR so the SUBTOTAL values appear on the same line.

It simply is an automatic formating function to avoid overwriting.

Interesting, then, that when you do NOT use STYLEMODE FIXED (which all our HTML, PDF, Excel reports are) in your example, the subototal is not on two lines.

TABLE FILE CAR
SUM RCOST DCOST SALES
BY COUNTRY SUBTOTAL
END

Why, then, does it happen sometimes and not others - when STYLEMODE is NOT FIXED? How does one predict when it will happen, and when it does happen, what to do about it?

Cheers.

Output formats such as HTML and EXCEL, by their very nature, have dynamic cell widths and therefore the output from FOCUS will put the SUBTOTAL values on the SAME line as the "TOTAL" label. This is because HTML and EXCEL can adjust for the width of the "*TOTAL ..." label length.

Output formats such as HTML STYLEMODE FIXED or PDF determine their positioning by character length and do not contain CELLS to place values in, as do HTML and EXCEL. Therefore you experience the splitting of the SUBTOTAL line into two lines.

Try the following code. The length of the field COUNTRY is a maximum of 10 characters and the length of the "*TOTAL " is 7. I created a field called COUNTRYL with a length of A17 and then sorted by it. This created enough space in the COUNTRY sort column so that the "*TOTAL ..." could fit in that column and therefore the SUBTOTAL fits on one line.

TABLE FILE CAR
SUM RCOST DCOST SALES
COMPUTE COUNTRYL/A17=COUNTRY; NOPRINT
BY COUNTRY NOPRINT SUBTOTAL
BY COUNTRYL
ON TABLE SET STYLEMODE FIXED
END

The key here is that the BY fields THAT ARE VISIBLE determine how much space there is to put the "*TOTAL ..." label. Try the following and you will see that even though the output is HTML, the SUBTOTAL is split into two lines. This is because there are no VISIBLE sort field columns on the report.

TABLE FILE CAR
SUM RCOST DCOST SALES
BY COUNTRY NOPRINT SUBTOTAL
END

Add the BY CAR sort and it is back to one line because there is a VISIBLE sort field column to put the "*TOTAL ..." label in.

TABLE FILE CAR
SUM RCOST DCOST SALES
BY COUNTRY NOPRINT SUBTOTAL
BY CAR
END

Unfortunately,

quote:

Output formats such as HTML and EXCEL, by their very nature, have dynamic cell widths and therefore the output from FOCUS will put the SUBTOTAL values on the SAME line as the "TOTAL" label

isn't always true, hence my original question. IBI have posted techniques where you define a dummy field and sort by that dummy field and the field with the subtotal to avoid the two-line subtotal.

Regards.

Hi Francis, when you say "IBI have posted techniques where you define a dummy field and sort by that dummy field and the field with the subtotal to avoid the two-line subtotal", does this mean you have already found the solution? If so, it will be great if you can share the info with all our members here.

Cheers,

Kerry

Sorry, here's the link to a solution:

http://techsupport.informationbuilders.com/app/css_web_...rpt_fmt_subtotal.htm

I don't understand this

quote:

If the row title for the subtotal line exceeds the width of the columns(s) for BY fields

The row title, I imagine, would be the word 'Total' plus the value of the column being subtotaled.

Regards.

Hi Francis, if you like, I would recommend that you should fill out the "Content Feedback" form at the bottom of the page. This way, your question will be reviewed and research within IBI.

Hope this helps, and have a great weekend.

Cheers,

Kerry

Francis, I think I addressed the different scenarios as to when this would happen and when not by the code examples I included in my post. The technique stated by IBI is a better way to resolve this issue. However, you keep stating that this happens unpredictably. Can you post an example of code in which the split happens but should not?

The rule to remember is to add up the maximum possible lengths of all the VISIBLE sort field columns and make sure that is equal to or greater than the total length of the row title "*TOTAL ..." for the SUBTOTAL line. This should keep the SUBTOTAL information on one line ALL the time.

IBI's technique works because it is doing the SUBTOTAL on the sort field which contains a maximum of 1 character (a single blank). So the length of "TOTAL " is shorter than 10 characters (the max length of a COUNTRY) and therefore does not split the SUBTOTAL line.

I hope this helps bring a little more clarity to the question at hand.

Back again to this problem!

I don't get it!

quote:

The rule to remember is to add up the maximum possible lengths of all the VISIBLE sort field columns and make sure that is equal to or greater than the total length of the row title "*TOTAL ..." for the SUBTOTAL line. This should keep the SUBTOTAL information on one line ALL the time.

I get it!

quote:

In the first example, the word "TOTAL" and the COUNTRY value are LONGER than the total length of the sort field values for COUNTRY so the SUBTOTAL values appear on a second line.

In the second example, the word "TOTAL" and the COUNTRY value are SHORTER than the total length of the sort field values for COUNTRY and CAR so the SUBTOTAL values appear on the same line.

I'll have to print that quote and keep it close!

and then there's always avoiding the issue by
ON {or BY } COUNTRY SUBTOTAL AS ''

... and then throw another spanner in the works.

All three of Mickey's examples show the row title on one line when I run them .... because I've added - TITLE='Country of Origin' - to the car.mas.

Because the length of the title is 17 chars it has the same effect as Mickey's second example of using a computed column of format A17.



T

Try this,
TABLE FILE CAR
PRINT DEALER_COST
BY BODYTYPE AS '- BODYTYPE -'
-* MAKE THE AS BIG ENOUGH TO FIT THE SUBTOTAL INCLUDING THE VALUE
-* THE TRICK IS TO USE NON BLANK CHARACTERS AT THE BEGINING AND END OF THE AS
-* FOR THE FIRST VERB OBJECT
-* THE NUMBER OF SPACES NEEDED IS BASED ON THE FONT TYPE. TRUETYPE USES
-* VARIABLE WIDTH CHARACTERS, A W TAKES UP MORE SPACE THEN A BLANK

ON BODYTYPE SUBTOTAL AS 'Total For:'
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, SIZE=8, STYLE=NORMAL, GRID= OFF, SQUEEZE= ON, $
END

Anyone wonder why we have to go through hoops to get this to work? I'd rather that the software take care of this pesky problem than get a new bell or whistle!

Or... give the developer control over the behaviour with a SET command. I know Gerry laughed at the suggestion at last year's Summit, but I still think it will simplify life.

Ruhan

Generally WebFocus will enlarge the first Sort column so that the text of the Subtotal line will fit under it. When this is insufficient you can reduce the amount of text in the subtotal, i.e. instead of *TOTAL , put TOT .

Sure,

But you shouldn't need to do that if your output is a cell-based one, i.e. HTML tables or Excel spreadsheets. The cell should be able to expand automatically.

I can understand that this behaviour exists because of mainframe fixed-format reports and, perhaps, for PDF, but we've all been yanked, kicking and screaming, into the 21st century, so should WebFOCUS.

Cheers.

quote:

THAT ARE VISIBLE

I made some test with the SUBTOTAL in PDF format and here is my understanding

If the literal appearing on the SUBTOTAL is 30 characters long

And you have 5 collumn of 10 characters long appearing on the detail of your report

The fist 3 collumn MUST by sorted.

So basically, in order to have enough space to display long literal in one line you have to sort extra field.

Here's my code below:

TABLE FILE CAR
PRINT RCOST DCOST SALES
BY COUNTRY
BY CAR
ON COUNTRY SUBTOTAL AS ''
END

By default, BY fields that are duplicates were suppressed. Is there a way not to without messing up the subtotal (it being on the same line as the subtotal values)?

Any suggestions?

Thanks,
Dan

add "SET BYDISPLAY=ON" befor the TABLE request

Thanks AlexU. That worked.

I can't believe this thread has had 8647 views!

I still can't always get the darn thing to work.

Basically,k as Mickey said, the TOTAL line is split when the label *TOTAL [fieldname} value IS longer than the BY field values. This might be because there are NO BY fields, (everything is NOPRINTED), or there is only one, and by the time you have that value + the label (* TOTAL) you have exceeded the space.
Sometimes this can be "fixed" by changing the first verb object (if it is an alpha field) into a low level BY field, thus extending the size of the BY's. Sometimes, if you put in a DUMMY BY (1 character) with a value of space as A BY, and then SUBTOTAL on that, you reduce the size of the value.
There are some other examples on the tech support web site:
http://techsupport.informationbuilders.com/sps/22302044.html
http://techsupport.informationbuilders.com/tech/ibm/ibm_tcn_024.html

Man, let me just add my butt to the list of those in pain due to this WebFOCUS ... feature.

i have two hundred reports that sort by a single BY field that is actually a variable that gets passed in (user chooses the sort field, which is not displayed because it appears elsewhere in the report already). some of these reports have TOTAL lines at the bottom, and they all display the actual total numbers on a separate line from the "total" label, and for no good reason.

The algorithm WebFOCUS is using seems flawed at best if it doesn't do the job it's meant to do: determine if there's enough room for the label. besides which, HTML is a cell-based output format, so i don't see why this algorithm is even invoked in the first place for this format.

Depending on how many fields you need to subtotal, you can use RECAP and SUBFOOT. I would have to check the exact syntax, but it is something like:
ON &BY RECAP ST_AMT/D12.2=ST.AMT
ON &BY SUBFOOT
"Total &BY<+0> ST.AMT"

of course if you are using HTML then you have the problem of lining up your columns

quote:

and by the time you have that value + the label (* TOTAL) you have exceeded the space

This has been explained over and over and over again, and I still don't always get it.

In a HTML table, you could have a cell in one row with only one character in it. Then in another row, in the same column, you could have a cell with 500 characters in it. Everything still lines up.

In PDF, you can use WRAP or WIDTH to alter the width of a report column, so there shouldn't be any problem with fitting the word TOTAL on the same line of a subtotal.

Instead of explaining why this happens, why isn't this getting fixed? Like I've said before, I'd give up some new-fangled feature like accordion reports to get this corrected.

I think I'll get a tea now.

Agreed. In the interim, use subfoot with free text and
TABLE FILE CAR
PRINT SALES
BY COUNTRY SUBFOOT
"*TOTALS* FOR END

ira 5.3.6

i'm sorry, could you edit your message? it seems to have been squashed by some errant HTML.

Sorry text got chopped off. Let me try again.
Agreed. In the interim, use subfoot with free text and if need be style it for positioning
TABLE FILE CAR PRINT SALES BY COUNTRY SUBFOOT
"*TOTALS* FOR left caret COUNTRY : left caret ST.SALES "
END