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]How to get aggregation done for a sum of products field in WF

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]How to get aggregation done for a sum of products field in WF
 Login/Join
 
S.G
Member
posted
Hi all,

I am now facing one issue about aggregation in WebFOCUS. Below is the description:

In WebFOCUS report, I would like to add up the value of (QUANTITY * UNIT_PRICE).

WebFOCUS

TABLE FILE TABLE_1
SUM
    COMPUTE FIELD_1/D12.2 = QUANTITY * UNIT_PRICE;
BY CUSTOMER_ID
END


To improve the performance, the aggregation should be done in database level. However when I ran the report with the above code, I got the below log:
  
SQL code generated by WebFOCUS

SELECT 
T1.CUSTOMER_ID,
SUM(T1.QUANTITY),
SUM(T1.UNIT_PRICE)

FROM TABLE_1 T1
GROUP BY 
T1.CUSTOMER_ID
ORDER BY
T1.CUSTOMER_ID


Can you please guide how to execute aggregation for the calculation (QUANTITY * UNIT_PRICE) in database level? Thanks in advance!

This message has been edited. Last edited by: <Emily McAllister>,


WebFOCUS 8105
Windows, All Outputs
 
Posts: 26 | Location: Hefei,China | Registered: March 27, 2016Report This Post
Dave
Master
posted Hide Post
S.G.

this should be basic knowledge. Please consider a course or some education. We're not a paid WebFocus-webcare team. Just peers.

COMPUTE's are executed on the result. Exactly what the SQL shows.

use DEFINE to create row-level calculations
DEFINE FILE TABLE_1
    FIELD_1/D12.2 = QUANTITY * UNIT_PRICE;
END

TABLE FILE TABLE_1
SUM
    FIELD_1
END



And no, you do not have to add QUANTITY or UNIT_PRICE to the SUM statement.

Greets,


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
S.G
Member
posted Hide Post
Hi Dave,

Thank you for the suggestion!

There is huge data in TABLE_1, so DEFINE operation will query and get all data from TABLE_1 into WebFOCUS. I think it will lead to low performance.

Could you please let me know if there is any other method? Thanks!


WebFOCUS 8105
Windows, All Outputs
 
Posts: 26 | Location: Hefei,China | Registered: March 27, 2016Report This Post
WF_IL
Platinum Member
posted Hide Post
Hi

Please note that most relational adapters translate Define to SQL:

 

DEFINE FILE wfretail81/wf_retail_lite
Define_1/D12.2=WF_RETAIL_LITE.WF_RETAIL_SALES.GROSS_PROFIT_US / WF_RETAIL_LITE.WF_RETAIL_SALES.DISCOUNT_US ;
END

TABLE FILE wfretail81/wf_retail_lite
SUM Define_1
BY WF_RETAIL_LITE.WF_RETAIL_PRODUCT.PRODUCT_CATEGORY
END


SELECT
 T7."PRODUCT_CATEGORY",
 SUM((T1."GROSS_PROFIT_US" / T1."DISCOUNT_US"))
 FROM
 ( wrd_wf_retail_sales T1
 LEFT OUTER JOIN wrd_wf_retail_product T7
 ON T7."ID_PRODUCT" = T1."ID_PRODUCT" )
 GROUP BY
 T7."PRODUCT_CATEGORY"
 ORDER BY
 T7."PRODUCT_CATEGORY";


Yours,
Eran
SRL Products

http://www.srl.co.il

 
Posts: 97 | Location: Tel Aviv, Israel | Registered: November 20, 2005Report This Post
Dave
Master
posted Hide Post
...as Eran points out.

It will exactly NOT do that.

Define will translate to, depending on the logic in it, to SQL.

Even IF statement will be converted to CASE. But you have to test all of it. Not all WebFocus statements are translated to SQL. ( depending on your data-provider and adapter I guess ).


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
EricH
Platinum Member
posted Hide Post
S.G,
You are saying that you want the database to perform the COMPUTE due to performance issues (large volume of data). But since there are no WHERE clauses in your queries, you are going to get the same # of rows back regardless of where the math is performed. If you have 5 million CUSTOMER_IDs you will get back 5 million rows. Having the additional column will only introduce trivial extra cycles, most of the work is done retrieving those 5 million rows.

But otherwise, I also agree with Eran's suggestion of converting to DEFINE.
 
Posts: 164 | Registered: March 26, 2003Report This Post
S.G
Member
posted Hide Post
Thanks all! I got it.

I wonder that if there are some documents for the similar technical question to refer?


WebFOCUS 8105
Windows, All Outputs
 
Posts: 26 | Location: Hefei,China | Registered: March 27, 2016Report 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]How to get aggregation done for a sum of products field in WF

Copyright © 1996-2020 Information Builders