What is great about ADF BC - this framework is very close to the DB and makes data management and analysis operations implementation really easy. There are different ways to implement total and average value calculation. The best way probably is to use Oracle DB Analytic Functions. Using analytic functions minimizes custom Java code, simplifies handling search from Query criteria of from table filter. There is no need to specify GROUP BY in SQL when using analytic functions, this makes easier ADF BC VO implementation.
You are welcome to download sample ADF 11g R2 application - ADFAnalyticFuncApp.zip. Two calculated attributes are added to the Employees VO, both of them are initialized from SQL statement calling analytic function. Here is example for total calculation - check SQL statement for calculated attribute:
Here is for the average:
This is how resulting SQL statement looks for ADF VO with two calculated attributes based on analytic function:
This is all about ADF BC. On ADF UI you need to define in page definition two attributes for calculated fields:
Reference these attributes from table column footer for example:
It works smooth on runtime - search is executed from ADF query, total and average reflect salary data:
If you want, type search criteria into table filter and execute additional filtering - total and average values will be automatically recalculated based on filtered table data:
You are welcome to download sample ADF 11g R2 application - ADFAnalyticFuncApp.zip. Two calculated attributes are added to the Employees VO, both of them are initialized from SQL statement calling analytic function. Here is example for total calculation - check SQL statement for calculated attribute:
Here is for the average:
This is how resulting SQL statement looks for ADF VO with two calculated attributes based on analytic function:
This is all about ADF BC. On ADF UI you need to define in page definition two attributes for calculated fields:
Reference these attributes from table column footer for example:
It works smooth on runtime - search is executed from ADF query, total and average reflect salary data:
If you want, type search criteria into table filter and execute additional filtering - total and average values will be automatically recalculated based on filtered table data:
Nice Article.
ReplyDeleteHi
ReplyDeleteThank you for good Articles
I do like the instruction in blog but when I do not search anything and I want to show all records return null for sum.
how can I have sum of amount column without any criteria???
I tested - if there is not criteria and if all records are returned - total sum is correctly displayed.
ReplyDeleteAndrejus
Hi Andrejus,
ReplyDeleteI've put some analytic function in my view object but directly in sql text in expert mode and totals don't work after filtering or searching...it allways shows same result(Apsolute total). I guess that in this case created attributes are not of calculated item type and adf does not refresh them.
So, solution is to create them view regular add new attribute and put analytic function in their default value SQL expression as you mentioned.
Regards,
Patrik
Hi,
ReplyDeleteI created a read only view object from the database view and was trying to achieve the same. But unfortunately, the view criteria is getting applied as shown below
SELECT * FROM (SELECT RECEIVED_YEAR,
WEEK_NO,
NO_RECEIVED,
NO_STARTED,
ROUND (TO_NUMBER (REPLACE (AVG_HRS_TO_START, ':', '.')),2) AVG_HRS_TO_START,
ROUND (AVG (TO_NUMBER (REPLACE (AVG_HRS_TO_START, ':', '.'))) OVER (ORDER BY RECEIVED_YEAR),2) OVERALL_AVG_HRS,
GOAL_HRS,
ROUND (AVG (NO_STARTED) OVER (ORDER BY RECEIVED_YEAR), 2) AVG_NO_STARTED,
ROUND (AVG (NO_RECEIVED) OVER (ORDER BY RECEIVED_YEAR), 2) AVG_NO_RECEIVED
FROM ORDERS_RCPT_TO_START_VW
ORDER BY RECEIVED_YEAR, WEEK_NO) QRSLT WHERE ( ( ( ( (RECEIVED_YEAR = :RecdYear ) AND (WEEK_NO <= :weekNo ) ) ) ) )
I want the view criteria should be present with in the first select statement. Can you please help me on this
Hi,
ReplyDeleteIs there any way to set the footer for the column groups. I have a requirement where i need to show something in the footer which consist of column groups. Meaning , One salary column has two sub account columns. Header Facet works fine but footer facet doesn't. Please suggest.
Hi,
ReplyDeleteGreat article and it works.
But is there any way to refresh the Totals when the value for a column is changed.
Regards.
In this case, not until you save it to DB and re-execute query.
ReplyDeleteRegards,
Andrejus
Hi,
ReplyDeleteI can't download the project
You can download all old samples from here - https://code.google.com/archive/p/jdevsamples/downloads
ReplyDeleteAndrejus