Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to avoid total line on two lines
Page 1 2 3 

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] How to avoid total line on two lines
 Login/Join
 
Expert
posted
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,
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
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.
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Expert
posted Hide Post
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.
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Expert
posted Hide Post
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.
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
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. Big Grin

Cheers,

Kerry
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
Expert
posted Hide Post
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.
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
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. Wink

Cheers,

Kerry
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
Virtuoso
posted Hide Post
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.
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Expert
posted Hide Post
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.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
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!


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
and then there's always avoiding the issue by
ON {or BY } COUNTRY SUBTOTAL AS ''




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Expert
posted Hide Post
... 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.

Roll Eyes

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Master
posted Hide Post
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


WFConsultant

WF 8105M on Win7/Tomcat
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
Expert
posted Hide Post
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!


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
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
 
Posts: 21 | Location: South Africa | Registered: April 22, 2005Report This Post
Platinum Member
posted Hide Post
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 .


Release 7.6.9
Windows
HTML
 
Posts: 226 | Registered: June 08, 2003Report This Post
Expert
posted Hide Post
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.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
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.


Production: WF 7.7.03 / OS: Solaris Sparc 64/ WebServer: Apache Tomcat/6.0/AppServer:WebLogic 10.3 /DB: Oracle 11.2/ Output formats: HTML, Excel, PDF, CSV, ZIP
 
Posts: 12 | Location: Montreal | Registered: August 22, 2006Report This Post
Platinum Member
posted Hide Post
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


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Report This Post
Gold member
posted Hide Post
add "SET BYDISPLAY=ON" befor the TABLE request


WF 7703M, XP/Win7, MRE, RC, BID, PMF, HTML, PDF, Excel 2000/7/10
 
Posts: 73 | Location: NY | Registered: February 06, 2007Report This Post
Platinum Member
posted Hide Post
Thanks AlexU. That worked.


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Report This Post
Expert
posted Hide Post
I can't believe this thread has had 8647 views!

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


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
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


WEFOCUS - All releases starting at 4.3.6
MF FOCUS -- All releases starting at 3.8.68
OS/Platfor _ Predominately Z/OS, but all others as swell
Expected Output Format -- *
 
Posts: 60 | Location: 2 penn | Registered: May 22, 2003Report This Post
Silver Member
posted Hide Post
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.


---------------------
WebFOCUS 7.6
 
Posts: 41 | Registered: August 05, 2005Report This Post
Master
posted Hide Post
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


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Expert
posted Hide Post
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.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
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
 
Posts: 29 | Registered: March 30, 2007Report This Post
Silver Member
posted Hide Post
i'm sorry, could you edit your message? it seems to have been squashed by some errant HTML. Smiler


---------------------
WebFOCUS 7.6
 
Posts: 41 | Registered: August 05, 2005Report This Post
Member
posted Hide Post
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
 
Posts: 29 | Registered: March 30, 2007Report This Post
  Powered by Social Strata Page 1 2 3  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to avoid total line on two lines

Copyright © 1996-2020 Information Builders