Wednesday, 21 July 2010

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.

12 comments:

Jko said...

Hi,

I have download you application and the page Refresh 3 works fine.

The problem is that when I try to reproduce it's not working.

So I try to copy your Refresh 3 page inside my application and again, doesn't work.
Any idee?

Many thanks for your help
jko

Anthony Rayner said...

Hi Jko,

Thanks for your feedback.

When you tried to reproduce, did you follow all the steps outlined in the post? If so and this still isn't working as expected, can you provide me access to a workspace on apex.oracle.com where you can reproduce this?

Regarding the copy page issue, which is a separate problem I think. The copy page wizard currently does not change the page ID in the 'Page Items to Submit' IRR report attribute (however it does elsewhere, like in the dynamic action). To get around this, if you go in to the IRR's report attributes on the copied page and amend the page ID in the 2 items, this should work. Can you verify the copied page works for you following this change?

I will also follow up internally with this copy page inconsistency.

Thanks again.
Anthony.

Anthony Rayner said...

Just to follow up with the copy page issue. A bug was created for this (#9941116) and has now been fixed, so will be available in our patchset release.

Anthony.

Anonymous said...

How to get this work if I am allowing a shuttle on department no (to allow selection of multiple dept_No values) and pass that to the interactive query as an IN condition?

Anthony Rayner said...

Anonymous,

Thanks for your question. I have added a new example 'Refresh 4' on the following page that shows how you can use a shuttle item as the report filter:

http://apex.oracle.com/pls/otn/f?p=39830:39

Hope it helps.

Anthony.

Anonymous said...

Thanks for an excellent post. Actually we are still on APEX 3.2 and I have a similar requirement. Can I achieve this same thing in APEX 3.2? Please share your expertise.

Thanks,
-Seenu

Olivier Dupont said...

Hello Anthony,

How can I submit items with a classic report?

For me this example on a clisic report is not working, maybe it's not possible to do this?

Thanks in advance.

Kind regards,
Olivier

Anthony Rayner said...

Hi Olivier,

As mentioned in the post, there is no 'Page Items to Submit' attribute in classic reports currently, so you need to save the item values in session state slightly differently. I eluded to a technique where you could use an additional 'Execute PL/SQL Code' action to set page item values for classic reports. Did you try this?

(Although, as stated in the post, this is inefficient as it issues 2 calls to the server.)

Regards,
Anthony.

Students at Oracle said...

Hi Anthony,

I have discovered your blog by chance (I just read a post in OTN forums and clicked in the link of your profile). Congrats for the blog, it is really useful.

I just wanted to say that I have tried both methods (IR and CR) and they work like a charm. Thanks!

Actually, I tried to refresh an IR some time ago but I couldn't make it. I didn't know the last step (Page Items to Submit).

Thanks again, you have a new reader of your blog!

Anonymous said...

Anthony,

Thanks for your detailed post!
Would there be a difference if I want to refresh a flash chart along with refreshing the report? I just can't find anywhere I can enter the last step into the chart attribute and it does not work without it!

KP said...

Anthony,

Great Blog!! Thanks for this.

My Query...
I have a Line Chart which needs to be updated based on the values coming from a 'Select List(Page Item)' that display years.

My series 1 and 2 are based on Select Statements that have this page item in the where clause.

Select
....
Where fn_yr=NVL(:YR,fn_yr);

I tried to add a 'before refresh' Dynamic Action Event to the Select List and Tried to execute a 'NULL' PL/SQL procedure and then send the Page Item as part of the 'Page Items to Submit' setting. this however does not produce a refresh.
I also tried the same with the 'Change' Event but no luck. I'd appreciate some help please

Anthony Rayner said...

Hi KP,

I'm afraid APEX native charts are not currently supported via 'Refresh' from dynamic actions. Although we plan to include this in our next release, APEX 4.2. When supported, you could trigger the refresh on 'Change' of your select list, then a 'Refresh' action and select the chart as the 'Affected Element' (again, this is not yet available). Additionally, you will need to ensure your select list is listed as a 'Page Item to Submit' under the relevant chart series.

Anthony.

Anthony.