I have a user request where they want a formula to be calculated within the resulting XLSX report, so that the calculated value updates when they manipulate the data in the sheet.
The problem I'm having is that the calculation is based on a MIN, MAX and AVE of several rows, hence why I used RECAP for the calculation, but no matter what I try, I seem to get fixed values in my RECAP formula instead of column references.
It there a way around this?
Here's a simplified example using the CAR table:
APP PREPENDPATH IBISAMP
SET CENT-ZERO = ON
SET CDN = OFF
TABLE FILE CAR
SUM
MAX.SEATS NOPRINT
MIN.SEATS NOPRINT
AVE.SEATS NOPRINT
BY COUNTRY NOPRINT
BY CAR
COMPUTE STV/D12.3% = 100 * (MAX.SEATS - MIN.SEATS)/AVE.SEATS; AS 'STV'
SUM
MAX.SEATS
BY COUNTRY NOPRINT
BY CAR
BY MODEL
ON CAR SUBFOOT
"STV: <STV"
ON TABLE PCHOLD FORMAT XLSX FORMULA
ON TABLE SET HTMLENCODE ON
END
This message has been edited. Last edited by: Wep5622,
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
November 05, 2019, 09:09 AM
MartinY
Not 100% sure of what you are looking for but it seems that to have the formula referenced with the column and not the value it needs to be visible columns Also, since you refer the COMPUTE in the subfoot, it only contain the result (value) and not the formula itself To have all displayed, seems to be something such as below
SET CENT-ZERO = ON
SET CDN = OFF
TABLE FILE CAR
SUM MAX.SEATS AS 'Max Seats,Per Car'
MIN.SEATS AS 'Min Seats,Per Car'
AVE.SEATS AS 'Ave Seats,Per Car'
COMPUTE STV/D12.3% = 100 * (MAX.SEATS - MIN.SEATS)/AVE.SEATS; AS 'STV'
BY COUNTRY NOPRINT
BY CAR
SUM MAX.SEATS AS 'Max Seats,Per Model'
BY COUNTRY NOPRINT
BY CAR
BY MODEL
ON CAR SUBFOOT
"STV: <STV"
ON TABLE PCHOLD FORMAT XLSX FORMULA
ON TABLE SET HTMLENCODE ON
END
Then if I change a value in either column B, C or D of the Excel, the E column then change accordingly but not your subfoot which does not contain a formula
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
November 05, 2019, 03:38 PM
Hallway
quote:
I seem to get fixed values in my RECAP formula instead of column references.
It seems like the formulas don't come through when dividing totals by totals. I would submit a case.
Also, as MartinY states, if a column is hidden, then there cannot be a cell reference to it in Excel because that cell doesn't exist in the Excel sheet. So, it will just put in the value of the hidden cell.
One thing that I did notice, is that you are using AVE.SEATS on an integer field. This causes the decimal of the average calculation to be truncated. If you want a true average, I would calculate it as SUM.SEATS/CNT.SEATS
Run the code below and see the differences in calculations
SET CENT-ZERO = ON
SET CDN = OFF
SET PAGE-NUM = OFF
TABLE FILE CAR
SUM
SUM.SEATS WITHIN CAR AS 'SUM,SEATS'
CNT.SEATS WITHIN CAR
MAX.SEATS WITHIN CAR
MIN.SEATS WITHIN CAR
AVE.SEATS/D12.3 WITHIN CAR
COMPUTE AVGSEATS/D12.3=SUM.SEATS/CNT.SEATS; AS 'AVE,SEATS2'
COMPUTE STV/D12.3% = 100 * (MAX.SEATS - MIN.SEATS)/AVE.SEATS;
COMPUTE STV2/D12.3% = 100 * (MAX.SEATS - MIN.SEATS)/AVGSEATS;
BY COUNTRY
BY CAR RECOMPUTE SUM. SUM.SEATS SUM. CNT.SEATS MAX. MAX.SEATS MIN. MIN.SEATS AVE. AVE.SEATS SUM. AVGSEATS SUM. STV SUM. STV2
BY MODEL
ON TABLE PCHOLD FORMAT XLSX FORMULA
ON TABLE NOTOTAL
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/flat.sty,$
TYPE=DATA, COLUMN=N9, BORDER-LEFT=LIGHT,BORDER-LEFT-COLOR=BLACK,$
TYPE=DATA, COLUMN=N10, BACKCOLOR=RGB(255 255 128), $
TYPE=DATA, COLUMN=N11, BORDER-LEFT=LIGHT,BORDER-LEFT-COLOR=BLACK,$
TYPE=DATA, COLUMN=N12, BACKCOLOR=RGB(255 255 128), $
TYPE=TITLE, COLUMN=N9, BORDER-LEFT=LIGHT,BORDER-LEFT-COLOR=BLACK,$
TYPE=TITLE, COLUMN=N11, BORDER-LEFT=LIGHT,BORDER-LEFT-COLOR=BLACK,$
TYPE=SUBTOTAL,BACKCOLOR='SILVER',$
TYPE=SUBTOTAL, COLUMN=N9, BORDER-LEFT=LIGHT,BORDER-LEFT-COLOR=BLACK,$
TYPE=SUBTOTAL, COLUMN=N11, BORDER-LEFT=LIGHT,BORDER-LEFT-COLOR=BLACK,$
ENDSTYLE
END
This message has been edited. Last edited by: Hallway,
Hallway
Prod: 8202M1
Test: 8202M4
Repository:
OS:
Outputs:
November 06, 2019, 04:11 AM
Wep5622
Thanks Hallway, adding the extra columns does seem to do the trick!
And indeed, being able to get column references in formulas on totals directly would be great. That looks like a good candidate for an NFR.
P.S. Good points on the hidden columns and the integer column - in our real scenario that's thankfully a value with precision.
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :