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 where 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.

• Demo
• Roel Hartman: calling an on-demand process from Interactive Report
• Icon Archive NOK image used
• Icon Archive OK image used