

Go  New  Search  Notify  Tools  Reply 
Virtuoso 
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 CENTZERO = 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 ENDThis message has been edited. Last edited by: Wep5622, WebFOCUS 8.1.03, Windows 764/200864, IBM DB2/400, Oracle 11g & RDB, MS SQLServer 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :  

Virtuoso 
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 CENTZERO = 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  

Master 
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 CENTZERO = ON SET CDN = OFF SET PAGENUM = 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, BORDERLEFT=LIGHT,BORDERLEFTCOLOR=BLACK,$ TYPE=DATA, COLUMN=N10, BACKCOLOR=RGB(255 255 128), $ TYPE=DATA, COLUMN=N11, BORDERLEFT=LIGHT,BORDERLEFTCOLOR=BLACK,$ TYPE=DATA, COLUMN=N12, BACKCOLOR=RGB(255 255 128), $ TYPE=TITLE, COLUMN=N9, BORDERLEFT=LIGHT,BORDERLEFTCOLOR=BLACK,$ TYPE=TITLE, COLUMN=N11, BORDERLEFT=LIGHT,BORDERLEFTCOLOR=BLACK,$ TYPE=SUBTOTAL,BACKCOLOR='SILVER',$ TYPE=SUBTOTAL, COLUMN=N9, BORDERLEFT=LIGHT,BORDERLEFTCOLOR=BLACK,$ TYPE=SUBTOTAL, COLUMN=N11, BORDERLEFT=LIGHT,BORDERLEFTCOLOR=BLACK,$ ENDSTYLE ENDThis message has been edited. Last edited by: Hallway,
 

Virtuoso 
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 764/200864, IBM DB2/400, Oracle 11g & RDB, MS SQLServer 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :  

Powered by Social Strata 
Please Wait. Your request is being processed... 