Subtotal / Total not showing properly

*** on the total means your field is not big enough to handle the total..

In Focus since 1993. WebFOCUS 7.7.03 Win 2003

Posts: 1903 | Location: San Antonio | Registered: February 28, 2005

Ignored post by Prarie posted

Gold member

posted

Unforunately that didn't work whether I tried D4, D6, I7, P2 etc in the SUM ITEM_COUNT/D4 line.
Did try all these already before I posted the message. :-(

WF V7.7.03
Platform: AIX 6.1 64-bit, WinXP, IE 8.0
Output Formats: Html, Pdf, Excel

Posts: 71 | Registered: October 16, 2006

Ignored post by Adrian Wong posted

Gizmo

Gold member

posted

You're probably still not going large enough. Keep in mind that these are display formats, not internal formats.

D4 will only display up to 9999. For your count field, try I11.

Windows: WF 7.6.2: SQL Server 2008 R2

Posts: 86 | Location: Chicago | Registered: August 03, 2007

Ignored post by Gizmo posted

Gold member

posted

No, I11 did not work.
I tried I12 and it gave me this message:
(FOC035) ERROR IN THE FORMAT OF THE COMPUTED FIELD DEFINITION: ITEM_COUNT/I12

I tried D20 it didn't work either.
But there are only 101 records of the first type and 50 of the second type. The grand total is only 150 records.

WF V7.7.03
Platform: AIX 6.1 64-bit, WinXP, IE 8.0
Output Formats: Html, Pdf, Excel

Posts: 71 | Registered: October 16, 2006

Ignored post by Adrian Wong posted

Francis Mariani

Expert

posted

When you post code, please put it between

[code]

[/code]

tags. We cannot see the ITEM_COUNT being used in the SUBFOOT. Have you tried removing NOPRINT from

SUM ITEM_COUNT/I7 NOPRINT

to see if the value being summed is correct?

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, 2005

Ignored post by Francis Mariani posted

Gold member

posted

Ok I will use

...

next time.
Yes, I have removed the NOPRINT before and the sum of the item_count for each of the record type is correct at 101 and 50.
The line that tries to display the number is right after the "ON CARD_TYPE_CODE SUBFOOT" line where it shows "Total

WF V7.7.03
Platform: AIX 6.1 64-bit, WinXP, IE 8.0
Output Formats: Html, Pdf, Excel

Posts: 71 | Registered: October 16, 2006

Ignored post by Adrian Wong posted

Prarie

Virtuoso

posted

Oh...and please read https://forums.informationbuilders.com/eve/forums/a/tpc/...1057331/m/8941065232

In Focus since 1993. WebFOCUS 7.7.03 Win 2003

Posts: 1903 | Location: San Antonio | Registered: February 28, 2005

Ignored post by Prarie posted

Francis Mariani

Expert

posted

You have to be careful how the program is coded. The value of ITEM_COUNT in your subfoot is based on the last BY statement, not the whole report.

Run this code to see what I mean:

TABLE FILE CAR
SUM SEATS NOPRINT
BY COUNTRY NOPRINT
PRINT COUNTRY CAR MODEL WHEELBASE 
BY COUNTRY NOPRINT
BY CAR NOPRINT
BY MODEL NOPRINT

ON COUNTRY SUBFOOT
"COUNTRY TOTAL SEATS: <SEATS"
ON TABLE SUBFOOT
"BAD TABLE TOTAL SEATS: <SEATS"
"GOOD TABLE TOTAL SEATS: <ST.SEATS"
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLESHEET *
TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=8, $
ENDSTYLE
END

Result:

COUNTRY   CAR        MODEL                WHEELBASE SEATS
--------- ---------- ------------------ ----------- -----
ENGLAND   JAGUAR     V12XKE AUTO              105.0     2
ENGLAND   JAGUAR     XJ12L AUTO               112.8     5
ENGLAND   JENSEN     INTERCEPTOR III          105.0     4
ENGLAND   TRIUMPH    TR7                       85.0     2
COUNTRY TOTAL SEATS: 13

FRANCE    PEUGEOT    504 4 DOOR               108.0     5
COUNTRY TOTAL SEATS: 5

ITALY     ALFA ROMEO 2000 4 DOOR BERLINA      101.0     4
ITALY     ALFA ROMEO 2000 GT VELOCE            92.5     2
ITALY     ALFA ROMEO 2000 SPIDER VELOCE        88.6     2
ITALY     MASERATI DORA 2 DOOR                102.3     2
COUNTRY TOTAL SEATS: 10

JAPAN     DATSUN     B210 2 DOOR AUTO          92.1     4
JAPAN     TOYOTA     COROLLA 4 DOOR DIX AUTO   93.3     4
COUNTRY TOTAL SEATS: 8

W GERMANY AUDI       100 LS 2 DOOR AUTO       105.3     5
W GERMANY BMW        2002 2 DOOR               98.4     5
W GERMANY BMW        2002 2 DOOR AUTO          98.4     4
W GERMANY BMW        3.0 SI 4 DOOR            106.0     5
W GERMANY BMW        3.0 SI 4 DOOR AUTO       106.0     5
W GERMANY BMW        530I 4 DOOR              103.8     5
W GERMANY BMW        530I 4 DOOR AUTO         103.8     5
COUNTRY TOTAL SEATS: 34 
 
BAD TABLE TOTAL SEATS: 34 
GOOD TABLE TOTAL SEATS: 70

The ST.SEATS is the correct way to have a grand total in a subfoot.

I don't know if this will help solve your problem. I see that you have
ON CARD_TYPE_CODE SUBFOOT

and
ON TABLE SUBFOOT

I assume you require totals in both subfoots, so my example shows you that.

(Did you know you can edit a previous posting you made by clicking the yellow folder icon at the bottom right of your posting, then put the code tags in so we can see the complete code).

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, 2005

Ignored post by Francis Mariani posted

PBrightwell

Master

posted

I think the problem is in the MFD. Your field is defined as P2. Try changing ITEM_COUNT/D20 to COMPUTE ITM_CT/D20=ITEM_COUNT; AS 'ITEM_COUNT'
and then use ST.ITM_CT in your subfoot

Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes

Posts: 755 | Location: TX | Registered: September 25, 2007

Ignored post by PBrightwell posted

Gold member

posted

Thanks, PBrightwell. I followed your suggestion and changed it a bit as well and it worked for my report. I suspected it had something to do with that P2 definition for the data but now I know how to display it thanks to you.
Thank you to Francis as well for his suggestion.

WF V7.7.03
Platform: AIX 6.1 64-bit, WinXP, IE 8.0
Output Formats: Html, Pdf, Excel

Posts: 71 | Registered: October 16, 2006

Ignored post by Adrian Wong posted

GamP

Virtuoso

posted

The reason this happened is because you had the ITEM_COUNT field reformatted in the request.
The ST. andTOT. prefixes look at the very first instance of the field ITEM_COUNT, which is a P@ field. Whatever reformatting you specified, it would still be displayed as a P2 field.
PBrightwell's solution works fine, because it creates a new unique field.
You could also have used DEFINE to create and total this new field, or change the fieldsize in the MFD for this field.

GamP

- Using AS 8.2.01 on Windows 10 - IE11.
	in Focus since 1988

Posts: 1961 | Location: Netherlands | Registered: September 25, 2007

Ignored post by GamP posted