21 December 2012

APEX: Dynamic Action in Interactive Report

Yesterday I was showing a colleague how to use a Dynamic Action in an Interactive Report to do an update on the underlying table. To get this to work prior to APEX 4 you would need to write some javascript on the page as well as an Application Process. Mostly I used the method described by Roel Hartman a number of years ago.
Want to take a look at a demo first before the steps to take to make it work? It's right here.
For this example, I will use a simple table named TASKS:
create table TASKS
(
  id              number not null,
  ind_complete    varchar2(1) not null,
  what            varchar2(250) not null,
  complete_before date
);

alter table TASKS
  add constraint TSK_PK
  primary key (id);
alter table TASKS
  add constraint IND_COMPLETE_CHK
  check (ind_complete in ('Y', 'N'));
To mark a task as "Done", create a procedure named MARK_TASK_COMPLETED:
create or replace
procedure mark_task_completed (p_tsk_id in tasks.id%type)
is
begin
   update tasks tsk
      set tsk.ind_complete = 'Y'
    where tsk.id = p_tsk_id
   ;
end mark_task_completed​;​
Seed the table with some sample data:
insert into tasks
   (id
   ,ind_complete
   ,what
   ,complete_before)
   select rownum
         ,'N'
         ,'testing' || to_char(rownum)
         ,sysdate + rownum
     from dual
   connect by level <= 10;
Now that the database side is done, we can turn our attention to APEX. To show images in the report, upload some in the Shared Components section.
The screenshot above has been made in APEX 4.2, so if the image looks "new and unfamiliar", now you know why.
Upload the images, and associate them with the Application that you are working with. The images I used are in the links below and I named them 'ok' for the checkmark and 'nok' for the white cross on a red background. There are some really nice looking icons on the site of IconArchive.
Next create an Interactive Report, using the wizard. For the Query use the following:
select id tsk_id
      ,case ind_complete
       when 'Y' then 'ok'
       when 'N' then 'nok'
       end ind_complete
      ,what
      ,complete_before
  from tasks
In the above statement I used a case statement to translate the indicator to 'ok' or 'nok', matching the names of the images that I uploaded in the previous step.
When you run the report, it will look like:


Next is to change the Ind Complete column into an image and make it clickable. Make the following changes to the column attributes of the Ind Complete column:
The target page will be page 0 (zero), but it could be any page - we are not going to use this for navigation, just to make the image clickable.
The link text used in the image above consists of two parts; the first is the location of the images, #APP_IMAGES# and the second part is the name of the image that we want to display #IND_COMPLETE#. The latter is a neat trick (at least I think so) instead of using a "hardcoded" name for the image, the current value is used (see the query used for the Interactive Report, column IND_COMPLETE).
For the Link Attributes, we set the ID to the current TSK_ID by using the same syntax #TSK_ID# and add a class to which the Dynamic Action will respond - setCompleted.
When you inspect the report at this time (in the browser using something like FireBug), you will see that the image source is replaced just like we wanted:
Before we can add the Dynamic Action, we need a page item where the current (the task that needs to be set completed) TSK_ID is placed for processing. Just add a hidden item to the page (in my case it's called P8_TSK_ID).
Now the final part to make it all come together; add the Dynamic Action
Right click on "Dynamic Actions", choose "Create" from the context menu.
Enter "Mark Tasks as Completed" for the Name:
Make the Dynamic Action respond to the Click event where the Class name is setCompleted (make sure there is a period before the classname, this is the jQuery class selector syntax)
There are several steps to take in this Dynamic Action, the first is to store the current TSK_ID in the hidden item we created earlier. The value that we want to place there can be retrieved with a JavaScript Expression: this.triggeringElement.id;.
"Fire on Page Load" can be unchecked.
Specify the item for the Set Value action, P8_TSK_ID.
The first part of the Dynamic Action is done, but there are some other TRUE actions to complete the whole thing.
Add another TRUE action to the Dynamic Action and have it execute PL/SQL:
To see the changes made to the underlying data, the report needs to be refreshed. Add another TRUE action to do just this: Refresh the Report:
The last step in the TRUE action is to prevent the navigation to Page 0 taking place. This can be accomplished by the Cancel Event action:
Now the report will respond to clicking the image shown.
And that's it.

Please note that on the Demo page, the procedure used to set the IND_COMPLETE is slightly different from the one described here. The procedure used will toggle the value, so you can mark the tasks completed as well as not completed. This is the source code for the procedure:
create or replace procedure mark_task_completed (p_tsk_id in tasks.id%type)
is
begin
   update tasks tsk
      set tsk.ind_complete = case ind_complete when 'Y' then 'N' when 'N' then 'Y' end     
    where tsk.id = p_tsk_id
   ;
end mark_task_completed;​

UPDATE: Adjust the Event Scope

As Hawk (from the comment below) pointed out: I forgot to mention the setting of the Event Scope. Change the setting for Event Scope to Dynamic and all should work.

23 comments:

  1. Hello: Thank you very much for this, but I have an issue. It works the first time the DA fires, but gives error and appears to try to branch to page 0 the second time DA fires. I put it on cloud in case you can look at it: http://apex.oracle.com/pls/apex/f?p=67201:1 I think it might be "fire on page load" settings of the TRUE actions, but could not get it to work. Any advice? Thanks, Hawk

    ReplyDelete
    Replies
    1. Hi Hawk,

      Thanks for your comments.
      To fix this you need to set the "Event Scope" to "Dynamic". Most likely it is "Static" now.
      Navigate to the Dynamic Action in the Page Rendering section, and set the Event Scope accordingly.

      Alex

      Delete
  2. Thank you very much for the quick response. Do you know of trick to get the .pngs printed when using the browser print()?

    ReplyDelete
  3. Hello: Please disregard my .png printing question. My issue was caused by a print stylesheet I created. I modified it and it all works fine now. I cannot seem to put my comment as a reply, so here it is under another comment. Thanks again, Hawk

    ReplyDelete
  4. this is not working for me. The images are not getting replaced. Am I missing something?

    ReplyDelete
    Replies
    1. possibly, it's hard to debug without being able to look at your code.

      Delete
  5. Hi,

    This is a really nice way to move javascript code to dynamic actions. However, I could not get the item value set in the dynamic action to be visible to the PL/SQL. I managed to resolve this with a standard htmldb_Get call using a javascript action. This passes 'this.triggeringElement.id);' via 'get.add(pItem,pValue)' to an empty application level procedure 'BEGIN NULL; END;'.

    It looks as if the value used in PL/SQL is the submitted (database) value of the item (set by get.add) and the 'Set Value' action is simply setting it at the page level. Am I missing something?

    Cheers, Graham (4.2.2)

    ReplyDelete
    Replies
    1. Did you fill in the "Page Items to Submit" (image 11)? For this example I did exactly as described in this blog, and apex.oracle.com is currently on release 4.2.2, so it should work.

      Delete
  6. That's it - just upgraded and submitting page items is getting me every time!

    ReplyDelete
  7. Hi Alex,
    nice idea, I'm going to use that in my app as well.

    One improvement: In the dynamic action I use 'Submit page', rather than 'Refresh'.
    Reason: When I 'Refresh' the Interactive Report, the pagination is reset as well.
    In your demo, try setting 'Rows per page' to 5, then go to rows 6-10.
    Click a OK/NOK button. It works, but the IR is back to rows 1-5.

    Cheers, JP

    ReplyDelete
  8. Hi,

    Can you help me how to save a Interactive report into another tab region which contains all saved reports.When clicked on the report name need to view the saved report.
    Is it possible?

    ReplyDelete
  9. After saving the Interactive report need to add that as a Favourite in another Tab.

    ReplyDelete
  10. Can i bind event handler manually?

    Dynamic actions are working, but this:

    $('#STATIC_ID').on("apexafterrefresh", function () {alert('Boom!')});

    - not. Is there a solution?

    Thanks!

    ReplyDelete
    Replies
    1. it works.. I tested your code (with only changing the STATIC_ID) to the section "Execute when Page Loads" at page level.
      You can try as well: Go to the Demo-page (at the top of the blogpost), use the console (F12) and enter:

      $('#task-report').on("apexafterrefresh", function () {alert('Boom!')});

      When you click on one of the images in the report (thus triggering a refresh of the report) you will see the alert.

      Delete
    2. hmm.. maybe my problem is that we using custom theme, i don't know what else can it be..

      I workarounded it, by looking into widget.interactiveReport..js. After triggering that event apex executes function _Finished_Loading (wich hides loading circle).
      I redefined this function with itself + my handler triggering :) not very beautiful, but i don't see any other solution..

      Delete
    3. I don't understand what is "not working". Is it the refresh itself that is not up to par?

      Does your custom theme include the #REGION_STATIC_ID# for the report template that you're using? An "automatic" refresh can only happen when this attribute is in the report template.

      Delete
    4. Of course, #REGION_STATIC_ID# is in the template. It just div-based, not table.

      Sorry, i forget to say, my problem is in my own application, not in the one in article. I just have APEX 4.2, IR on page and need to execute my js code after refresh.
      "Not working" means if i create Dynamic Action on page wich runs javascript - it works perfectly. If i'm binding my own handler for "apexafterrefresh" event - it doesn't fire and my code is not executed.

      Delete
  11. Hi,

    I am following your example to fit for a classic report. For the PLSQL true action, how do i get the value 'Y' or 'N' to the PLSQL block. I am placing my PLSQL code directly in APEX. Thank you for your time

    ReplyDelete
  12. Very nice! That’s exactly what I am looking for. Thank you!

    ReplyDelete
  13. Hello,

    This looks a really neat method for toggling images, cheers. As such, I've followed your instruction and it seems to work for me, to a fashion!!!! The only changes I have made is to record the row id (e.g. tsk_id) to a collection (via the PL/SQL true action). However, rather than the value being added (e.g. 1,2,3.....100) to column c001, the actual name of the original id column (e.g. #tsk_id#) is being repeatedly added. What am I missing? Oh, and I'm using APEX v 4.1, therefore I am unable to set the event scope to "Dynamic", I only have the options "bind", "live" or "once".

    I'm getting pretty desperate, so your help would be appreciated.....

    ReplyDelete
    Replies
    1. So, you're using a collection as the basis for your report and you're using the APEX_ITEM package to display the content of the collection? Then you should be able to get the actual values, instead of the name of the column.. Can you setup an example on apex.oracle.com?

      Delete
  14. Hello,
    I have created a Dynamic Action, with the property "When page is loaded".
    I am trying to obtain values from a PL/SQL procedure or function, but, the values are charged just when I refresh the page (Using F5 or linking to another page).

    Could you help me?

    What is the best solution, to charge the data from a database when I want to set a textbox with these values at the moment of loading the main page?

    Thanks.

    ReplyDelete
    Replies
    1. For the scenario that you describe, you don't need a Dynamic Action. Create a Page Process (On Load - Before Header) and execute a PL/SQL procedure to populate items on your page.

      Delete