Wednesday, 11 December 2013

Count number of rows returned in APEX report

Often I want to configure an APEX page based on how many rows have been returned in a (typically classic) report region. There are a number of methods for determining this row count, and some have advantages over others.

1) Computation while rendering the page.

This often isn't best for performance & maintenance since you effectively need to do a count(*) over the SQL you already have. 
An advantage is you'll know the count while the page is being rendered - but depending on what you want to happen based on the row count, this isn't imperative.

2) Add substitution strings to Region Footer

If you're using pagination on a classic report you can include substitution strings such as #ROWS_FETCHED#.

You don't necessarily want to see this displayed on the page, so you could use
<div id="emp_count" style="display:none;">#TOTAL_ROWS#</div>
Then interrogate this value using
Trouble is these don't get updated as you paginate through your results, and are usually only handy when operating with one page of results (or no results). Then the "No data found" and "More data found" message attributes can come into play.

3) jQuery function call

Length() returns the number of elements matching your selector. Note size() is deprecated as of jQuery 1.8
This method will work regardless of pagination usage. All you need is to identify something in your report you can count.

In this sample page there are a number of options. There is one button per record, so you could count the number of .uButtonGreen
A safer and faster method would be to nominate a column within my table. So in this case I defined a static region ID for my report region (emp)
, and nominated to count the number of employee name cells.
$s('P6_ROW_COUNT', $('table#report_emp td[headers="ENAME"]').length);
To help decide on my selector I used the browser's inspect element tool
Firefox inspect element
Press the "Show count" button on my sample page to see notification of row count.
Change pagination to see varying results.

I'm really starting to love jQuery.

No comments: