Apex: Defaulting a collapsible region title based on numbers of rows returned in an interactive report

Requirement 

You want to put your interactive report in a collapsable container, and set the title of the region with the total number of rows returned by the report.

That way users can choose whether or not to expand the collapsible region to see the report results, and you can fit multiple reports on a page, each under a collapsible region.

Method

1. Capture your parameters

2. Have a method to ensure that the report only fires when parameters are present

3. Execute  the report

4. Count the rows and dynamically set the title of its host collapsiable region.

1. Capture parameters

These can be whatever you want.

 

Create you interactive report. Set it as a subregion to your collapsible region. Have your IR query use these parameters.

 

 

2. Only fire the report when parameters are present.

Create a hidden  page field called "?SEARCH_ON"

 

Have a search button on your page with 2 dyamic events, search and don't search.

Search will have the following JS expression condition.

Set value will set "?SEARCH_ON" to "Y", then refresh the IR region.

Make sure that your IR query accounts for the "?SEARCH_ON" parameter.

 

"...where :P17_SEARCH_ON = 'Y...."

 

Don't search will have the following JS expression condition

 

 

You can do something like display an alert.

 

3.  Execute the report

This would already have been done with the "Search actions" defined above.

It make take a few seconds to run in the background, depending on how fast your query is.

Note that the next step counts the number of rows, so it is important to define a rpimary report in your application which displays all rows. Otherwise you may just get 15 rows always.

 

 

4.  Count the rows and dynamically set the title of its host collapsiable region

 

Now you want to det the title of your collapsible region. Create a hidden variable to store your total in.

 

Then pick a column in your report that will be display. I chose "Quantity".

 

  Now create a DA "After refresh" of your report. It will have 2 actions.

 

The first action will execute JS Code:

 

$s('P17_STOCK_DESPATCHED', $('.a-IRR-table td[headers="Quantity"]').length);

 

Now set the static ID for your collapsable region.

 

Your second action will execute JS Code:

 $('#stockdespatched .t-Region-title').text('Stock Despatched ('+  apex.item( "P17_STOCK_DESPATCHED" ).getValue() + ")")

 

 Thats' it! Your collapsible region will show the number of rows in it's subregion interactive report.

 

Acknowledgement

http://www.grassroots-oracle.com/2013/12/count-number-of-rows-returned-in-apex.html