How to Refresh a Report Only Once With Multiple Dependant Cascading LOVs
Suppose you have a region with a set of cascading LOVs and report that references both of these items. When either of these items is refreshed, the report should be refreshed. When the parent item is changed it will trigger multiple refreshes of the report. The following example highlights this problem:
{% asset_img overview.png %}
p2_deptno:
select d.dname d, d.deptno r
from dept d
order by dname
p2_empno:
select e.ename d, e.empno r
from emp e
where 1=1
and e.deptno = nvl(:p2_deptno, e.deptno)
order by e.ename
Cascading LOV Parent Item(s): p2_deptno
Report Region
Type: Classic Report
SQL Query:
select empno, ename, deptno
from emp e
where 1=1
and e.deptno = nvl(:p2_deptno, e.deptno)
and e.empno = nvl(:p2_empno, e.empno)
Page Items to Submit: p2_deptno,p2_empno
Next, create a Dynamic Action (DA) that will trigger a refresh on the Report
region each time any item has changed:
{% asset_img dynamic-action.png %}
When p2_deptno
is changed, 3 Ajax requests occur:
Report
region is refreshed (reason: Page items to Submit containsp2_deptno
)p2_empno
LOV is refreshed (reason: Cascading LOV Parent Item(s):p2_deptno
). This triggers another refresh to theReport
region (see next step)Report
region is refreshed (reason: Page items to Submit containsp2_empno
)
The following demo highlights this problem. The report refreshes so quickly the only way to see the problem is to look at the differnet Ajax calls. Note: the order of the Ajax responses may change since it's asynchronous.
{% asset_img cascade-lov-dup.gif %}
If your report runs relatively quick you may not even notice the duplicate refresh. I had a situation where we had five cascading LOVs and the underlying report was refreshed five times and was very noticeable to the users.
Thankfully there's a simple fix to this problem. When a JavaScript (JS) change
event occurs and is triggered "by a user" the JS event e.originalEvent
contains a value. In the above example step 3 was not really triggered by a user, rather triggered by a cascading LOV. In this case the JS event attribute originalEvent
is null.
Using the event attribute originalEvent
we can solve the problem by adding the following Client-side Condition to the DA: this.browserEvent.originalEvent !== undefined
{% asset_img da-condition.png %}
Now when p2_deptno
is modified only two Ajax calls occur (which is correct):
{% asset_img cascade-lov-fix.gif %}
Thanks to Adrian Png for helping me with this.