Skip to Main Content

Editable Interactive Report

It is known that current APEX versions lack the ability of editing Interactive reports. I want to compensate this by offering the below solution. Here is how you can create editable interactive report using APEX collection. You can modify this sample to work with tables instead of apex_collections. If you are using APEX 3.x, load jQuery library in page template header or page HTML header.

Create page process "On Load - After Header":

IF NOT APEX_COLLECTION.COLLECTION_EXISTS(p_collection_name => 'EMP')
    THEN
    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B(
      p_collection_name => 'EMP',
      p_query => '
        SELECT empno,
          ename,
          mgr,
          sal,
          deptno,
          hiredate
        FROM emp
      '
    );
    END IF;
    

On Demand process SAVE_IR_VALUE:

BEGIN
      APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE(
        p_collection_name => 'EMP',
        p_seq             => APEX_APPLICATION.G_x01,
        p_attr_number     => APEX_APPLICATION.G_x02,
        p_attr_value      => APEX_APPLICATION.G_x03
      );
      htp.prn('OK');
    EXCEPTION WHEN OTHERS THEN
      htp.prn('NOK');
    END;
    

Interactive report from query:

SELECT seq_id,
      c001,
      c002,
      c003,
      c004,
      c005,
      c006
    FROM apex_collections
    WHERE collection_name = 'EMP'
    

Page HTML header:

<style type="text/css">
     .ir-edit{text-decoration:none!important;color:#000!important}
     .edit-img{vertical-align:text-top}
     .whback{background-color:#FFF!important;font-weight:bold!important}
     .whback a{font-weight:bold}
    </style>
    <script type="text/javascript">
    function changeValue(pThis,pSeqId,pColIdx,pValue,pSize,pMaxLength){
     if($(".apexir_WORKSHEET_DATA td input:not(:hidden)").length==0){
      var lSize=$nvl(pSize,20);
      var lMaxLength=$nvl(pMaxLength,30);
      var lCell=$(pThis).parent()
      var lField=$("<input/>",{"type":"text","id":"htmldbir_value_change","value":pValue,"size":lSize,"maxlength":lMaxLength});
      var lOk=$("<img/>",{"src":"#IMAGE_PREFIX#Fndokay1.gif","alt":"Save","height":"16","width":"16","css":{"vertical-align":"text-top"}});
      var lCancel=$("<img/>",{"src":"#IMAGE_PREFIX#FNDCANCE.gif","alt":"Cancel","height":"16","width":"16","css":{"vertical-align":"text-top"}});
      var lRow=$(pThis).parents("tr:eq(0)").children("td")
      var lOld=lCell.children().detach();
      lRow.addClass("whback");
      lOk.click(function(){
       $.ajax({
        url:"wwv_flow.show",
        dataType:"html",
        traditional:true,
        cache:false,
        type:"POST",
        data:{
         p_flow_id:$v("pFlowId"),
         p_flow_step_id:$v("pFlowStepId"),
         p_instance:$v("pInstance"),
         p_request:'APPLICATION_PROCESS=SAVE_IR_VALUE',
         x01:pSeqId,
         x02:pColIdx,
         x03:$v("htmldbir_value_change")
        },
        success:function(d){
         if(d=='NOK'){
          raiseError();
         }else{
          gReport.pull();
         }
        }
       });
      });
      lCancel.click(function(){
       lCell.empty().append(lOld);
       lRow.removeClass("whback");
       $(".edit-img").show();
      });
      $(".edit-img").hide();  
      lCell.append(lField,lOk,lCancel);
     }
     function raiseError(p){
      var lErr=$nvl(p,"Can not save value.");
      $("#htmldbir_value_change").addClass('ui-state-error ui-corner-all fielddatabold');
      alert(lErr);
     }
    }
    </script>
    

Go edit report Column Definition. For the columns you like to edit place this Link Text:

#C002#&nbsp;<img class="edit-img" alt="" src="#IMAGE_PREFIX#ed-item.gif" />
    

Replace #C002# accordingly to the column substitution string.

Link Attributes:

class="ir-edit" onclick="changeValue(this,#SEQ_ID#,2,'#C002#',10,10)"
    

Replace third attribute according to APEX collection attribute number and #C002# accordingly column substitution string.

Change Target to "URL". And place to URL:

javascript:void(0)
    

Download sample application

You can check the working sample on apex.oracle.com

Comments

  • Mintu 5 Feb 2016

    Hi Jari,

    Thanks for replying!

    Is it possible to use gReport in Apex 4 Version?

    gReport only update data in a Report and inorder to insert it in database,we Need a insert procedure right?

    If then how can we realate the process with insert procedure.

    Could you please also help me what changes we have to make to use this application in Apex 5.1. What all changes should be done in Javascript.

    Kindly advise me in this!

  • Jari Laine 2 Feb 2016

    Hi Min,

    This example is created for APEX 3.2 and unfortunately it's not work in APEX 5 without changes.

    If you like get this working on APEX 5, you need modify JavaScript function because there isn't anymore gReport JavaScript object.

    Regards,
    Jari

  • Mintu 1 Feb 2016

    Hi Jari,

    I tried using your code in Apex 5.1 and the update is not working.Only cancel is working. Ihave seen the code in SAVE_IR_VALUE process. Will only that code is enough for update process?

    In your page in HTML Header section, I have seen IMAGE_PREFIX URL for save and cancel,but I was unable to find these Images.Could you please inform me where these Images have you saved?I have only seen edit Image in WORKSPACE_Images.

    Kindly advise me

  • Jari Laine 23 Nov 2015

    Hi Rajesh,

    Thanks for information. I will check and update blog post for APEX 5 modifications.

    I have post also another solution ( Yet another way to create "editable interactive report" part 2 ) that you might like check.

    Regards,
    Jari

  • Rajesh 23 Nov 2015

    Jaris,

    Thanks for showing a great way to make editable report.This demo is not working in Apex 5.0. Do I need to modify anything?

    Regards

    Rajesh

  • Jari Laine 19 Oct 2014

    Hi Bishoy,

    Run page and select columns you like display from interactive report action menu. Then save default layout.

    Regards,
    Jari

  • Bishoy MF 16 Oct 2014

    Thank Jari,

    One more question, why collection display only 7 columns(from Seq_id, c001 to c006) even if i add c007 in the report query it still not in the display?

    Best Regards,

    Bishoy

  • Jari Laine 15 Oct 2014

    Hi Bishoy,

    I have not done that example, but it is "interesting workaround".
    I'm not sure about that example, because there is update to table in on demand process and in after submit process.
    I think one of those is unnecessary.

    You can change both for you needs and see how it works.

    I see that for performance point it might not be best solution to first update one row column to apex_collection and then update all collection rows to table.

    Regards,
    Jari

  • Bishoy MF 15 Oct 2014

    Hi Jari,

    AS you can see here in the application

    Workspace: xxx

    User: xxx

    Password: xxx

    Application Name: Editable Report

    ID: xxx

    Page: x

    he update the real table from the collection.

    If i want to change the table in this application from EMP to another table what i should do?

  • Jari Laine 15 Oct 2014

    Hi Bishoy,

    It depends what you mean by "automatically". APEX do not have build in API that update/insert/delete table rows from apex_collection.

    You can write your own procedure for that.

    Regards,
    Jari