Focal Point
Torals in the Header
December 07, 2007, 06:54 AM
Majid JeddiTorals in the Header
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
December 07, 2007, 08:32 AM
Tom FlynnTABLE 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
December 07, 2007, 09:54 AM
Majid JeddiHi 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
December 07, 2007, 03:45 PM
Tom FlynnMajid,
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
December 07, 2007, 03:52 PM
GinnyJakesMajid,
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
December 07, 2007, 03:59 PM
FrankDutchMajid,
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 |
December 07, 2007, 04:11 PM
GinnyJakesWe'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
December 08, 2007, 04:48 AM
Majid JeddiHi 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
December 08, 2007, 10:19 AM
FrankDutchMajid
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 |
December 08, 2007, 11:44 AM
Majid JeddiFrank,
You are not a master by as Italian says:
You are Maestro.
This is working exactly as I wanted.
Regards.