Start
Features at a Glance
Versions
Roadmap
License
Live demo
Download
Learn more
Author

Nested reports

Oracle APEX dynamic action plugin v1.0

The plugin is dynamic action plugin implementing nested reports within APEX classic reports. Scope of data, data appearance and behavior is customizable within the plugin attributes.

Features at a Glance

Compatibility with Oracle APEX

The plugin is compatible with Oracle APEX starting from version 5.0.

Browser support

The plugin was tested under three major browsers: Internet Explorer 10+, Firefox, Chrome.

Features

Fetched data is defined as SQL query
Fetched rows can be limited by values presented in cells within classic report
The plugin can be bound to TD, TR and jQuery selector within classic report
The plugin supports four operating modes (default callback, default template, custom template, custom callback)
Custom template (Mustache library)
Default callback behavior and appearance is customizable with 8 attributes (cache results, collapse expanded row, max height, slide animation, loading indicator, border and background colors)
Support for custom JavaScript to embed results within APEX page

Versions

This is the first version of the plugin. No version history is available.

Roadmap

Plugin events on collapsing and expanding row (default callback)
Support for Interactive report
More attributes to customize

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).

Live demo

Demo presents three examples of different configuration of the plugin. Examples show possible configuration modes: default template, custom template, custom callback.

click on the icon to display ordered items list
click on the "Customer" cell to display customer detailed information

Multiple rows can be expanded at the same time. Fetched data is rendered with default template (table).

Details	Customer	Product count	Order total	Order date
2	1John Dulles	10	855	2015-07-15
3	2William Hartsfield	5	435	2015-07-26
9	2William Hartsfield	3	265	2015-08-31
4	5Fiorello LaGuardia	5	435	2015-07-28
7	3Edward Logan	7	475	2015-08-17
1	7Eugene Bradley	3	280	2015-07-01
8	4Frank OHare	4	460	2015-08-25
10	7Eugene Bradley	3	280	2015-09-03
5	6Albert Lambert	5	420	2015-08-07
6	3Edward Logan	4	505	2015-08-12

1 - 10

select 
  ROWNUM as "No",
  DPI.CATEGORY as "Category",
  DPI.PRODUCT_NAME as "Product",
  DPI.PRODUCT_DESCRIPTION "Description",
  DPI.LIST_PRICE as "Price"
from 
  DEMO_ORDER_ITEMS DOI
left join
  DEMO_PRODUCT_INFO DPI
on
  DOI.PRODUCT_ID = DPI.PRODUCT_ID
where 
  order_id = #ORDER_ID#

select
  CUST_FIRST_NAME||' '||CUST_LAST_NAME as "Customer name",
  CUST_STREET_ADDRESS1||', '||CUST_CITY||' '||CUST_POSTAL_CODE as "Adress",
  NVL(CUST_EMAIL, 'Not provided') as "E-mail",
  PHONE_NUMBER1 as "Phone number",
  CREDIT_LIMIT as "Credit"
from
  DEMO_CUSTOMERS
where
  customer_id = #CUSTOMER_ID#

Only one row can be expanded at the same time - expanding row results in collapsing expanded rows. Fetched data are rendered with custom template (Mustache).

To learn more about Mustache templates visit Mustache documentation.

Details	Customer	Product count	Order total	Order date
2	1John Dulles	10	855	2015-07-15
3	2William Hartsfield	5	435	2015-07-26
9	2William Hartsfield	3	265	2015-08-31
4	5Fiorello LaGuardia	5	435	2015-07-28
7	3Edward Logan	7	475	2015-08-17
1	7Eugene Bradley	3	280	2015-07-01
8	4Frank OHare	4	460	2015-08-25
10	7Eugene Bradley	3	280	2015-09-03
5	6Albert Lambert	5	420	2015-08-07
6	3Edward Logan	4	505	2015-08-12

1 - 10

select 
  ROWNUM,
  DPI.CATEGORY,
  decode(
    DPI.CATEGORY,
    'Womens', 'fa-female',
    'Mens', 'fa-male',
    'Accessories', 'fa-paperclip',
    'fa-question'
  ) CATEGORY_ICON,
  DPI.PRODUCT_NAME,
  DPI.PRODUCT_DESCRIPTION,
  DPI.LIST_PRICE
from 
  DEMO_ORDER_ITEMS DOI
left join
  DEMO_PRODUCT_INFO DPI
on
  DOI.PRODUCT_ID = DPI.PRODUCT_ID
where 
  order_id = #ORDER_ID#

select
  CUST_FIRST_NAME||' '||CUST_LAST_NAME as "Customer name",
  CUST_STREET_ADDRESS1||', '||CUST_CITY||' '||CUST_POSTAL_CODE as "Adress",
  NVL(CUST_EMAIL, 'Not provided') as "E-mail",
  PHONE_NUMBER1 as "Phone number",
  CREDIT_LIMIT as "Credit"
from
  DEMO_CUSTOMERS
where
  customer_id = #CUSTOMER_ID#

Fetched data are embedded in bootstrap popover plugin. Bootstrap popover functionality is provided as custom JavaScript code within the plugin. Fetched data are rendered with custom template similar to template in Custom template example.

Details	Customer	Product count	Order total	Order date
2	1John Dulles	10	855	2015-07-15
3	2William Hartsfield	5	435	2015-07-26
9	2William Hartsfield	3	265	2015-08-31
4	5Fiorello LaGuardia	5	435	2015-07-28
7	3Edward Logan	7	475	2015-08-17
1	7Eugene Bradley	3	280	2015-07-01
8	4Frank OHare	4	460	2015-08-25
10	7Eugene Bradley	3	280	2015-09-03
5	6Albert Lambert	5	420	2015-08-07
6	3Edward Logan	4	505	2015-08-12

1 - 10

select 
  ROWNUM,
  DPI.CATEGORY,
  decode(
    DPI.CATEGORY,
    'Womens', 'fa-female',
    'Mens', 'fa-male',
    'Accessories', 'fa-paperclip',
    'fa-question'
  ) CATEGORY_ICON,
  DPI.PRODUCT_NAME,
  DPI.PRODUCT_DESCRIPTION,
  DPI.LIST_PRICE
from 
  DEMO_ORDER_ITEMS DOI
left join
  DEMO_PRODUCT_INFO DPI
on
  DOI.PRODUCT_ID = DPI.PRODUCT_ID
where 
  order_id = #ORDER_ID#

select
  CUST_FIRST_NAME||' '||CUST_LAST_NAME as "Customer name",
  CUST_STREET_ADDRESS1||', '||CUST_CITY||' '||CUST_POSTAL_CODE as "Adress",
  NVL(CUST_EMAIL, 'Not provided') as "E-mail",
  PHONE_NUMBER1 as "Phone number",
  CREDIT_LIMIT as "Credit"
from
  DEMO_CUSTOMERS
where
  customer_id = #CUSTOMER_ID#

Download

Please click on the button below

Installation guide

After downloading and unpacking the zip archive please proceed with following steps:

Select application you want to install plugin
Go to Shared Components > Plugin-ins
Click import button
Choose installation file and proceed with installation steps.

Usage guide

Create classic report
Create dynamic action
1. When = Click
2. jQuery Selector = td[headers="COLUMN_NAME"]
Create action "Pretius APEX Nested reports [Plug-In]"
1. Customize settings according to your needs
2. Set affected Elements to the report created in step 1
Test the plugin

Learn more

Blog post Pretius APEX Nested Reports Plugin with Bootstrap popover

About the author

Bartosz Ostrowski

@bostrowsk1
bostrowski@pretius.com

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.

Nested reports

Oracle APEX dynamic action plugin v1.0

Features at a Glance

Compatibility with Oracle APEX

Browser support

Features

Versions

Roadmap

License

Legal Disclaimer

Live demo

Default template

Nested reports - SQL queries

Order list

Customer

Custom template

Nested reports - SQL queries

Order list

Customer

Custom callback

Nested reports - SQL queries

Order list

Customer

Download

Installation guide

Usage guide

Learn more

About the author