Good Morning. Powers-that-be want me to calculate (subtract) a new value based on something displayed in a SUBHEAD and a subtotal currently displayed in a RECOMPUTE. I've put together similar code as below. When you run the report, half way down the 1st page is product F103. I need to calculate the difference between the 28.00 unit price displayed and the 2667 subtotal displayed for the Unit Sales (ignore the reason why).
Any suggestions please? Thanks in advance!
-*
APP PREPENDPATH IBISAMP
-*
-SET &ECHO = 'ALL';
-*
JOIN PCD IN GGSALES TO PCD IN GGPRODS END
-*
TABLE FILE GGSALES
PRINT
SEQ_NO
CATEGORY
PCD
PRODUCT_DESCRIPTION
PRODUCT
REGION
ST
CITY
STCD
DATE/MDY
UNITS
DOLLARS
BUDUNITS
BUDDOLLARS
BY ST NOPRINT
BY CITY NOPRINT
BY PCD NOPRINT
ON PCD SUBHEAD
"<PCD Cost: <UNIT_PRICE"
ON PCD RECOMPUTE
UNITS
DOLLARS
BUDUNITS
BUDDOLLARS AS 'Total'
HEADING
"Sales Report"
ON TABLE SUBFOOT
"(Sales Report - 01 - 01)"
"(Date Run: <+0>&DATEMDY<+0>)"
WHERE DATE GE 19970801 AND DATE LT 19970901;
ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
FONT='ARIAL',
SIZE=8,
LINES-PER-PAGE=10000,
$
TYPE=TITLE,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
BORDER-TOP-COLOR='GRAY',
BORDER-BOTTOM-COLOR='GRAY',
BORDER-LEFT-COLOR='GRAY',
BORDER-RIGHT-COLOR='GRAY',
BACKCOLOR='SILVER',
STYLE=BOLD,
$
ENDSTYLE
END
This message has been edited. Last edited by: bbollmann,
Brian Bollmann Spartan Light Metal Products WebFocus 7.6.4 / iSeries / WebSphere
March 26, 2009, 08:14 AM
Danny-SRL
Brian,
You will need to alter your code as follows:
TABLE FILE GGSALES
-* This is to have the UNIT_PRICE handy at the PCD level
SUM FST.UNIT_PRICE NOPRINT
BY ST NOPRINT
BY CITY NOPRINT
BY PCD NOPRINT
PRINT
SEQ_NO
CATEGORY
PCD
PRODUCT_DESCRIPTION
PRODUCT
REGION
ST
CITY
STCD
DATE/MDY
UNITS
DOLLARS
BUDUNITS
BUDDOLLARS
BY ST NOPRINT
BY CITY NOPRINT
BY PCD NOPRINT
ON PCD SUBHEAD
"<PCD Cost: <UNIT_PRICE"
ON PCD RECOMPUTE
UNITS
DOLLARS
BUDUNITS
BUDDOLLARS AS 'Total'
-* this calculates the difference between the total UNITS and the UNIT_PRICE, for F103 only
ON PCD RECAP BRIAN/I6=UNITS - FST.UNIT_PRICE;
WHEN PCD EQ 'F103'
The rest is unchanged.
If you don't like to see the result of the calculation the way WF displays it, you can put it into a SUBFOOT.
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
March 26, 2009, 02:57 PM
bbollmann
Excellent. Thanks!
Brian Bollmann Spartan Light Metal Products WebFocus 7.6.4 / iSeries / WebSphere
March 27, 2009, 08:20 AM
bbollmann
Note: For my 'real' version I'm building of this report, I was having trouble with the FST.UNIT_PRICE being duplicated by the number of lines in each group. I removed the BY ST and BY CITY from the first SUM clause and it corrected the problem
Brian Bollmann Spartan Light Metal Products WebFocus 7.6.4 / iSeries / WebSphere
May 21, 2009, 11:21 AM
bbollmann
From Danny-SRL's reply above, can you give suggestion on how to generate a final total on the BRIAN variable generated by the code below
ON PCD RECAP BRIAN/I6=UNITS - FST.UNIT_PRICE;
Brian Bollmann Spartan Light Metal Products WebFocus 7.6.4 / iSeries / WebSphere