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] Question for a computed field

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Question for a computed field
 Login/Join
 
EdHou
Gold member
posted
I need one computed field ‘SalesGE10000” only select detail field which GE 10000 then add together and display with other SUM fields. I use this CAR table as an example. For Italy, I need total for dealer_cost, retail_cost, sale_cost and SalesGE10000. So, what I need for Italy is dealer_cost $41,235, retail_cost $51,065, sales $30,200 and “SalesGE10000” $25,400, because $4,800 < $10,000, so this computed field should not includes $4,800.

My real table contains many detail fields not meet the requirement. I really need to know how to add detail fields meet the requirement and display with other summary fields together. Thanks.


Ed
WebFocus 7.6.11

Example need to modify (not meet the requirement):

TABLE FILE CAR
SUM
'CAR.BODY.DEALER_COST'
'CAR.BODY.RETAIL_COST'
'CAR.BODY.SALES'
COMPUTE SalesGE10000/D12.2 = IF CAR.BODY.SALES GE 10000 THEN CAR.BODY.SALES ELSE 0;
BY 'CAR.ORIGIN.COUNTRY'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON

dealer_cost retail sales
ITALY 4,915 5,925 4800
ITALY 5,660 6,820 12400
ITALY 5,660 6,820 13000
ITALY 25,000 31,500 0

This message has been edited. Last edited by: Kerry,


WebFOCUS 8.0.09
WebFOCUS 7.7.05
DBMS: Oracle 11g
all output (Excel, HTML, PDF)
 
Posts: 56 | Registered: June 17, 2011Report This Post
ERINP
Platinum Member
posted Hide Post
Try this:
TABLE FILE CAR
SUM
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
     'CAR.BODY.SALES'
BY 'CAR.ORIGIN.COUNTRY'
WHERE CAR.BODY.SALES GE 10000;
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
EdHou
Gold member
posted Hide Post
Except those summary fields, dealer_cost, retail_cost, and sales, I also need a computed field add all detail sales but not include those detail sales below 10000.

Thank you.


WebFOCUS 8.0.09
WebFOCUS 7.7.05
DBMS: Oracle 11g
all output (Excel, HTML, PDF)
 
Posts: 56 | Registered: June 17, 2011Report This Post
EdHou
Gold member
posted Hide Post
Other words, the sum car.body.sales still is 30,200, but this computed field is 25,400. I still need sales below 10000.


WebFOCUS 8.0.09
WebFOCUS 7.7.05
DBMS: Oracle 11g
all output (Excel, HTML, PDF)
 
Posts: 56 | Registered: June 17, 2011Report This Post
RSquared
Guru
posted Hide Post
Please explain why your code does not work. I tried it and it does seem to work.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
ERINP
Platinum Member
posted Hide Post
EdHou,
You are already on the right track. You just need to create 2 computes fields instead of using one. You need one that totals sales less than 10000 and another that totals greater than 10000. With that being said you will have to remove the WHERE statement other wise you will not get the sales that are less than 10000.


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
Dave
Master
posted Hide Post
Or...

you could
DEFINE FILE CAR
BIGSALES/I6 = IF SALES GE 10000 THEN SALES ELSE 0;
END

TABLE FILE CAR
SUM SALES BIGSALES
BY COUNTRY
END


This works fine in the CAR sample. If in your real database it's also a detail level value it can be done.
( if not, I know a solution also )

G'luck
Dave


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
EdHou
Gold member
posted Hide Post
Thank you everyone. I think I didn’t explain well. For example, for Italy has detail sail 4800, 12400, & 13000. Total sale = 4800 + 12400 + 13000 = 30200. My computed field only need add 12400 + 13000 = 25400.

So, my report should be:

Country Sales SalesGE10000
Italy 30,200 25,400

I can get either 30,200 or 25,400, but I can’t get both.

Also,
If I change codes to

DEFINE FILE CAR
BIGSALES/I6 = IF SALES GE 10000 THEN SALES ELSE 0;
END

TABLE FILE CAR
SUM SALES BIGSALES
BY COUNTRY
END

Or

DEFINE FINE CAR
BIGSALES/I11 = IF CAR.BODY.SALES GE 10000 THEN CAR.BODY.SALES ELSE 0;
END

TABLE FILE CAR
SUM
'CAR.BODY.SALES'
BIGSALES
BY 'CAR.ORIGIN.COUNTRY'

and click run button, then it just can’t run.

Thanks,
Ed


WebFOCUS 8.0.09
WebFOCUS 7.7.05
DBMS: Oracle 11g
all output (Excel, HTML, PDF)
 
Posts: 56 | Registered: June 17, 2011Report This Post
ERINP
Platinum Member
posted Hide Post
Ed,
Hopefully the typo's and the lack of single quotes around the fields is just an error on your part. If the following code does not work on your end please post your version, OS, etc so someone may assist you further. I'm sure "DEFINE FINE CAR" is "DEFINE FILE CAR" in your code and BIGSALES looks like 'BIGSALES' in your second example.Please try the following:

  
DEFINE FILE CAR
BIGSALES/I11 = IF CAR.BODY.SALES GE 10000 THEN CAR.BODY.SALES ELSE 0;
END


TABLE FILE CAR
SUM
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
     'CAR.BODY.SALES'
	 'BIGSALES'
BY 'CAR.ORIGIN.COUNTRY'
WHERE CAR.BODY.SALES GE 10000;
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
EdHou
Gold member
posted Hide Post
With

DEFINE FILE CAR
BIGSALES/I11 = IF CAR.BODY.SALES GE 10000 THEN CAR.BODY.SALES ELSE 0;
END

