percent needs recalculated on column totals...

December 18, 2007, 10:03 AM

<Shane>

Hey all,

I have a dilemma when trying to add column totals to a report. I have 3 columns on a report as you can see in the code below. 2 totals and a percent.

In the column totals, it calculates the totals for the first 2 columns just fine, but when it goes to calculate the percent total, it doesn't recalculate based upon the compute defined in the report. It merely sums the percentages up. How can I get my column totals to display the correct percentage values?

TABLE FILE SALES
SUM
     COMPUTE SALES/D12.2!D = RETAIL_PRICE * UNIT_SOLD; AS 'Sales'
     UNIT_SOLD AS 'Units'
     COMPUTE SALES_PCT/D12.2% = ( UNIT_SOLD / SALES ) * 100; AS '% of Sales'
BY CITY
ON TABLE COLUMN-TOTAL AS 'TOTAL'
END

Results:

CITY        Sales   Units % of Sales 
NEW YORK  $1,764.18  162    9.18% 
NEWARK      $104.16   42   40.32% 
STAMFORD  $4,805.28  376    7.82% 
UNIONDALE   $297.70   65   21.83% 
TOTAL     $6,971.32  645   79.16%

The total for the "% of Sales" column should not be 79.16%. It should be 9.25%.

Shane

In Focus since 1993. WebFOCUS 7.7.03 Win 2003

December 18, 2007, 10:26 AM

<Shane>

Nevermind all...I got it to work by adding ON TABLE SUMMARIZE.

TABLE FILE SALES
SUM
     COMPUTE SALES/D12.2!D = RETAIL_PRICE * UNIT_SOLD; AS 'Sales'
     UNIT_SOLD AS 'Units'
     COMPUTE SALES_PCT/D12.2% = ( UNIT_SOLD / SALES ) * 100; AS '% of Sales'
BY CITY
ON TABLE SUMMARIZE
END

Hope this helps someone else out in the future.

Shane

December 18, 2007, 10:41 AM

Tom Flynn

Hi Shane,

As simplistic as this was based on your example, I have found that MOST of the time, reports are a bit more complex and SUMMARIZE/RECOMPUTE won't suffice. So, I am a true believer in RECAP/SUBFOOT, especially when needing to style.

Here's an example, based on your SALES code:

  
DEFINE FILE SALES
  DUMMY/A1 = ' ';
END
TABLE FILE SALES
SUM
     COMPUTE SALES/D12.2!D = RETAIL_PRICE * UNIT_SOLD; AS 'Sales'
     UNIT_SOLD AS 'Units'
     COMPUTE SALES_PCT/D12.2% = ( UNIT_SOLD / SALES ) * 100; AS '% of Sales'
BY DUMMY NOPRINT
BY CITY 
ON DUMMY RECAP
	XSALES/D12.2!D = SALES;
	XUNITS/I6 = UNIT_SOLD;
	XPCT/D12.2% = (XUNITS / XSALES) * 100;
ON DUMMY SUBFOOT
"Total<XSALES<XUNITS<XPCT"
ON TABLE SET STYLE *
     UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT,$
TYPE=REPORT,
     SQUEEZE=ON, GRID=ON, WRAP=ON, FONT='ARIAL', SIZE=9, STYLE=BOLD,$
-********************************************************************
-* Align the TOTALS
-********************************************************************
TYPE=SUBFOOT,
     BACKCOLOR=RGB(176 196 222), HEADALIGN=BODY, JUSTIFY=RIGHT,$
TYPE=SUBFOOT, 
     ITEM=1, JUSTIFY=LEFT,$
END
-EXIT

Just showing there is another way up the WF mountain.

Happy Holidays! to ALL in Paris...

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe