Aggregate Data from Business Objects with Functions

Are you looking to show summary, average, or count across a set of records stored in your Visual Builder's Business Objects? Since you don't get direct SQL access to the underlying tables when working with business objects this can be a bit tricky. In this blog we'll show how object functions can be used to overcome this limitation. For example, we are going to get the total salary for a list of employees that match a specific criteria. 

Here is a video showing the complete end-to-end development steps.

Object Functions

In amny cases, aggregated fields in business objects can be used to summarize data in related objects, for example you can easily define an aggregated field at the department level summarizing the average salary for the employees working in that department. See for example this introduction video. But, if the query you are looking to execute is more complex and doesn't depend on a direct relationship you can use an object function to do the aggregation.

Start by creating a dummy business objects on which we'll define the function. Make sure there is one row in that business object – as we would need to refer to that row's id whenever calling the function.

In the function itself you can access any business object in your application. Simply define a new view based on the business object name. Then you cand define a viewCriteria filter for the view. Note that we are using here the upper function to help us ignore upper/lowercase issues. Also note that we are using the proper way of creating, referencing, and setting values dynamically using bind variables. Using bind variables is one of our groovy best practices, and can prevent situations of SQL injection. 

Then we use the ability to execute aggregation function on the view – while we are using sum, you can also use avg, max, min, and count functions In the same way.

The object function is marked as "Callable by External Systems" – which then exposes it as a POST operation you can invoke like any other REST endpoint. (note that the expected content-type for invoking this end point is – application/vnd.oracle.adf.action+json

def vo = newView('Employees');
addBindVariable(vo,'name','Text');
vo.appendViewCriteria('upper(name) like upper(:name)')
setBindVariable(vo,'name',pname)
retval= vo.count("salary");
if (!retval) 
  {return 0}
else 
  {return retval;}

One thing to be aware of is that this approach is not using the database to do the aggregation, rather it uses the VB middletier layer. If you'll use the BO tracing functionality to look at the SQL being generated you'll see that the SQL fetches all the rows that match the conditionn, and then the BO layers does the aggregation. This might be a heavy operation if you are retreiving too many records.

Calling the Function from UI

Since the function is a POST operation, you should define a new type that maps to the request parameter for it, and then define a variable based on that type. Then you can set the value for the parameter. Since we are aiming to do a wildcard search we'll surround our variable with % on both sides. We can then assign the returned value to a variable in our page and map a UI component on top of it.