Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Using XLSX FORMULA for RECAP
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Using XLSX FORMULA for RECAP
 Login/Join
 
Virtuoso
posted
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 :
 
Posts: 1649 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2223 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Master
posted Hide Post
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
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 485 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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 :
 
Posts: 1649 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Using XLSX FORMULA for RECAP

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.