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     FML - How to recalulate a COMPUTE in a RECAP?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
FML - How to recalulate a COMPUTE in a RECAP?
 Login/Join
 
Expert
posted
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
I 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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
TABLE 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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
Francis,

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
 
Posts: 14 | Location: Canada, U.S.A., Ivory Coast | Registered: February 02, 2007Report This Post
Expert
posted Hide Post
Merci 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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Michel,
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 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
I just don't get why the FML RECAP doesn't recompute the % Frowner


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
Francis,

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

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Expert
posted Hide Post
Got 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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
Thanks 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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Member
posted Hide Post
Tony 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
 
Posts: 14 | Location: Canada, U.S.A., Ivory Coast | Registered: February 02, 2007Report This Post
Expert
posted Hide Post
Michel,

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 Frowner



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 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Virtuoso
posted Hide Post
Francis,

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. Smiler I got into this thread late.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Expert
posted Hide Post
Mickey,

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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Francis,
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
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Virtuoso
posted Hide Post
wonderful photos Francis

http://www.terlien.nl/frank

take 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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
To 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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
Francis, 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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report 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     FML - How to recalulate a COMPUTE in a RECAP?

Copyright © 1996-2020 Information Builders