Dynamic filter on Interactive Reports

This feature will allow you to select a row on one Interactive Report and add a filter a second Interactive report using the selected value. This can be achieved easily with a Master Detail Page, APEX will use interactive grids for this, but if you want to use two interactive Reports or if your filter value doesn't come from a report, you can use the solution on this post. This is the demo app, if this is what you are looking for, keep reading the blog.

For this example we'll use two interactive reports and one item to store the selected value. In this case we have the Department and Employee Reports, when the user clicks on one department, the employee report will be filtered by the department name.

First, in the Department report, set the following properties on the column you want to use as link:

• Type: Link
• Target Type: URL
• URL: javascript:void(0);
• Link Text: <span class="name-filter">#DNAME#</span>
• Link attributes: data-id=#DNAME#

Here, DNAME is the column name for the Department Name.

We need to set a static ID for the second report. For our example the static ID is “employee”. Even if your source is not other Interactive Report you'll need the static id.

Now create a dynamic action with the following attributes:

• Event Scope: Dynamic
• event: Click
• Selection Type: jQuery Selector
• jQuery Selector: .name-filter

The jQuery Selector has a point at the beginning. This will trigger the dynamic action when an object with class .name-filter is clicked by the user. 

Create a true action of type Set Value with the following attributes:

• Action: Set Value
• set Type: JavaScript Expression
• Javascript Expression: $(this.triggeringElement).parent().data('id')
• Affected Elements: Item(s)
• Item(s): P4_DEPARTMENT

This will set P4_DEPARTMENT with the value of the clicked Department name.

Create another true action with the following attributes:

Action: Execute Server-side Code

PL/SQL code:

declare
 l_region_id   number;
begin
 select max(region_id) into l_region_id 
 from APEX_APPLICATION_PAGE_REGIONS 
 where application_id = :APP_ID and 
       page_id = :APP_PAGE_ID and 
       static_id = 'employee';
        
        
APEX_IR.ADD_FILTER(
       p_page_id       => :APP_PAGE_ID,
       p_region_id     => l_region_id,
       p_report_column => 'DNAME',
       p_filter_value  => :P4_DEPARTMENT, 
       p_operator_abbr => 'EQ', 
       p_report_id     => null);
end;

In the select we'll get the region ID for the employee report. Here we need the static ID assigned to this region.

In the function, We'll ask APEX to add a filter to the column DNAME using the value from the item P4_DEPARTMENT.

And that's it, the employee report will be filtered by the selected Deparment, let's celebrate with Kashiyuka!!! If this has been usefull for you, please let a comment.