Single Article

Yet another way to create "editable interactive report"

Category APEX Tricks

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.

EMPNO column html expression

ENAME column html expression

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.

APEX 4.2 editable interactive report

 

See working example.

Comment Button

Comments

  • Zaif 17 Jan 2017  

    Thanks Jari for a reply.

    I was able to fix it by changing apexir_WORKSHEET_DATA to a-IRR-table(its the latest one)

    Best Regards,

    Zaif

        
  • Jari Laine 16 Jan 2017  

    Hi Zaif,

    Most probably problem relates to changed CSS class names as you suspect.
    Please check that you use correct jQuery selector.

    Regards,
    Jari

        
  • Zaif 11 Jan 2017  

    Hi Jari,

    This solution is no longer working in Oracle Apex 5.

    I guess some issue with apexir_WORKSHEET_DATA ?

    Let me know if you have updated solution ?

    Br,

    Zaif

        
  • Deepa Unni 10 Aug 2016  

    Hi Jari,

    I am new to the APEX world and we are on EBS R12.1.3 with APEX 5.0.3 installed.

    I have a Tabular Form based on a complex view with several long list of columns on a page.

    Is there a way I can freeze the Column headers on a TAB Form ?

    I do see that there is similar question in the discussion in the OTN : Fixed header in Tabular Form

    But the options are not very obvious to me, even after I read to thro' - Jari's APEX Blog : Article Yet another way to create "editable interactive report"

    I have a tabular form, I don't think I can make it to a IR , due to various other requirements.

    Can you please help..?

        
  • Koloo Enock 20 Jun 2015  

    Jari,

    I am using the apex.oracle.com 5.0 and I tried to call to add a button to refresh the IR and see if the changes are saved not working,

    So how do you save the report changes and there are no "Save" button or anything also the ondemand process is executed after refresh meaning the changes are lost as you refresh the IR?

    I don't know if iam missing something now...

        
  • Jari Laine 20 Jun 2015  

    Hi Koloo,

    What is your APEX version?
    Any errors?
    How did you already debug this problem?

    Regards,
    Jari

        
  • Koloo Enock 19 Jun 2015  

    Hi,

    I saw you have the ondemand code to save changes but after updating a record then refresh the changes are lost? I don't get it how you apply the changes?

        
  • Jari Laine 31 Mar 2015  

    Hi Richard,

    You could use collection MD5 column for that.

    Regards,
    Jari

        
  • Richard Legge 31 Mar 2015  

    Hey Jari. Quick Q. Tabular forms allow you to check for changed data, for read consistency... How would you implement something similar, to stop someone overwriting changes, on this editable report..

    Thanks

    Richard

        
  • Jari Laine 10 Feb 2015  

    Hi Dave,

    I'm not PL/SQL guru, but it seems there is quite a lot problems in that code. I think it can't be fixed just adding equals signs.

    Regards,
    Jari

        

Global Right Column

Search

Archives

Subscribe in a Reader