Yet another way to create "editable interactive report"

When reading OTN Application Express forum, I have seen questions how create interactive report where user can edit data (interactive tabular form ???).
There is various answers for that, but most of solutions utilize APEX_ITEM functions in report query. And that do not work so well with interactive report filtering and other build in features.

Previously I have post example about Editable Interactive Report witch do not use APEX_ITEM.
Idea is that user clicks icon on cell to edit value. Click of icon runs JavaScript that converts cell to editable filed.

Example was originally made using APEX 3.2, but works still on APEX 4.2.

Recently I did find another solution witch I explain here.

Example use APEX_COLLECTION, but you can modify code to work with real table.
Also you should know that IR build in features Control Break and Group By do not work correctly with this solution.

First create Before Regions PL/SQL process using source:

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERYB2 (
  p_collection_name => 'EMP',
  p_query => '
    SELECT e1.empno
      , e1.sal
      , COALESCE(e1.comm, 0) AS comm
      , NULL
      , NULL
      , e1.hiredate
      , NULL
      , NULL
      , NULL
      , NULL
      , e1.ename
      , e1.job
      , e2.ename AS mgr
      , d.dname
    FROM emp e1
    JOIN dept d ON e1.deptno = d.deptno
    LEFT JOIN emp e2 ON e1.mgr = e2.empno 
  '
);

Set process conditionally by PL/SQL expression and place to Expression 1

NOT APEX_COLLECTION.COLLECTION_EXISTS('EMP')

Next create interactive report from query

SELECT seq_id
  ,n001 AS empno
  ,c001 AS ename
  ,c002 AS job
  ,c003 AS mgr
  ,d001 AS hiredate
  ,n002 AS sal
  ,n003 AS comm
  ,c004 AS dname
FROM apex_collections
WHERE collection_name = 'EMP'

Then go edit report columns attributes and change SEQ_ID column Display Text As to Hidden.

Edit other columns and place to Column Formatting HTML Expression

<input type="text" class="ir-edit-input" data-last-value="#EMPNO#" value="#EMPNO#" id="N-#SEQ_ID#-1" maxlength="80" size="20" />

For each column adjust data-last-value and value attributes substitution string according column alias.
You need also modify each column id attribute value to begin with same character as collection column name at upper case and id last number to match collection column number without leading zeros.

Column id will be passed to On Demand process and splitter by - separator. That way we know witch type column is, row and attribute number for update.

Here you can see examples for two first column.

Next create dynamic action

• Name: Edit IR
• Event: After Refresh
• Selection Type: Region
• Region: {select IR region}
• Condition: -No Condition-
• Action: Execute JavaScript code
• Fire On Page Load: True
• Code:

  $(this.triggeringElement)
  .find(".apexir_WORKSHEET_DATA input.ir-edit-input")
  .change(function(e){
   var $lThis=$(this);
   apex.server.process("SAVE_DATA",{
    x01:this.id,
    x02:this.value
   },{
    dataType:"html",
    beforeSend:function(){
     $lThis.addClass("ui-autocomplete-loading").prop("disabled", true);
    },
    success:function(pData){
     $lThis.removeClass("ui-autocomplete-loading").prop("disabled", false);
     if(pData!=="OK"){
      $lThis.val($lThis.data("last-value"));
      alert(pData);
      return false;
     }
     $lThis.data("last-value", $lThis.val());
     return false;
    }
   });
  })
  .filter("[id^=D]")
  .datepicker({
   dateFormat:"dd.mm.yy",
   constrainInput:true,
   changeMonth:true,
   changeYear:true,
   showButtonPanel:true,
   showOn:"both",
   buttonImageOnly:true,
   buttonImage:"#IMAGE_PREFIX#asfdcldr.gif",
   showAnim:"slideDown"
  });
  

• Selection Type: None

Then create On Demand process called SAVE_DATA

DECLARE
  l_date_val DATE;
  l_num_val  NUMBER;
  l_arr      APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN

  l_arr := APEX_UTIL.STRING_TO_TABLE(APEX_APPLICATION.G_X01, '-');

  IF l_arr(1) = 'C' THEN

    APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
      p_collection_name => 'EMP',
      p_seq             => l_arr(2),
      p_attr_number     => l_arr(3),
      p_attr_value      => APEX_APPLICATION.G_X02
    );

  ELSIF l_arr(1) = 'N' THEN

    BEGIN
      l_num_val := TO_NUMBER(APEX_APPLICATION.G_X02);
    EXCEPTION WHEN VALUE_ERROR
    THEN
      HTP.PRN(q'|Couldn't save value. Please check that value is valid number.|');
      RETURN;
    END;

    APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
      p_collection_name => 'EMP',
      p_seq             => l_arr(2),
      p_attr_number     => l_arr(3),
      p_number_value    => l_num_val
    );

  ELSIF l_arr(1) = 'D' THEN

    BEGIN
      l_date_val := TO_DATE(APEX_APPLICATION.G_X02, 'DD.MM.YYYY');
    EXCEPTION WHEN OTHERS
    THEN
      HTP.PRN(q'|Couldn't save value. Please check that value is valid date.|');
      RETURN;
    END;

    APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
      p_collection_name => 'EMP',
      p_seq             => l_arr(2),
      p_attr_number     => l_arr(3),
      p_date_value      => l_date_val
    );

  END IF;
  HTP.PRN('OK');
END;

And for last touch add to page CSS Inline

.ui-autocomplete-loading {
 background: url("#IMAGE_PREFIX#libraries/jquery-ui/1.8/themes/base/images/ui-anim_basic_16x16.gif") no-repeat scroll left center transparent;
 text-indent: 16px;
}
.apexir_WORKSHEET_DATA td {
 white-space: nowrap;
}
.apexir_WORKSHEET_DATA input.ir-edit-input {
 color: inherit;
 text-align: inherit;
}

Now when you run page, you should have "editable IR" where e.g. filter and highlights works.

See working example.