Databases Reference
In-Depth Information
We will copy the page of the previous example to page 10 and keep everything we did. We will add
another report which will show the data of the employees. If the manager selects a specific department,
the report only has to show the data for that department. So we add a Classic SQL Report to the page
with the following
SELECT
statement:
select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM
from emp
where deptno = nvl(:P10_DEPTNO, deptno)
To remove the
submit
event of the page when the manager clicks on the pie chart, you need to
change the link in the pie chart. We will set the value of
P10_DEPTNO
dynamically with JavaScript (by using
$s()
, a built-in function in APEX) so no submit happens. The
select
statement of the pie chart becomes:
SELECT 'javascript:$s("P10_DEPTNO",'||d.deptno||')' LINK,
d.dname LABEL,
sum(e.SAL) sal
FROM emp e, dept d
where e.deptno = d.deptno
group by 'javascript:$s("P10_DEPTNO",'||d.deptno||')' , d.dname
ORDER BY d.dname
Now, when the manager clicks on a slice in the pie chart the hidden item will get a value (note that
this value is not yet in session state as it has not been submitted yet, but is available through JavaScript).
The issue now is that the other charts won't drill-down yet as they were not yet refreshed, which
happened automatically before when the entire page was reloaded due to the submit event of the page.
To refresh the other charts, you could set the Asynchronous Update in the Chart Attributes to 5 seconds,
which will refresh the chart every 5 seconds. However, this is not a recommended way of handling the
chart refresh in this scenario, because the refresh would always happen, even if the manager does not
click on a slice. It also doesn't work for the report on the page, as you cannot define an Asynchronous
Update like you could in the chart.
To solve the refresh issue, you use a Dynamic Action in APEX. Our dynamic action will refresh the
two charts and the report. The Dynamic Action will fire whenever the value of the hidden item
P10_DEPTNO
gets changed. This is how our dynamic action is defined:
•
Event: Change
•
Selection Type: Item(s)
Item(s):
P10_DEPTNO
•
•
True Actions
•
Action 1
•
Action: Execute PL/SQL Code
•
Fire When Event Result is: True
•
Fire On Page Load: No
•
PL/SQL Code:
null;
Page Items to Submit:
P10_DEPTNO
•
