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     Suppress some across totals

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Suppress some across totals
 Login/Join
 
FrankDutch
Virtuoso
posted
I have report like this

TABLE FILE CAR
SUM 
     SEATS
BY COUNTRY
ACROSS BODYTYPE
ON TABLE SET PAGE-NUM OFF 
ON TABLE ROW-TOTAL AS 'TOTAL'
ON TABLE COLUMN-TOTAL AS 'TOTAL'
END


For some reason I do not want the row total for "England" nor the column-total for "Coupe".

And also the total of total should not be calculated.

I tried something with WHEN conditions, but did not get the wanted results.
Any help would be welcome.




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
mgrackin
Virtuoso
posted Hide Post
Frank,

To get specific COLUMN-TOTALS try using:

ON TABLE SUBTOTAL fieldname fieldname . . .

This is just a guess off the top of my head.

As far as getting the ACROSS-TOTAL for certain rows, there are no suggestions that i can think of.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
hammo1j
Master
posted Hide Post
Frank

Try ACROSS ... COLUMNS ... AND to get the across columns you want.

Tony A. has got further great techniques for using FML for working with across columns - it's in the user area of tech support.

How did your guys get on after Euro Under 21 final?

Regards

John


Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
FrankDutch
Virtuoso
posted Hide Post
Mickej, John I'm afraid that's not the solution I was looking at.




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
<JJI>
posted
Hi Frank,

The only way to accomplice what you want, is to make your own column totals and rowtotals. You'll need to make your own across-total column and your own total line. So basicly you will have to create 3 files that you'll have to combine in 1 file with a match command. I'll have done this already with an ACROSS but never in combination with a BY.
I'll post my example in the morning.
To get this total line and total colomn in place create a dummy ACROSS field and a dummy BY field that both will contain a large number like 999 or 9999999999999 etc, to make sure it will be the last ACROSS-column and the last BY-field-value (last line) in your report. Since this colomn and row is only containing the sum of all the other data you'll need to give your individual records also a dummy ACROSS value and a dummy BY value ( from 1 to n)......

I know this not very clear but I'll hope this get you started. It will be more clear when I post my example.
 
Report This Post
Francis Mariani
Expert
posted Hide Post
Here's an idea: do the ACROSS and HOLD the data. Then, in the report, you can specify the columns and specify which ones need subtotals.

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
-RUN

TABLE FILE CAR
SUM SALES/D12 AS 'S' ACROSS COUNTRY
BY BODYTYPE
ON TABLE HOLD AS H001
END
-RUN
?FF H001
-RUN

TABLE FILE H001
SUM
SENGLAND
SFRANCE
SITALY
SJAPAN
'SW GERMANY'
BY BODYTYPE

ON BODYTYPE SUBTOTAL SENGLAND SITALY AS 'TOTAL'
HEADING
"WEBFOCUS REPORT"
WHERE READLIMIT EQ 100
WHERE RECORDLIMIT EQ 100
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLESHEET *
BORDER=1, BORDER-COLOR=SILVER,
FONT='ARIAL', SIZE=8, $
TYPE=SUBTOTAL, COLOR=BLUE, $
END


If the field values in the across change regularly, you could do a TABLE FILE, HOLD and -READ to create DM variables...


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
FrankDutch
Virtuoso
posted Hide Post
Thanks for the smart solutions, the last one (Francis) works very good, in this case I have some static by and across values so I can build the report as shown.




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
<JJI>
posted
Francis,

I thought suppressing a ROW-TOTAL was a key question for Frank.

I can do it myself by first getting all the details, then get the sum of all the details and then merge these two result by using the MATCH command.
But since your solution is less complicated then mine I wondered if you could suppress the ROW-TOTAL (= ACROSS-TOTAL) in your example?
 
Report This Post
FrankDutch
Virtuoso
posted Hide Post
Dirk

The row-total is in the solution Prancis suggests no more a real issue.
Since the fields are put into a hold I can do an define on that hold

...
ROWTOTALD12S=IF BODYTYPE EQ 'COUPE' THEN 0 ELSE E01+E02+E03+E03; AS 'rowtotal'
....

And I can give the individual fields the look and feel I want.
It's better than the normal "accross" in this case.

Thanks for the helping thoughts...




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Suppress some across totals

Copyright © 1996-2020 Information Builders