Focal Point
FML - How to recalulate a COMPUTE in a RECAP?
March 14, 2007, 11:54 AM
Francis MarianiFML - How to recalulate a COMPUTE in a RECAP?
How do I get RECAP to recalculate COST in the example below? With the code as it is, the two COST values simply get added.
DEFINE FILE CAR
RETAIL_SALES/D10 = SALES * RETAIL_COST;
COST/D10 = SALES * DEALER_COST;
END
TABLE FILE CAR
SUM
RETAIL_SALES
COST
COMPUTE PROFITPCT/D6.1 = (RETAIL_SALES-COST) / RETAIL_SALES * 100;
FOR
COUNTRY
'ENGLAND' LABEL CN1 OVER
'JAPAN' LABEL CN2 OVER
RECAP TOT = CN1 + CN2; AS 'TOTAL'
END
Thanks.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
March 14, 2007, 12:27 PM
FrankDutchI did not get anything.
Only Italy is in my database. do I have to update the database?
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
March 14, 2007, 12:54 PM
Francis MarianiTABLE FILE CAR
SUM COUNTRY BY COUNTRY NOPRINT
END
gives me
ENGLAND
FRANCE
ITALY
JAPAN
W GERMANY
If you wish, you can run
dblcar.fex in C:\ibi\apps\ggdemo or
loadcar.fex in C:\ibi\apps\ibisamp
to reload the FOCUS DB. You may have to tweak the USE command.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
March 14, 2007, 01:00 PM
mikipagoFrancis,
Essaie ce code :
SET STYLEMODE = FIXED
-*=======
DEFINE FILE CAR
-*=======
BIDON /A1 = ' ';
RETAIL_SALES/D10 = SALES * RETAIL_COST ;
COST /D10 = SALES * DEALER_COST ;
END
-*=======
TABLE FILE CAR
-*=======
SUM RETAIL_SALES
COST
COMPUTE PROFITPCT/D6.1 = (RETAIL_SALES - COST) / RETAIL_SALES * 100;
BY BIDON NOPRINT
ON BIDON RECOMPUTE AS 'TOTAL'
FOR COUNTRY
'ENGLAND' LABEL CN1 OVER
'JAPAN' LABEL CN2
-*ECAP TOT = CN1 + CN2; AS 'TOTAL'
ON TABLE SET PAGE NOLEAD
ON TABLE NOTOTAL
END
-RUN
Résultat :
RETAIL_SALES COST PROFITPCT
------------ ---- ---------
ENGLAND 161,892,000 134,328,000 17.0
JAPAN 251,942,170 214,014,580 15.1
TOTAL 413,834,170 348,342,580 15.8
Amitié,
Michel
WF 8.1.05M/8.2.06,CentOs,Windows,Oracle ,Postgress
March 14, 2007, 01:59 PM
Francis MarianiMerci Michel, mais...
My example wasn't clear enough. I need to use FML for the FOR and RECAP commands. Here is a better example:
DEFINE FILE CAR
RETAIL_SALES/D10 = SALES * RETAIL_COST;
COST/D10 = SALES * DEALER_COST;
END
TABLE FILE CAR
SUM
RETAIL_SALES
COST
COMPUTE PROFITPCT/D6.1 = (RETAIL_SALES-COST) / RETAIL_SALES * 100;
FOR
COUNTRY
'W GERMANY' LABEL CNE1 OVER
'ITALY' LABEL CNE2 OVER
RECAP TOTE = CNE1 + CNE2; AS 'TOTAL EUROPE' OVER
'JAPAN' LABEL CNA1 OVER
'TAIWAN' LABEL CNA2 OVER
RECAP TOTA = CNA1 + CNA2; AS 'TOTAL ASIA' OVER
RECAP TOT = TOTE + TOTA; AS TOTAL
END
I would like my subtotals and my grand total to recalculate the %.
Thanks.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
March 14, 2007, 02:04 PM
Tony AMichel,
quote:
BY BIDON NOPRINT
ON BIDON RECOMPUTE AS 'TOTAL'
You do not require the additional DEFINE, just use ON TABLE RECOMPUTE.
T
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | | |
WebFOCUS App Studio 8.2.06 standalone on Windows 10 | |
March 14, 2007, 02:22 PM
Francis MarianiI just don't get why the FML RECAP doesn't recompute the %
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
March 14, 2007, 02:43 PM
Danny-SRLFrancis,
RECAP in FML doesn't recompute. C'est comme ca. But you can always redo the calculation. See below:
DEFINE FILE CAR
RETAIL_SALES/D10 = SALES * RETAIL_COST;
COST/D10 = SALES * DEALER_COST;
END
TABLE FILE CAR
SUM
RETAIL_SALES
COST
COMPUTE PROFITPCT/D6.1 = (RETAIL_SALES-COST) / RETAIL_SALES * 100;
FOR
COUNTRY
'ENGLAND' LABEL CN1 OVER
'JAPAN' LABEL CN2 OVER
RECAP TOT(1,2) = CN1 + CN2; AS 'TOTAL' OVER
RECAP TOT(3) = (TOT(1) - TOT(2)) / TOT(1) * 100;
END
Regards,
Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
March 14, 2007, 03:06 PM
Francis MarianiGot it, Danny! Thanks!
Seems bl**dy counter-intuitive. One would think that, if, as described in the manual, FML is some kind of sophisticated financial reporting language, it would automate formula recalculating, since that's what financial reports are all about.
It seems strange that you have to code an OVER when the second RECAP is actually recalculating a cell in the same line as the first RECAP.
DEFINE FILE CAR
RETAIL_SALES/D10 = SALES * RETAIL_COST;
COST/D10 = SALES * DEALER_COST;
END
TABLE FILE CAR
SUM
RETAIL_SALES
COST
COMPUTE PROFITPCT/D6.1 = (RETAIL_SALES-COST) / RETAIL_SALES * 100;
FOR
COUNTRY
'W GERMANY' LABEL CNE1 OVER
'ITALY' LABEL CNE2 OVER
RECAP TOTE = CNE1 + CNE2; AS 'TOTAL EUROPE' OVER
RECAP TOTE(3) = (TOTE(1) - TOTE(2)) / TOTE(1) * 100; OVER
'JAPAN' LABEL CNA1 OVER
'TAIWAN' LABEL CNA2 OVER
RECAP TOTA = CNA1 + CNA2; AS 'TOTAL ASIA' OVER
RECAP TOTA(3) = (TOTA(1) - TOTA(2)) / TOTA(1) * 100; OVER
RECAP TOT = TOTE + TOTA; AS 'TOTAL' OVER
RECAP TOT(3) = (TOT(1) - TOT(2)) / TOT(1) * 100;
END
Now to apply this to my real-world report.
Thanks again.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
March 14, 2007, 04:00 PM
FrankDutchThanks Francis
I will do the reload and run your latest solution tomorrow.
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
March 14, 2007, 04:34 PM
mikipagoTony A,
You were right. It's nice to exchange with people like you. Thanks
Regards,
Michel
WF 8.1.05M/8.2.06,CentOs,Windows,Oracle ,Postgress
March 14, 2007, 04:56 PM
Tony AMichel,
A pleasure. It's great that the Forum helps join fellow coders the world over.
Francis,
Think of it as in Excel, in your primary example you only told WF to make a new row of data equal to the sum of the preceding two rows.
As Danny's method shows, giving WF further instructions to then modify the result in column 3 of the total row is the way forward.
As far as the recompute goes, you have to tell WF to recompute at the right time, even with subtotals. I always steer clear of using recompute in combination with FML if I can, prefering to control the contents of a cell by RECAP ing the indivdual cell contents as required.
T
p.s. check your email inbox - I think it's full
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | | |
WebFOCUS App Studio 8.2.06 standalone on Windows 10 | |
March 14, 2007, 05:24 PM
mgrackinFrancis,
Forgive me if I have no clue what the real need is but, glancing at the your code, it looks like it could be (almost) done alot easier with the use of RECOMPUTE.
DEFINE FILE CAR
RETAIL_SALES/D10 = SALES * RETAIL_COST;
COST/D10 = SALES * DEALER_COST;
SORTCONT/A1=DECODE
COUNTRY('W GERMANY' '1' 'ITALY' '1'
'JAPAN' '2' 'TAIWAN' '2' ELSE '3');
CONTINENT/A12=DECODE
COUNTRY('W GERMANY' 'EUROPE' 'ITALY' 'EUROPE'
'JAPAN' 'ASIA' 'TAIWAN' ASIA' ELSE 'AMERICA');
END
TABLE FILE CAR
SUM RETAIL_SALES COST
COMPUTE PROFITPCT/D6.1 = (RETAIL_SALES-COST) / RETAIL_SALES * 100;
BY SORTCONT NOPRINT
BY CONTINENT NOPRINT
ON CONTINENT RECOMPUTE AS 'TOTAL'
BY COUNTRY
WHERE CONTINENT NE 'AMERICA'
ON TABLE RECOMPUTE
END
If this is of no use to you just ignore me.
I got into this thread late.
Thanks!
Mickey
| FOCUS/WebFOCUS 1990 - 2011 |
March 14, 2007, 06:14 PM
Francis MarianiMickey,
My example was a simplified fex using CAR to illustrate what I want to do in a more complicated program.
Cheers,
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
March 15, 2007, 03:25 PM
susannahFrancis,
designwallah.com is just wonderful!
Everyone should go visit it...
| In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID |
March 15, 2007, 05:37 PM
FrankDutchwonderful photos Francis
http://www.terlien.nl/franktake a look here...
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
March 19, 2007, 09:56 AM
Francis MarianiTo me, RECAP in an FML report should behave the same way RECOMPUTE works in a non-FML report. The particular problem I had was an easy one to resolve, I have seen some very ugly stuff going on in an FML report, just to calculate percentages or subtotals... I wonder how easy it is to handle this in the FML GUI...
--------------------------------------------------
Re photos, thanks to both susannah and FrankDutch.
Cheers,
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
March 19, 2007, 10:34 AM
FrankDutchFrancis, I disagree on this.
the advantage you have in FML is that you can do the same buth also a complete different calculation in the (sub)totals.
You can do things you normaly only could in excel, so if you would give that functionality up for just the recompute function, why should you then use the FML.
Frank
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |