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     Torals in the Header

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Torals in the Header
 Login/Join
 
Guru
posted
Hi,

I create an MRE report using a reporting abject.
I am sorting by the following fields:
Company
country
I have may 4 numeric fields for which I am displaying the detail by Company and country.
and the end of each country I am showing a subtotal.
Is there any way to display this total in the header of each country:

FRANCE TotalField1
Amount1
Amount2
Amount3

Where TotalField1=Amount1+amount2+amount3

Regards.


WebFocus 7.6.5
AND WebLogic server as web server
sql2005 as database server
 
Posts: 273 | Location: Europe | Registered: May 31, 2007Report This Post
Expert
posted Hide Post
TABLE FILE CAR
SUM
COMPUTE TotalField1/P13.2C = Amount1+amount2+amount3; NOPRINT
BY COUNTRY
SUM
Amount1
amount2
amount3
BY COUNTRY
HEADING
"

Good Luck...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Guru
posted Hide Post
Hi Tom,

My problem is that I do not know in advance How many lines will be linked to country France in advance.
Amount1 amount2 and amount3 assume at this time I have only 3 lines in my table but It can be 100 of lines or more and I would like to print the total before displaying the detail.

Thanks a lot.

Majid.


WebFocus 7.6.5
AND WebLogic server as web server
sql2005 as database server
 
Posts: 273 | Location: Europe | Registered: May 31, 2007Report This Post
Expert
posted Hide Post
Majid,

If you don't know how many columns there are, how can you summarize them???

If you are building the fex dynamically, then you HAVE to count the columns.

Say, you have 5 amount columns, then, set an AMPER variable to equal that number:

-SET &FLD_CNTS = 5;

then:

 
-SET &CNTR = 1;
TABLE FILE CAR
SUM
COMPUTE TotalField1/P13.2C = 
-REPEAT FLD_LOOP &FLD_CNTS TIMES
-SET &FLD.&CNTR = IF &CNTR EQ &FLD_CNTS THEN 'AMOUNT' || &CNTR | '; NOPRINT' 
-                                       ELSE 'AMOUNT' || &CNTR | ' +';
&FLD.&CNTR
-SET &CNTR = &CNTR + 1;
-FLD_LOOP
BY COUNTRY

SUM
Amount1
amount2
amount3
amount4
amount5
BY COUNTRY
HEADING
"<COUNTRY <TotalField1 </1"



Best I can do with the information given...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Expert
posted Hide Post
Majid,

Is this what you want?

TABLE FILE CAR
SUM SALES 
BY COUNTRY
BY CAR
ON COUNTRY SUBHEAD
"<ST.SALES"
END  


Lord knows, this is an incredibly simple example. But maybe we were making too much of it.

If you need to see the detail, perhaps you can do a multi-verb request as well.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
Majid,

I was writing the same example as Ginny...




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
Expert
posted Hide Post
We're going to get this figured out if it kills us. Here's another example with the detail:

SET ASNAMES=ON
TABLE FILE CAR
SUM SALES AS TOTSALES
BY COUNTRY 
PRINT SALES 
BY COUNTRY
BY CAR
ON TABLE HOLD FORMAT ALPHA
END
-RUN
? HOLD HOLD
-*-EXIT

TABLE FILE HOLD
PRINT SALES 
BY COUNTRY
BY CAR
ON COUNTRY SUBHEAD
"SALES FOR <COUNTRY ==> <TOTSALES"
END  


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Guru
posted Hide Post
Hi Frank and Ginny,

Both examples are working fine and thanks a lot.
The only problem is that I have many totals to display in the header for different columns but i am not able to align them with the fileds they are summing.
I will try to figure it out.

Thanks again to all of you.

Majid.


WebFocus 7.6.5
AND WebLogic server as web server
sql2005 as database server
 
Posts: 273 | Location: Europe | Registered: May 31, 2007Report This Post
Virtuoso
posted Hide Post
Majid

In that case there is an other way to do this.
You can put your totals per country in a dummy model (total) and combine these data in the report.


SET ASNAMES=ON
DEFINE FILE CAR
DUMMYMOD/A24='TOTAL';
END
TABLE FILE CAR
SUM SALES RCOST DCOST 
CNT.BODY AS TEL  
BY COUNTRY
BY DUMMYMOD AS MODEL
ON TABLE HOLD AS HTOTAL
END
TABLE FILE CAR
SUM SALES RCOST DCOST 
CNT.BODY AS TEL
BY COUNTRY
BY MODEL
ON TABLE HOLD AS HDETAIL
END
TABLE FILE HTOTAL
PRINT SALES RCOST DCOST TEL
BY COUNTRY
BY MODEL
ON TABLE HOLD AS HFINAL
MORE
FILE HDETAIL
END
-* now you have both the detail and the summary line in the holdfile
-* to get it printed in the proper sequence you might need an extra sortorder field 
DEFINE FILE HFINAL
SORTORDER/I2=IF MODEL EQ 'TOTAL' THEN 1 ELSE 2;
END
TABLE FILE HFINAL
PRINT 
 SALES 
 RCOST
 DCOST
 TEL
BY COUNTRY
BY SORTORDER NOPRINT
BY MODEL
ON COUNTRY SUBHEAD PAGE-BREAK
ON SORTORDER SUBFOOT
"" 
END


And now you can play with the letter type, color etc so the total per country is printed in bold and cursief and the detail is not...

I hope this is what you want..

I think there are more solutions for this issue.




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
Guru
posted Hide Post
Frank,

You are not a master by as Italian says:
You are Maestro.
This is working exactly as I wanted.

Regards.
 
Posts: 273 | Location: Europe | Registered: May 31, 2007Report 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     Torals in the Header

Copyright © 1996-2020 Information Builders