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     How to create 2 new column in a fex using across

Read-Only Read-Only Topic
Go
Search
Notify
Tools
How to create 2 new column in a fex using across
 Login/Join
 
DKWAN
Silver Member
posted
A user sent this kind of request in, but we have a problem to figure out how to do that. Any idea will help us.
Thanks.

A report is needed as below

STATUS 2002 2003 2004 2005 2006 CHANGE GROWTH
NEW 9% 10% 10% 12% 12%
10 22 33 55 60 5 10%
.
.
.
TOTAL 100% .. ...
422 550 .... .. 500 600 100 20%


Our problem is how to get the Change and Growth column.
We current using a fex
Sum pct.cntx/d5.1% as '' over
cnt as ''
by status
across yr
.
.

It works fine but problem is in the NEWLY added
CHANGE, AND GROWTH column.

We can't spin off to a seperate report, since user would like to see the Change immediately



Prod: WebFOCUS 7.1.1 CGI - Self Service - Report Caster,Win2000/IIS
Output: HTML, Excel 2000 and PDF
 
Posts: 36 | Location: LOS ANGELES | Registered: February 01, 2006Report This Post
GamP
Virtuoso
posted Hide Post
When you're NOT using over, you can just use the COMPUTE statement to create new columns to the right of your across fields, by specifiying the compute(s) AFTER your across statement.
But, you are using OVER, which would then mean that the use of compute after across is not possible any more. In that case you'll have to try and find a solution using fml syntax, which I'm not quite familiar with. Maybe there is someone in this forum that does know fml?


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
DKWAN
Silver Member
posted Hide Post
GamP
Thanks so much. The compute works and adds one column successfully. I'll cont to the others.
I still need to figure out how to get the difference between the last 2 col (yrs).

I never used FML before, perhaps someoneelse can show me more. Very apprecipated.



Prod: WebFOCUS 7.1.1 CGI - Self Service - Report Caster,Win2000/IIS
Output: HTML, Excel 2000 and PDF
 
Posts: 36 | Location: LOS ANGELES | Registered: February 01, 2006Report This Post
FrankDutch
Virtuoso
posted Hide Post
FML has a lot of nice functions.
If you use the GUI you will be able to do this, otherwise you have to code it by hand

Try this code

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
'USA'  LABEL CNU1 OVER
BAR AS '-' 
END


And you can also do different calculations for each column.
You can add text lines in between the rows etc..
The formating is a bit more complex.




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
DKWAN
Silver Member
posted Hide Post
Frank
Thanks. I tried, but get an error of
" 0 ERROR AT OR NEAR LINE 20 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC080) WORD OR SYNTAX IN 'FOR' PHRASE (FML OPTION) NOT RECOGNIZED: USA
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

What do you think?



Prod: WebFOCUS 7.1.1 CGI - Self Service - Report Caster,Win2000/IIS
Output: HTML, Excel 2000 and PDF
 
Posts: 36 | Location: LOS ANGELES | Registered: February 01, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
you're missing an OVER before 'USA'


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
FrankDutch
Virtuoso
posted Hide Post
DK

Yes...sorry my mistake..

does it work now?
doe you understand what this program does?

Do you have the GUI (Developer Studio)
If so try to open it in that tool and see how it works.




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
DKWAN
Silver Member
posted Hide Post
FrankDutch
The FML is working fine now. Thanks.
For my problem, since I recieved a sample code from the help line, I decided to follow their soultion. I'm getting the final output done now.
However, I'm going to spend some time on the FML to see how to put all 6 last years data and the change/growth columns of only the last 2 years.
I think I'll get a good FML report soon. Thanks again



Prod: WebFOCUS 7.1.1 CGI - Self Service - Report Caster,Win2000/IIS
Output: HTML, Excel 2000 and PDF
 
Posts: 36 | Location: LOS ANGELES | Registered: February 01, 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     How to create 2 new column in a fex using across

Copyright © 1996-2020 Information Builders