Dynamic Action Link with APEX chart

A couple of great things about APEX charts are Dynamic Actions on the region and the easy to use Link option when clicking on a chart element.

However, if you use a dynamic action, it doesn’t know about the fields within the clicked element. If you use the link option, it does allow you to reference the element fields; but your choices are limited to redirections to pages or a URL?

So, how can you pass your selected chart values to a dynamic action? The trick is using the Redirect to URL option which is normally used to branch to a different web page; but can instead invoke javascript. In particular, if you use apex.item().setValue (or $s), you can pass your chart element fields to page items and then dynamic actions triggered by the Change event will fire allowing you to refresh regions, hide or show items, run client and/or server-side code, etc.

To illustrate, I’ll walk through the steps of a small app using the sample SH data set.
I used APEX 20.2 to build and test these steps. Other versions might be slightly different but should have some corresponding version of the steps below.

First create a new application.
On the home page create a charge region called Monthly Sales of type Chart with a SQL Query Source.

  SELECT TRUNC(s.time_id, 'mm') sale_month,
         co.country_name,
         SUM(s.quantity_sold * s.amount_sold) monthly_sales,
         co.country_id,
         TO_CHAR(TRUNC(s.time_id, 'mm'),'yyyy-mm') sale_month_text
    FROM sh.sales s
    INNER JOIN sh.customers cu
          ON cu.cust_id = s.cust_id
    INNER JOIN sh.countries co
      ON co.country_id = cu.country_id
GROUP BY TRUNC(s.time_id, 'mm'),co.country_name, co.country_id;

Under Region Attributes, set

• Type: Bar, Stack: on
• Time Axis Type: Enabled

For the Series set

• Location: Region Source
• Series Name: COUNTRY_NAME
• Label: SALE_MONTH
• Value: MONTHLY_SALES
• Link Type: Redirect to URL
• Target:
  • Type: URL
  • URL: javascript:apex.item('P1_DATE').setValue('&SALE_MONTH_TEXT.');apex.item('P1_COUNTRY_ID').setValue('&COUNTRY_ID.');
  • alternately, for the URL you could use $s instead of apex.item().setValue: javascript:$s('P1_DATE','&SALE_MONTH_TEXT.');$s('P1_COUNTRY_ID','&COUNTRY_ID.');

Create two Hidden items within the region

• P1_DATE
• P1_COUNTRY_ID

Create a new region called “Country Sales by Product” of type Chart with a SQL Query source.

   SELECT p.prod_name,
          co.country_name,
          SUM(s.quantity_sold * s.amount_sold) monthly_sales
     FROM sh.sales s 
      INNER JOIN sh.customers cu
       ON cu.cust_id = s.cust_id
      INNER JOIN sh.countries co
       ON co.country_id = cu.country_id          
     INNER JOIN sh.products p 
       ON p.prod_id = s.prod_id
    WHERE s.time_id >= TO_DATE( :p1_date, 'yyyy-mm') 
      AND s.time_id < ADD_MONTHS(TO_DATE( :p1_date, 'yyyy-mm'), 1)
      AND cu.country_id = TO_NUMBER(:p1_country_id,'999999')
 GROUP BY p.prod_name, co.country_name
 ORDER BY monthly_sales;

Page Items to Submit: P1_DATE,P1_COUNTRY_ID

Under Region Attributes set

• Type: Bar
• Legend: Show On

For the Series set

• Location: Region Source
• Series Name: COUNTRY_NAME
• Label: PROD_NAME
• Value: MONTHLY_SALES

Go back to your hidden items, create a Dynamic Action under one of them, but specify both items. The resulting action will be associated with both items.

• Event: Change
• Selection Type: Items(s)
• Item(s): P1_DATE, P1_COUNTRY_ID

Create a True Action

• Action: Refresh
• Selection Type: Region
• Region: Country Sales by Product

Now you can run your application. You should see a stacked bar chart of monthly totals. If you hover over a bar, you can highlight an individual country’s sales activity. If you click on one of the bar segments you’ll set the two hidden items which will trigger the second region to refresh showing the product sales for the chosen month and country.

This is a simplified example for speed and ease of construction. There are, of course, many formatting options you may want to include for a finished app; but hopefully this is sufficient to illustrate the framework you can use in your own applications. Also note, while I used bar charts in both of these examples above, the same Link options exist for other charts thus allowing this method to be used with any chart type.

While these techniques are not difficult; they are not obvious. I hope this saves other developers time and frustration trying to build the same functionality. Questions or suggestions for easier ways to do the same thing are welcome. In particular if there is a more declarative method without having to backdoor the javascript calls that would, of course, be preferred.