Apex Parameter Report

Report filtering with APEX 4.0, dynamic action style: -

I've just got back from the APEX SIG event in Birmingham. As ever, the event was well organised and attended and it's always good to catch up with the extremely knowledgable and enthusiastic UK APEX community. If you haven't yet attended one of these events then I'd definitely recommend them, you will undoubtedly learn something new, make some great contacts and enjoy the day! The next event is planned for 3rd November at the Oracle City Office in London, so if you're interested in coming along, please see here.

Down to business. During the event I was asked whether dynamic actions in APEX 4.0 can handle Ajax based filtering of report data. The answer is yes, so thought I'd make the most of the train journey home to blog about how. The approach is slightly different depending on whether you're dealing with classic or interactive report regions. In this post, I will detail the steps to achieve this with Interactive Report Regions (IRRs), fully declaratively without coding 1 line of JavaScript.


You can also see this working in my sample application here. This example shows a report on the EMP table, with quick filters available to filter by department or job via Ajax, partial page refresh.

The following steps assume that you have the EMP and DEPT tables in your current schema.


With IRRs, the basic approach involves the following 4 steps:

• Create a page with a report region, ensuring the page item filters are referenced in the SQL.
• Create the page items for filtering.
• Create the dynamic action to fire whenever any of the page item filters change value, to refresh the IRR.
• Define the IRR to save these items values in session state after it is refreshed.

Now let's walk through these steps in detail.

• Create the page
1. Within an application, click the 'Create Page' button.
2. Select 'Report', click 'Next'.
3. Select 'Interactive Report', click 'Next'.
4. For 'Page Attributes', enter 'Employee Report' for 'Page Name' and 'Region Name'. Importantly, you must also ensure that the 'Region Template' is set to some template that contains the #REGION_STATIC_ID# substitution string, otherwise this example will not work (dynamic actions need this ID to be able to perform the refresh). Most of the new themes now default to an appropriate template when creating new IRRs (as defined by the new Theme 'Region Default' for 'Interactive Reports'). However if you're using an old theme then you may need to select an appropriate template (try 'Region without Buttons and Title'). Also make a note of the page number (I'll assume page 1 for these steps) and click 'Next'.
5. For 'Tab Attributes', leave as default and click 'Next'.
6. For 'Enter a SQL SELECT Statement', enter the following query (referencing the page number noted in step 4 when defining the item names) and click 'Next':
   
   
   

   select e.empno,
   e.ename,
   d.dname,
   e.job,
   e.hiredate,
   e.sal,
   e.comm
   from emp e,
   dept d
   where e.deptno = d.deptno
   and e.deptno = nvl(:P1_DEPTNO, e.deptno)
   and e.job    = nvl(:P1_JOB, e.job)

7. Click 'Finish'.
8. For purposes of this example, we're going to switch off the standard 'Search' bar to keep things clean. You can do this by right clicking on the 'Employee Report' in tree view and selecting 'Edit Report Attributes'. Then in the 'Search Bar' region, select 'No' for 'Include Search Bar' and 'Apply Changes'.
If you now click 'Run Page', you'll see the basic report page as been created. We now need to add the page item filters.
• Create the page items
1. Go to edit the page. We'll create these items in a separate 'Quick Filters' region above the IRR. In 'Tree View', right click on the region position where the IRR has been created (by default this is 'Body (3)' and select 'Create'.
2. Select 'HTML' and click 'Next'.
3. Select 'HTML' and click 'Next'.
4. For 'Title', specify 'Quick Filters', for 'Region Template' select 'Report Filter - Single Row' and amend the 'Sequence' to be say 5, so that this appears before the IRR on the page, click 'Next'.
5. For 'Region Source', leave blank and click 'Create Region'. This has created a blank region, that will be the container for our filter items. We'll create 2 filters, to filter the employee report by the job and department columns respectively.
6. In 'Tree View', right click on the 'Quick Filters' region and select 'Create Page Item'.
7. Select 'Select List', click 'Next'.
8. For 'Item Name', enter 'P1_DEPTNO', click 'Next'.
9. For 'Item Attributes', accept defaults and click 'Next'.
10. For 'Settings', accept defaults and click 'Next'.
11. Ensure 'Display Null Value' is 'Yes'.
12. For 'Null Display Value' enter '- Show All -'.
13. Leave 'Null Return Value' leave as blank, this will be treated as a true null by APEX.
14. For 'List of Values Query', enter the following query and click 'Next':
    
    
    

    select dname d,
    deptno r
    from dept
    order by 1

15. For 'Source' accept defaults and click 'Create Item'.
16. Now for the job select list. In 'Tree View', right click on the 'Quick Filters' region and select 'Create Page Item'.
17. Select 'Select List', click 'Next'.
18. For 'Item Name', enter 'P1_JOB', click 'Next'.
19. For 'Item Attributes', accept defaults except for the 'Begin on New Line' attribute. We want to set this to 'No', so that the filters appear on the same line in the 'Quick Filters' region. Click 'Next'.
20. For 'Settings', accept defaults and click 'Next'.
21. Ensure 'Display Null Value' is 'Yes'.
22. For 'Null Display Value' enter '- Show All -'.
23. Leave 'Null Return Value' leave as blank, this will be treated as a true null by APEX.
24. For 'List of Values Query', enter the following query and click 'Next':
    
    
    

    select distinct job d,
    job r
    from emp
    order by 1

25. For 'Source' accept defaults and click 'Create Item'.
If you run the page now, you'll see all the basic page components are created, the regions and the items, but selecting different values from the filters has no effect. We now need to bring these to life.
• Create the dynamic action
1. Go to edit the page. Right click on the 'Dynamic Actions' tree node and select 'Create'.
2. Select 'Advanced' for the 'Implementation'. We need 'Advanced' here because 'Standard' only supports the 'Show', 'Hide', 'Disable' and 'Enable' actions.
3. For 'Name', enter 'QUICK FILTER REFRESH', click 'Next'.
4. For 'When', leave the event as 'Change' and for 'Item(s)', enter 'P1_DEPTNO,P1_JOB'. Leave 'Condition' as 'No Condition'. These selections define the dynamic action will fire whenever either the department or job select list's value changes. Click 'Next'.
5. For 'Action', select 'Refresh' under the 'Component' category in the select list. The 'Refresh' action currently supports IRRs, classic reports, all item types with cascading LOV support and may also support item or region plug-ins, depending on whether the plug-in author has coded the plug-in to support this (the plug-in documentation should state if this is supported). Charts in APEX 4.0 are not yet supported.
6. Ensure the 'Fire on Page Load' checkbox is unchecked, there is no need to refresh the report when the page loads, it's already fresh. Click 'Next'.
7. On the 'Affected Elements' page, we'll define what will be refreshed. Select 'Region' from the 'Selection Type' select list and 'Employee Report' from the 'Region' select list. Click 'Create'.
If you now run the page, you'll notice that this still isn't working as expected. Actually, the dynamic action is firing, the report is being refreshed, but it is not being scoped by the filter selection. The problem is the values for the filter page items are not being saved to session state and are therefore not set when the report's SQL is executed. This is easy to fix.
• Define the IRR to save these items values in session state after it is refreshed
1. Right click on the 'Employee Report' and select 'Report Attributes'.
2. Go the 'Advanced' and for 'Page Items to Submit', enter 'P1_DEPTNO,P1_JOB'. Click 'Apply Changes'.

So that's it, run the page and you will now see the filters are fully functional. Select different jobs and departments and see the report refresh, showing employees scoped by your selections. This example could be easily extended to have different item types used as the filters, not just select lists. A common example would be a slider component where you select a value or range of values, upon which the report is refreshed with rows specific to your selection. I will try and add that to my sample application when I can.

For classic reports, this is slightly more complicated than as outlined above, as there is no 'Page Items to Submit' attribute (yet!!), so you need to save the values in session state in a slightly different fashion. I will try and add that too when I can. (Hint: Instead of defining the IRR 'Page Items to Submit' as detailed in the last bullet, use the 'Execute PL/SQL Code' action within the dynamic action, to fire before the 'Refresh' action. Just leave the code as 'null;' and set the 'Page Items to Submit' attribute to your page items. This will work, but unfortunately issues 2 Ajax calls to do so, which is not optimal.)

Also, if you're interested in seeing more examples of refreshing reports with dynamic actions, I have a couple of others in the sample application:

1. Refresh - Showing an alternative approach for user's to select their 'Saved Reports' in IRRs. Instead of using the default select list in the search region, this has been switched off in favour of a separate report on the left of the page containing the saved reports available to the current user. This report also shows a tooltip for the report description (if defined), when hovering over the report name. The dynamic action is used to refresh this saved report list, so if a user saves a new report or deletes a report, the report list is updated. Again this is all via Ajax and does not require any manual JavaScript coding.
2. Refresh 2 - Showing how report row deletion can be handled via Ajax with dynamic actions. This example does require a couple of places within dynamic actions where a line of JavaScript is required.

To learn more about these examples, please download the application, install in your workspace and have a closer look.