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. 

Analytics can deliver this value within the same SQL. Add the following column to your query
,count(*) over (order by null) cnt
And remove the column from sight of the user with the following CSS.
#emp thead th#CNT
,#emp tbody td[headers=CNT]
  {display:none;}
You can't declaratively set it hidden otherwise you cannot reference it with jQuery.

You can then use this jQuery to return the count.
$('#p1_notes tbody td[headers=CNT]:first').text()
It returns the text value of the CNT column from the first row.

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
$('#emp_count').text()
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.

4 comments:

Marcel said...

Hi, please, can you send the application code?
thanks.

Scott Wesley said...

Sent to the email you supplied (comment suppressed)

Matt P said...

Awesome - thanks for this post - just what I needed.

SamSam said...

Hello,

Thank you for this very interesting post !
Could you give us the "Show count" button please ? I need to make the result in a number field element.

Thanks in advance !