Pretius APEX Nested Reports Plugin with Bootstrap popover

31 August 2016, Bartosz Ostrowski

One month ago I have released new Oracle APEX plugin Pretius APEX Nested reports which has been greeted with enthusiasm among APEX developers. Since then,  I have been asked how to implement Bootstrap popover functionality as Custom Callback. In this article I’m sharing detailed instruction (step by step) how to implement bootstrap popover functionality in Oracle APEX classic report.

The plugin

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.

If it is the first time you read about this plugin, visit live demo application.

Business case

In most cases, reporting pages are made of “Master – Details” reports – general data is presented in master report and details can be previewed on another page. But what if business user would rather stay on the same page and is not willing to preview report details in popup or on another page?

Oracle APEX doesn’t support native nested reports. Even if you want to implement it yourself, you have to create separate “on demand” processes to fetch data or custom templates. A lot of stuff to implement just to make one column having nested report. And here comes the plugin.

Imagine the table of customer(s) orders with general data:

  • ORDER_ID
  • CUSTOMER_ID
  • ORDER_DATE

To make it more readable you can join customer details such as first name, last name and maybe address. For orders you can count all orders in shopping cart. If you join all ordered products, the report becomes too complicated to read – each product in shopping cart duplicates information that shouldn’t be presented more than once. Probably it would be nice to have details of each row/column in popover. Business user clicks on the column “Customer” and detailed information about the customer appears without reloading the page.

Setup

Classic Report

You need a report showing general data of something. In this example I will show you implementation of the plugin within report of orders described above (same as in demo application).

If you want to recreate every step in this article  you need model & data. You can create it by installing Sample Database Application from Packaged Apps Gallery or you can download and install prepared SQL scripts. You should end up with new tables filled with data:

  • DEMO_ORDERS
  • DEMO_CUSTOMERS
  • DEMO_ORDER_ITEMS
  • DEMO_PRODUCT_INFO

We want to have general information about customers orders:

  • who has made the order?
  • how many products are in the shopping cart?
  • what is the total cost of the order?
  • when the order was made?
  • what are IDs of details (order and customer) – we need it to make the plugin work but you wont display it

Next step is creating report of orders made by customers

  1. create new APEX page in you application
  2. create new Classic report and enter SQL query:
  1. set the report attribute “Advanced > Static ID”  to “CUSTOMER_ORDERS

After saving and running the page you should see typical APEX report. Nothing interesting yet but you have to start with something.

apex

Main report (view#1)

Columns “Order id” and “Customer id” are IDs and they don’t say much about the orders or customer. Moreover we want column “Customer” to be clickable and to show customer details in bootstrap popover.

To make the report more similar to demo application lets hide the column “Customer id” and lets set the shopping cart icon in “Order id”. Column “Customer” should contain value of CUSTOMER_ID so we can pass its value to the plugin details query – Customer ID will be represented as #CUSTOMER_ID# in details query.

Columns mustn’t be removed from report SQL query. They should be hidden because you need to be able to reference theirs values in other columns.

Add details column with icon

In page designer:

  1. Select column ORDER_ID and set “Type” to “Hidden”.
  2. Create new derived column and:
    1. set “Type” to “Plain text”
    2. set “Heading” to “Details”
    3. change “Column Alignment” to “center”
    4. set “Column Formatting > HTML expression” to

  1. Reorder columns so the new derived colum is on the first place;

Customer details

In page designer:

  1. select column CUSTOMER_ID and set “Type” to “Hidden”.
  2. select column CUSTOMER
    1. change “Aligment” to “center”
    2. set “Column Formatting > HTML expression” to

When you save and run the page, you should see new column “Details” with cart icon in place of “Order id” column:

apex

Main report (view#2)

Now your report is similar to report in demo application and you are almost ready to configure the plugin to use boostrap popover functionality.

Bootstrap files

Before implementing the plugin, we need bootstrap CSS styles and JavaScript library to use popover in APEX application. You can download files from bootstrap (v3.3.7) download page or download already prepared files from here.

If you have decided to download files from bootstrap repository be aware that, the bootstrap CSS styles may override your Universal Theme CSS rules with redundant styles. You need to manually cut redundant styles, so it won’t change UT appearance – prepared files are free of this issue.

After importing bootstrap.min.js and bootstrap.min.css files (as application files) you need to include those files in the application, so the application can use them.

You can do it:

  • on APEX page in section “JavaScript > File URLs” and “CSS > File URLs”
  • in APEX page template in section “JavaScript” and “Cascading Style Sheet” (remember to clone your template so you can edit it)
  • in APEX “User Interfaces”
    1. go to Shared Components > User Interface Attributes
    2. click on edit icon for choosen interface (eg. Desktop)
    3. enter file references in section “JavaScript > File URLs” and “CSS > File URLs”

No matter where you want to include files you have to use #APP_IMAGES# substitution string as prefix for files:

and

After doing it, your application is ready to invoke popovers on DOM elements within your application.

Plugin configuration

Clicking on cell in column “Customer” should fetch selected customer data and present it as popover. To do so, you need to create Dynamic Action with “True” action that will trigger the plugin.

  1. In page designer switch to “Dynamic Actions” tab and create dynamic action
    Name Customer details
    Event Click
    Selection Type jQuery Selector
    jQuery Selector td[headers=CUSTOMER]
    Condition none
    Event scope Dynamic
    Static Container (jQuery Selector) #CUSTOMER_ORDERS
  2. Set dynamic action “True” action as below:
    Attribute Value
    Settings
    Action Pretius APEX Nested Reports [Plug-In]
    Mode Custom template & custom callback
    Details query
    Settings [x] Cache results[x] Loading indicator
    Custom template
    Custom callback
    Affected Elements
    Selection Type Region
    Region Custom callback
    Execution Options
    Fire On Page Load No

Save and run the page. After clicking on cell in column “Customer” you will see unstyled popover.

apex

Main report (view#3) – popover with custom template

Look and feel

The popover pops out but we are missing some styles for Custom template and JavaScript listener for close button added in popover.

In page designer:

  1. Go to “Page Attributes”
    1. add listener in “JavaScript > Function and Global Variable Declaration”
    2. add styles for custom template in “CSS > Inline”

Save and run the page to see how the final result looks like. You should end up with fully styled popover and custom template like the one in the image.

apex

Main report (view#4)

Clicking cell in column “Customer” displays customer details as popover. Each click on cell pops out new popover and popovers could be closed after clicking “X” button.

I hope this article was useful. I will be thankful if you provide feedback on the GitHub.

Ps. If you have any question remember that the plugin contains detailed help within APEX application builder:

Tagged with: , , , , , ,

Hire us!

Pretius is a software development company.
We create web applications using: Java, Oracle DB, Oracle Apex, AngularJS.
Contact us to talk about how we can help you with your software project!