First steps with Pretius APEX Nested Reports

Pretius APEX Nested Report plugin was released almost 3 years ago in August 2016. Despite the fact the usage quide is pretty clear I have recieved countless e-mails regarding basic usage of the plugin. Because of demand for step by step instructions and since I have started this blog, I’ve decided to write it once and for everyone.

If you are not familiar with the plugin, let me do small introduction. The plugin was welcomed very kindly by the #orclapex community. It main purpose is to provide #orclapex developer a handy component showing nested data but without need of creating separate AJAX processes (to fetch data) and Java Script (to embed tesults in report).

Look at the image below, showing the plugin basic usage.

If you want to learn how to create such funcionality keep on reading. I will show you step by step how to implement your first nested report in Oracle APEX.

Install the plugin

Before you can use the plugin you have to install it in your Oracle APEX enviroment. To do so follow these steps:

  1. Go to github repository
  2. Downlaod repository as zip file
  3. Extract files to chosen folder
  4. Install the plugin packages in Orace APEX Schema owner (ie via SQL Workshop)
  5. Install the plugin file via Oracle APEX plugin wizard

The plugin documentation is available at github and as inline application builder help texts.

Create first nested report

You need to prepare Classic Report in which you will display nested report. I will be using emp and dept tables. If you don’t have access to those tables, you can find create/insert SQL statements at Ask TOM site.

Create Classic Report

I assume you have already created page within your application and you have access to emp and dept tables. Now create Classic Report presenting departments:

  1. Create new region called Departments
  2. Change its type to Classic Report
  3. For SQL Query provide
select
  DEPTNO,
  DNAME,
  LOC
from
  dept
  1. Create Virtual Column
  2. Set Target to url and provide
javascript: void(0);
  1. Set Link Text to
<span class="fa fa-search"></span>
  1. Set Link Attributes to
class="details"
  1. Move Virtual Column to first position
  2. Hide column DEPTNO by changing its type to Hidden Column

In page designer your Classic Report should look like on the image below:

Classic Report in Page Designer

After saving and running the page you should see typical Classic Report with newly added Virtual Column represented by magnifier icon.

Right now clicking on magnifier icon won’t do anything – you need dynamic action executing nested report plugin. The plugin will do everything that is needed to fetch data and display it as table based report.

Create Dynamic Action

  1. Create new dynamic action called Show employees of department
  2. In When section:
    1. Change Event to Click
    2. Set Selection Type to jQuery Selector
    3. Set jQuery Selector to .details
  3. In Advanced section:
    1. Change Event Scope to Dynamic
    2. Set Static Container (jQuery Selector) to body
  4. Create True action Pretius APEX Nested Reports [Plug-In]
  5. Provide SQL query for attribute Details query
select
  ENAME as "Name",
  JOB as "Job",
  to_char(HIREDATE, 'YYYY-MM-DD') as "Hire date",
  to_char(SAL, 'FML999G999G999G999G990D00') as "Salary",
  to_char(NVL(COMM, 0), 'FML999G999G999G999G990D00') as "Commission"
from
  emp
where
  deptno = '#DEPTNO#'  

Save and run page. After clicking magnifier icon you will end up with the plugin error as below:

This error occurs because you are missing one configuration step. As the error says the plugin was unable to find value placeholders that has to contain value to be bound within the plugin SQL Query. In this case the plugin couldn’t find value from column DEPTNO.

...
where
  deptno = '#DEPTNO#'  

Each time you use string #COLUMN_NAME# in the plugin SQL Query the plugin expects DOM element with class COLUMN_NAME embeded in row for which the plugin was invoked. To fix the issue you have to embed DEPTNO value in report row using one selector metioned by the plugin.

$('[headers="DEPTNO"]').text() //for nested reports embeded directly in Classic Report
$('[headers="DEPTNO"]').text() //for nested reports embeded directly in Interactive Report (column static ID is required)
$('[headers="NR_1_DEPTNO"]').text() //for nested reports embeded in nested reports
$('span[class*="DEPTNO"]').text() //universal column marker (requires changes in APEX Column Formatting)

The most common way across report types is to provide span element with proper class and value.

<span class="DEPTNO" style="display: none">#DEPTNO#</span>

This span can be embeded in various places such as derivied column Link Text attribute

or as HTML Expression in visible column, for example DNAME column

or if you want you can embed it in Classic Report SQL Query:

select
  DEPTNO,
  '<span class="DEPTNO">'||DEPTNO||'</span>'||DNAME,
  LOC
from
  dept

It doesn’t matter where you will put it as long as its under TR and it is accessible using jQuery selector.

I prefer using HTML expression over Link Text because of readability in APEX page designer.

After this step, save and run the page. The nested report is bound with Classic Report. Each click on magnifier icon shows employees of selected department.

In the near future you can expect more articles about using this plugin. For example I will describe using custom callback, custom template and I will show you how you can use it to embed other APEX page as nested report in classic report.

Ostrowski Bartosz

Oracle APEX Developer @PretiusSoftware

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *