The page is in modification.

Pretius APEX Nested Reports

Oracle APEX dynamic action plugin

The plugin is dynamic action plugin implementing nested reports within APEX Classic Reports, Interactive Reports and static HTML tables. Scope of data, data appearance and behavior is customizable with the plugin attributes.

License

This plugin is currently available for use in all personal or commercial projects under MIT license. License has been included with this software.

Legal Disclaimer

The program(s) and/or file(s) are supplied as is. The author disclaims all warranties, expressed or implied, including, without limitation, the warranties of merchantability and of fitness for any purpose. The author takes no responsibility, assumes no liability for damages, direct or consequential, which may result from the use of these program(s) and/or file(s).

Documentation

Detailed documentation, usage guide, roadmap ang version changelog is available at Pretius GitHub repository.

Live demo

Demo presents various implementations of the plugin. Example reports and nested reports are based on EMP i DEPT tables. Details for EMP table have been generated randomly.

The plugin contains inline help in APEX Application Builder. Live demo only presents capabilities of the plugin.

Default template & Default callback

ACCOUNTINGNEW YORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON

Custom template & Default callback

107839KINGPRESIDENT-1981-11-175000-
307698BLAKEMANAGER78391981-05-012850-
107782CLARKMANAGER78391981-06-092450-
207566JONESMANAGER78391981-04-022975-
207788SCOTTANALYST75661982-12-093000-
207902FORDANALYST75661981-12-0313000-
207369SMITHCLERK79021980-12-17800-
307499ALLENSALESMAN76981981-02-201600300
307521WARDSALESMAN76981981-02-221250500
307654MARTINSALESMAN76981981-09-2812501400

Extend default template

Default template can be extended with custom HTML in Default template HTML attribute. HTML can preceed or follow report.

To see example click

Employees
ACCOUNTINGNEW YORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON

Sorting data

Default template supports sorting nested report data using JavaScript sort function. Data is sorted depending on type (number or varchar2).

To see example click and click on nested report column to sort nested report.

ACCOUNTINGNEW YORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON

Empty heading & Icon as heading

Default template supports special column aliases to create column heading without content (empty) or with icon as heading content.

To use icon (font awesome) as heading for column in nested report use column alias DERIVIED<XX>_FA_<NAME> (e.g. ENAME as DERIVIED01_FA_USER) where

  • <XX> is 2 digit string eg. 01
  • <NAME> is icon class name with "_" instead of "-" eg. search

To create heading without content use DERIVIED<XX>_EMPTY (e.g. ENAME as DERIVIED01_EMPTY)

To see example click

ACCOUNTINGNEW YORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON

Strech report

If checked report table is streched to 100% width of parent report.

To see example click

StrechedNot streched
ACCOUNTINGNEW YORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON

Loading indicator

If checked loading indicator is shown each time AJAX call fetching data is initialized. Loading indicator style can be set in "Loading indicator style" attribute.

To see example click

Aligned to reportAligned to cellCell icon spinnerReplace cell content
ACCOUNTINGNEW YORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON

Manual events

Default callback supports 4 predefined actions (refresh, expand all, collapse, collapse all) that can be executed using anchors with proper classes. The table of clases can be viewed here. Actions works only when embeded in Extend default template or Custom template using Default Callback.

In the example, the first level of nested report uses Extend default template (preview embeded HTML) to embed 3 actions:

  • Expand all 2nd level nested reports defined with triggering element class ".showPersonal"
  • Collapse all expanded rows
  • Refresh itself

The 2nd level uses Custom template to render data (using custom mustache tamplete). Within template HTML are embeded 3 actions and one button supporting dialog page. Dialog close event is used to refresh nested report (preview custom template HTML).

  • Edit detailed data in modal APEX page and refresh nested report after closing modal. (close dialog dynamic action definition)
  • Collapse itself
  • Collapse itself with given time in anchor attribute duration
  • Refresh itself
ACCOUNTINGNEW YORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON

Plugin event

Whenever the plugin shows, hides or refreshes nested report it triggers Default Callback [Pretius APEX Nested Reports] event. The event can be bound with dynamic actions on document (Selection Type set to Javascript Expression = document) to listen all nested reports events. Or the event can be bound to particular report (Selection Type set to Region) to listen only specific report events.

Event data describes state of currently active nested report. Event data object can be referenced by this.data within Execute JavaScript Code true Action. Event data object is described here.

Events example

In this example dynamic action bound with the plugin event changes looks of triggering element icon and add animaton. Javascript used in dynamic action "Execute Javascript Code" can be viewed here.

ACCOUNTINGNEW YORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON

Cache results

If this option is checked the result of the SQL query is retrieved only once for the specified table cell or jQuery selector.

To see example click

OnOff
ACCOUNTINGNEW YORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON

Set max height

If checked max-height property is added to expanded row. Value must be provided in Set max height attribute.
In this example max-height is set to 100px

To see example click

OnOff
ACCOUNTINGNEW YORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON

Add animation

If checked slideDown and slideUp animation is added while expanding / collapsing row. Animation speed can be changed in plugin Componenet Settings

To see example click

OnOff
ACCOUNTINGNEW YORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON

Collapse expanded

If checked every expanded row will be collapsed each time new row is presented.

To see example click

ONOFF
ACCOUNTINGNEW YORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON

Drilldown

The example present employees hierarchy in EMP table and is handled by only one dynamic action (preview query). Number of nested reports is not limited by the plugin.

 
7839KINGPRESIDENT5000-0
7839KINGPRESIDENT5000-0
7839KINGPRESIDENT5000-0
7839KINGPRESIDENT5000-0
7839KINGPRESIDENT5000-0
7839KINGPRESIDENT5000-0
7839KINGPRESIDENT5000-0
7839KINGPRESIDENT5000-0
7839KINGPRESIDENT5000-0
7839KINGPRESIDENT5000-0
7839KINGPRESIDENT5000-0
7839KINGPRESIDENT5000-0
7839KINGPRESIDENT5000-0
7839KINGPRESIDENT5000-0

Interactive reports

Example #1

The plugin can be bound with Interactive reports. Example below presents Interactive Report with 2 levels of nested reports.

DeptnoDnameLoc
10ACCOUNTINGNEW YORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
  • 1 - 4

Example #2

Example below presents Interactive Report with Control Break. As long as report is HTML table based it is possible to bound the plugin.

Report Settings

Dname : ACCOUNTING
EmpnoEnameJobMgrHiredateSalCommLoc
7839KINGPRESIDENT-1981-11-175000-NEW YORK
7934MILLERCLERK77821982-01-231300-NEW YORK
7782CLARKMANAGER78391981-06-092450-NEW YORK
Dname : RESEARCH
EmpnoEnameJobMgrHiredateSalCommLoc
7566JONESMANAGER78391981-04-022975-DALLAS
7788SCOTTANALYST75661982-12-093000-DALLAS
7902FORDANALYST75661981-12-0313000-DALLAS
7369SMITHCLERK79021980-12-17800-DALLAS
7876ADAMSCLERK77881983-01-121100-DALLAS
Dname : SALES
EmpnoEnameJobMgrHiredateSalCommLoc
7521WARDSALESMAN76981981-02-221250500CHICAGO
7654MARTINSALESMAN76981981-09-2812501400CHICAGO
7844TURNERSALESMAN76981981-09-0815000CHICAGO
7900JAMESCLERK76981981-12-03950-CHICAGO
7499ALLENSALESMAN76981981-02-201600300CHICAGO
7698BLAKEMANAGER78391981-05-012850-CHICAGO
  • 1 - 14

HTML table

This example presents the plugin bound to HTML static table (not generated by APEX). The only difference is that the true action Affected Elements > Selection Type is set to jQuery Selector and Affected Elements > jQuery Selector is set to #staticHTMLTable.

You can inspect table HTML below the report.

Example HTML table with nested report

Department Random column #1
Accouting Random column value
Research Random column value test qwerty

Static HTML

<div class="t-Report t-Report--stretch t-Report--altRowsDefault t-Report--rowHighlight" id="staticHTMLTable">
  <div class="t-Report-wrap">
    <table class="t-Report-report">
      <thead>
        <tr>
          <th class="t-Report-colHead" width="50"><span class="fa fa-search"></span></th>
          <th class="t-Report-colHead">Department</th>
          <th class="t-Report-colHead">Random column #1</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td class="t-Report-cell" align="center">
            <a href="javascript: void(0)" class="htmlTableDept">
              <span class="fa fa-search"></span>
              <span class="DEPTNO hidden">10</span>
            </a>
          </td>
          <td class="t-Report-cell">
            Accouting
          </td>
          <td class="t-Report-cell">
            Random column value
          </td>
        </tr>
        <tr>
          <td class="t-Report-cell" align="center">
            <a href="javascript: void(0)" class="htmlTableDept">
              <span class="fa fa-search"></span>
              <span class="DEPTNO hidden">20</span>
            </a>
          </td>
          <td class="t-Report-cell">
            Research
          </td>
          <td class="t-Report-cell">
            Random column value test qwerty
          </td>
        </tr>
      </tbody>
    </table>
  </div>
</div>

Download

Please click on the button below

 

About the author

Bartosz Ostrowski
Bartosz Ostrowski

Oracle Application Express, PL/SQL and Frontend developer. IT consultant experienced in developing database and web applications using Oracle Application Express.
Oracle APEX enthusiast, plugin developer and active community member.

release 1.0 Set Screen Reader Mode On