PCT. and ACROSS and ROW-TOTAL and ACROSS-TOTAL and COLUMN-TOTAL (Here I go again!)

Francis,

I'm not quite sure what you are trying to do. I see that WebFOCUS is summing the %'s anf therefore giving you a value greater than 100%. However, I would just not include the % field in the SUBTOTAL since it will always be 100%.

As far as the TOTAL columns go, i'm not sure what you are trying to do with the COMPUTES. Are you trying to create a total for the row?

I also trimmed down your code a bit since you have unnecessary code. What you created is a multi-verb request and it is not necessary to do that. See this code below.

DEFINE FILE CAR
SALESD/D10 = SALES;
END

TABLE FILE CAR
SUM
SALESD AS 'Sales'
PCT.SALESD/D6.2% WITHIN COUNTRY WITHIN SEATS AS '%'
BY COUNTRY NOPRINT
BY CAR
ACROSS SEATS
COMPUTE XTOT_SALESD/D15 = C1; AS 'Total'
COMPUTE PCT/D12.2 = C2; AS '%'

ON COUNTRY SUBHEAD
"COUNTRY: <COUNTRY"
ON COUNTRY SUBFOOT
" "
ON COUNTRY SUBTOTAL SALESD AS 'TOTAL'
END

Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011

Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003

Ignored post by mgrackin posted

Expert

posted

Mickey,

Thanks for the response.

The "row total" or "across total" should be just that - a total of the row with the percentage recalculated.

For example:

England Jaguar total should be 12,000 with 100% - both total columns are showing zero.

Italy Alfa Romeo total should be 30, 200 with 100% - the total columns are showing the values of Seats=2 only - (column C1 I guess).

W Germany Audi total should be 7,800 with 8.84%
W Germany BMW total should be 80,390 with 91.16%

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

susannah

Expert

posted

I can tell you how I cheat...
i do my ACROSS calculations up front, in columns 1 and 2 with double verbs and noprints;
Handy when the ACROSS summary calculation is complex and not just a total.
SUM SALES NOPRINT
COMPUTE WHATEVER/... NOPRINT
BY COUNTRY
SUM SALES ACROSS CAR
AND COMPUTE TOTAL1/I8S=C1;
AND COMPUTE TOTAL2/...=C2;
BY COUNTRY
END
not elegant, but it works. and its not dependent upon the knowing the number of across items.

In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

Posts: 3811 | Location: Manhattan | Registered: October 28, 2003

Ignored post by susannah posted

j.gross

Virtuoso

posted

Elegant, but works as advertised only when the BY and ACROSS are properly nested. For example, in --

TABLE FILE CAR
WRITE MAX.WHEELBASE   
  BY COUNTRY
WRITE MAX.WHEELBASE   
  BY COUNTRY 
  ACROSS BODYTYPE 
  COMPUTE MAX_WB/D6.1=C1;
END

-- the COMPUTE produces a row-summary column, whereas in

TABLE FILE CAR
WRITE MAX.WHEELBASE   
  BY COUNTRY
WRITE MAX.WHEELBASE   
  ACROSS BODYTYPE 
  BY COUNTRY 
  AND COMPUTE MAX_WB/D6.1=C1;
END

it produces a subfoot.

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

mgrackin

Virtuoso

posted

Francis,

The following code will get you everything EXCEPT for a proper GRANDTOTAL. If you can live without the GRANDTOTAL then this should do it for you.

DEFINE FILE CAR
SALESD/D10 = SALES;
END
-***************************************
TABLE FILE CAR
SUM SALESD NOPRINT
BY COUNTRY NOPRINT
SUM SALESD NOPRINT
BY COUNTRY NOPRINT
BY CAR
SUM SALESD AS 'Sales'
PCT.SALESD/D6.2 WITHIN COUNTRY WITHIN SEATS AS '%'
BY COUNTRY NOPRINT
BY CAR
ACROSS SEATS
COMPUTE XTOT_SALESD/D15 = C2; AS 'Total'
COMPUTE PCT/D12.2 = C2 / C1 * 100; AS '%'
-***************************************
ON COUNTRY SUBHEAD
"COUNTRY: <COUNTRY"
ON COUNTRY RECOMPUTE AS 'TOTAL'
ON TABLE NOTOTAL
END

Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011

Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003

Ignored post by mgrackin posted

Expert

posted

Mickey, thanks for the post. I'll try to apply this to my program.

I don't understand why we have to go through pyrothecnics like this to make PCT., ACROSS totals, etc to work properly.

I'd give up a lot of bells and whistles that are added to the product every few months for some core language upgrading.

Thanks.

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

mgrackin

Virtuoso

posted

Francis,

What is so difficult about your request is that you want the context of the PCT. calculation to change based on its position in the report (on a single row, in the row-total, on a subtotal line, or on a grand total line). The RECOMPUTE command does this nicely to COMPUTE fields but not to the PCT. SUBTOTAL and RECOMPUTE will only aggregate the values of the PCT. field. In later releases of WebFOCUS you can use some prefix operators in SUBFOOT lines to do something like you are trying to do but PCT. is not one of the prefix operators available.

Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011

Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003

Ignored post by mgrackin posted

Expert

posted

What I would like for Christmas, then, is an intelligent PCT prefix operator.

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

Ignored post by Francis Mariani posted

MacLonghorn

Platinum Member

posted

Digging up an old thread...

I've been able to RECOMPUTE my PCT column in "ON TABLE" fashion. But I have a problem: the output is PDF and I'm using this output in a coordinated PDF. I'm using MERGE=ON which pushes the total onto a separate page.

Any ideas on a workaround?

Thanks.

Mark
WF 7.6 Windows

Posts: 150 | Registered: July 26, 2007

Ignored post by MacLonghorn posted

Expert

posted

I haven't tried this, but did you try setting PRINTPLUS to ON? I don't have a clue if this will help.

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