03 March, 2010

Refreshing Flash Charts with AJAX.

NOTE: This post makes use of internal APEX JavaScript that is not part of the standard public API’s. Therefore it is likely not supported, so use at your own risk!

--------------------------

Yesterday, I ran across this post on the OTN APEX Forums. The problem was basically this.

Say you have a chart which you want to refresh each time someone changes a select list value. Sure you could have a select list with submit, but that would cause a redraw of the entire page and, depending on what else might be on that screen, could potentially incur some wait time on behalf of the user.

Another way to do this would be to use the chart’s Asynchronous Refresh feature, but this would cause the chart to refresh on a set interval, not on an event. And to make this worth your while, the interval would have to be so small (seconds) that you would instigate an unnecessary amount of network traffic.

So how do you get a chart to refresh asynchronously? Lets have a look

First, what I’ve done is to create a simple chart that selects the employees and their salaries from the EMP table. Along with it I created the select list upon which it will depend.

Select List SQL

select null link, ENAME label, SAL value1
from "SUMNEVA"."EMP"
where deptno like decode(:P2_DEPT_NO,'%null%','%',:P2_DEPT_NO)

Select List SQL

select DNAME display_value, DEPTNO return_value
from DEPT
order by 1

PastedGraphic2.211Fch1GOGvR.jpg

If this were a select list with submit, then everything would work fine as the newly selected value for the department would get set in session state and the chart would redraw using that value when the page was refreshed. However i want to bypass the page submit and make the chart refresh without redrawing the entire screen. How can this be done?

We can find the initial clues by looking at what APEX does internally when you turn ASYNCHRONOUS REFRESH on for a chart.

Looking at the chart region’s SOURCE in the REGION DEFINITION tab, shows us that APEX uses a number of replacement variables, including #CHART_NAME# and #CHART_REFRESH#.

PastedGraphic3.AxFtSjJyjwb6.jpg

It’s the second of these that initially caught my attention. If you turn Asynchronous Refresh on for the chart and run the page, you can then view the source to see the javascript that is inserted in place of the #CHART_REFRESH# Variable.

PastedGraphic4.vEh8qFlWzfUf.jpg

I won’t go into the details of what this is doing, but the code that does the “heavy lifting” is

apex_RefreshChart (2, '10588887924364140363', 'en-us');

After tracking down the definition of this function, now now that the method has the following signature.

apex_RefreshChart (A, D, C);

A = Current Page Number
D = Name of the chart with the initial ‘c’ removed
C = Language

So we could potentially use this function to our own ends, but to do that we need to know the value of #CHART_NAME#, which is only in context as the chart source is being rendered to the page. What this means to us is that what ever JavaScript function we create needs to be created as part of the rendering of the chart itself.

Editing chart SOURCE in the REGION DEFINITION tab, I added the following JavaScript function below the #CHART_REFRESH# replacement variable.

<script type="text/javascript">
function myRefreshChart(){
var chartName = '#CHART_NAME#';
chartName = chartName.substring(1);
apex_RefreshChart(&APP_PAGE_ID., chartName, 'en-us');
}
</script>

The function basically creates a variable for the chart name and strips off the first character. (This is required for the call to apex_RefreshChart as that function actually prepends the ‘c’ back on to the name parameter). It then calls the apex_RefreshChart function.

Now it would be tempting to think that we could just hook this script to the on_change event for the select list, but there is a problem with that. The chart series query depends on the value of P2_DEPT_NO that is in session state, and if we’re not submitting the page, then the session state value for the item won’t change.

To force the change, we can use APEX’s standard AJAX toolkit; htmldb_get().

As with any AJAX function you need the following things.

  1. Application Item(s) to pass values to the process
  2. An Application Process
  3. A JavaScript function to execute the AJAX call
  4. An event that initiates the function
First we create an Application Item called AJAX_DEPT_NO that will be used to pass the value the user chose in the select list.

Then we create an Application Process called SET_DEPT_NO that does just that.

PastedGraphic5.ofvPSzE57SuC.jpg

Now we need to create the Javascript that will do the AJAX call and then call the myRefreshChart() function. You could easily do this in the HTML HEADER, but I like to put these things in their own HTML REGION with a render sequence of AFTER HEADER.

<script type="text/javascript">
function selectChanged(filter)
{
var get = new htmldb_Get(null,$v('pFlowId'), 'APPLICATION_PROCESS=SET_DEPT_NO',0);
get.add('AJAX_DEPT_NO', filter.value);
var ret = get.get();

myRefreshChart();
}
</script>

If you’re used to using AJAX in APEX, then none of this should be of any surprise to you. We’re using htmldb_Get to call the application process and to send the current value of the select list (represented in the function by the filter variable).

Once the application process is complete we then run the myRefreshChart() function to tell the chart to refresh it’s data.

The last thing we need to do is to put an on_change event on the select list and have it call the selectChange function indicated above. Edit the select list and in the ELEMENT region, enter the following in the HTML Form Element Attributes.

PastedGraphic6.dKBCJtih1T05.jpg

This calls the selectChanged JavaScript function each time the select list changes and passes it a reference to the select list itself.

Put it all together ...

Once everything is in place, we can now run the page and see that every time the select list changes, the graph updated without refreshing the page.

You can see an example of this in the Sumneva workspace on APEX.ORACLE.COM


9 comments:

mnolan said...

Hi Doug

You could even regate the need for the app process by doing the following:

function selectChanged(filter)
{
var get = new htmldb_Get(null,$v('pFlowId'), 'APPLICATION_PROCESS=Do Nothing',0);
get.add(filter.id, filter.value);
var ret = get.get();

myRefreshChart();
}

As you can call a non-existent app process and still update items in session state. One step further then would be to check the type of your parameter "filter" which could be a single item object or an array of items for handling multiple binds in your chart query. Tyler's jApex plugin is a good example of this...

Nice post though, hadn't come across the #CHART_REFRESH# substitution string before.

Thanks
Matt

tscottt said...

Doug!

I've been needing to figure out dynamic graph updates for a long time. Your'e my hero!!!

I've built a treemap (from Javascript INfoVis) on a page that updates and associated line-graph whenever a cell (leaf) is clicked on.

Thank you!

Yann39 said...

Thank you, it helped me ;)

Yann39 said...

Thank you, it helped me a lot :)

I just had to use apex_RefreshFlashChart() instead of apex_RefreshChart() in new APEX 4.0 release ;)

Yann.

Nick69 said...

I believe that in version 5.1 of AnyChart (i.e. the one that comes with APEX 4.0), the correct call to refresh the chart is:
apex_RefreshFlashChart(&APP_PAGE_ID., chartName, 'en-us');

Roel said...

As this is an old post, but still very useful, I'll add an update for APEX 4.
The code presented here works for AnyChart3, but not for version anymore. Then you have to replace "apex_RefreshChart"with "apex_RefreshFlashChart". Otherwise it won't work.
(And you can replace the onchange + Application Process code with a Dynamic Action)

APEX Developer In Texas said...

Doug,
This is Tony Miller, from Houston, TX..

Don't know if you remember talking with on a few projects in the past and on the support forum..

Thought I would drop you a line letting you know I MIGHT re-locating to the Dallas area for a contract with the FDIC.. They are looking for contract APEX developers, and the pay sounds good (better than I am getting now). Also they WANT TO USE Apex, not just tweek a little with it and move along to the next environment, like I am at now..

Just wanted to say hi...

Anonymous said...

Hi Doug,

very interesting post!
I have used your example and after some modifications it is now possible to refresh the chart region from the report selector of the interactive report.
Here is my example: http://apex.oracle.com/pls/otn/f?p=2071:210

Regards

Doug Gault said...

Anonymous ... Very interesting. I'll have to remember that trick!