Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report 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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report 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: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders