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
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#.
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
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.
- Application Item(s) to pass values to the process
- An Application Process
- 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.
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.
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.