TABLE FILE CAR
SUM
'CAR.BODY.DEALER_COST'
'CAR.BODY.RETAIL_COST'
'CAR.BODY.SALES'
'BIGSALES'
.. .

then I click run button, but nothing was running.

with

TABLE FILE CAR
SUM
'CAR.BODY.DEALER_COST'
'CAR.BODY.RETAIL_COST'
'CAR.BODY.SALES'
COMPUTE BIGSALES/I11C = IF CAR.BODY.SALES GE 10000 THEN CAR.BODY.SALES ELSE 0;

then I got:

COUNTRY DEALER_COST RETAIL_COST SALES BIGSALES
ITALY 11,320 13,640 25400 25,400
JAPAN 5,512 6,478 78030 78,030

The Bigsales for Italy is correct, but I need Sales = 30200.

Thank you again,

Ed
Window 2003
WebFocus 7611


WebFOCUS 8.0.09
WebFOCUS 7.7.05
DBMS: Oracle 11g
all output (Excel, HTML, PDF)
 
Posts: 56 | Registered: June 17, 2011Report This Post
RSquared
Guru
posted Hide Post
quote:
DEFINE FILE CAR
BIGSALES/I11 = IF CAR.BODY.SALES GE 10000 THEN CAR.BODY.SALES ELSE 0;
END

TABLE FILE CAR
SUM
'CAR.BODY.DEALER_COST'
'CAR.BODY.RETAIL_COST'
'CAR.BODY.SALES'
'BIGSALES'
.. .

then I click run button, but nothing was running.

Get rid of the Quotes around BIGSALES.

S/B
DEFINE FILE CAR
BIGSALES/I11 = IF CAR.BODY.SALES GE 10000 THEN CAR.BODY.SALES ELSE 0;
END

TABLE FILE CAR
SUM
'CAR.BODY.DEALER_COST'
'CAR.BODY.RETAIL_COST'
'CAR.BODY.SALES'
BIGSALES
.. .

then click run button.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
EdHou
Gold member
posted Hide Post
Without the quotes around BIGSALES, I got "No HTML Output!.


WebFOCUS 8.0.09
WebFOCUS 7.7.05
DBMS: Oracle 11g
all output (Excel, HTML, PDF)
 
Posts: 56 | Registered: June 17, 2011Report This Post
Tom Flynn
Expert
posted Hide Post
  
APP PREPENDPATH IBISAMP
-RUN
TABLE FILE CAR
SUM 
'CAR.BODY.DEALER_COST'
'CAR.BODY.RETAIL_COST'
'CAR.BODY.SALES'
COMPUTE BIGSALES/I11C = IF CAR.BODY.SALES GE 10000 THEN CAR.BODY.SALES ELSE 0;
  BY COUNTRY
END
-EXIT


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
EdHou
Gold member
posted Hide Post
Thank you everyone. You all provide very valuable suggestions. Currently, I use ON TABLE HOLD then use HOLD file to sum SALES, & BIGSALES. It seems working, but I need test more.

Ed


WebFOCUS 8.0.09
WebFOCUS 7.7.05
DBMS: Oracle 11g
all output (Excel, HTML, PDF)
 
Posts: 56 | Registered: June 17, 2011Report This Post
RSquared
Guru
posted Hide Post
I ran it without the quotes on bigsales and got this report.

COUNTRY DEALER_COST RETAIL_COST SALES BIGSALES
ENGLAND 37,853 45,319 12000 12000
FRANCE 4,631 5,610 0 0
ITALY 41,235 51,065 30200 25400
JAPAN 5,512 6,478 78030 78030
W GERMANY 54,563 64,732 88190 62540
/* code
DEFINE FILE CAR
BIGSALES/I11 = IF SALES GE 10000 THEN SALES ELSE 0;
END

TABLE FILE CAR
SUM
'CAR.BODY.DEALER_COST'
'CAR.BODY.RETAIL_COST'
'CAR.BODY.SALES'
BIGSALES
BY 'CAR.ORIGIN.COUNTRY'

END

-RUN
/* end code


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
ERINP
Platinum Member
posted Hide Post
RSquared,
I think that is just code tightening between different versions for webFOCUS. The single quotes may not be required for 5.3.2 but in 7.6.11 BIGSALES without quotes throws an error that the field is not recognized.


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
RSquared
Guru
posted Hide Post
Odd, I just ran it on 7.6.11 and it ran and produced a report.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
ERINP
Platinum Member
posted Hide Post
RSquared,
When I tried to run this on Tuesday without the single quotes it threw the error that the Field BIGSALES was not recognized. I run it today and it seems to work just fine with or without the single quotes. Go figure. Perhaps I had a typo while testing this the other day. I will go with your idea of no '' required.


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
EdHou
Gold member
posted Hide Post
RSquared,
I copy your codes and click "run" button, however, no reaction at all from this WebFocus7611. I am not sure why.


WebFOCUS 8.0.09
WebFOCUS 7.7.05
DBMS: Oracle 11g
all output (Excel, HTML, PDF)
 
Posts: 56 | Registered: June 17, 2011Report This Post
EdHou
Gold member
posted Hide Post
RSquared,

I copy your codes and working today. I guess our system had some problems.

Thank you very much and thanks to all of you posted solutions.

Ed


WebFOCUS 8.0.09
WebFOCUS 7.7.05
DBMS: Oracle 11g
all output (Excel, HTML, PDF)
 
Posts: 56 | Registered: June 17, 2011Report 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] Question for a computed field

Copyright © 1996-2020 Information Builders