Single Article

Yet another way to create "editable interactive report" part 2

Category APEX Tricks

While back I did write blog post Yet another way to create "editable interactive report". This post is part 2, where I explain how you can add select lists and text area to report. In example I have use APEX_COLLECTION, but you can modify code to work with real table.

First create Before Regions PL/SQL process that creates apex_collection:

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


Then go to Shared Components > Lists of Values and create new LOV:

  • From Scratch
  • name: MANAGER
  • type: Dynamic
  • Query:
    select ename d, empno r
    from   emp
    order by 1


Create another new LOV:

  • From Scratch
  • name: DEPARTMENT
  • type: Dynamic
  • Query:
    select dname d, deptno r
    from   dept
    order by 1


Go back to edit page and create interactive report from query:

SELECT seq_id
  ,n001    AS empno
  ,c001    AS ename
  ,c002    AS job
  ,n004    AS mgr
  ,d001    AS hiredate
  ,n002    AS sal
  ,n003    AS comm
  ,n005    AS dname
  ,clob001 AS enotes
  ,n004    AS mgrno
  ,n005    AS deptno
FROM apex_collections
WHERE collection_name = 'EMP'


Change ENOTES column Display as to standard report column, MGRNO and DEPTNO columns to hidden.

Column attributes


Edit ENOTES column attributes and add to column formatting:

<textarea class="ir-edit-input" id="CLOB-#SEQ_ID#-1" cols="30" rows="3">#ENOTES#</textarea>

Clob column formatting

You can control text are width and height using attributes cols and rows.

Edit MGR column attributes and change Display Type to Display as Text (based on LOV, escape special characters).
Set Named List of Values to MANAGER

Manager column attributes


Set column formatting

<select class="ir-edit-input" data-lov="MANAGER" data-display-null="- Select -" data-last-value="#MGRNO#" id="N-#SEQ_ID#-4"></select>

Attribute data-lov defines shared components LOV name used for column select list.
Include attribute data-display-null if LOV should have null value. This attribute value is used as null display value.

From report query you can see that apex_collection columns n004 and n005 are defined two times with different alias. We did set columns with alias MGRNO and DETPNO as hidden column. Attribute data-last-value value should be set to that hidden column substitution string because we like have real column value to this attribute. If we use column substitution string value from column where Display Type was set to Display as Text (based on LOV, escape special characters), then attribute gets LOV display value. And select list do not work correctly in that case.

Manager column formatting

 

Edit DNAME column attributes and change Display Type to Display as Text (based on LOV, escape special characters).
Set Named List of Values to DEPARTMENT

Department column attributes

Set column formatting

<select class="ir-edit-input" data-lov="DEPARTMENT" data-last-value="#DEPTNO#" id="N-#SEQ_ID#-5"></select>

Department column formatting

Setup for other columns see previous blog post.

Create After Footer PL/SQL process:

DECLARE
  cur  SYS_REFCURSOR;
  lov_d VARCHAR2(4000);
  lov_r VARCHAR2(4000);
  l_cnt PLS_INTEGER;
BEGIN
  htp.prn('<script type="text/javascript">');
  FOR c1 IN (
    SELECT list_of_values_query
     ,list_of_values_name
    FROM apex_application_lovs
    WHERE application_id = :APP_ID
    AND list_of_values_name IN('MANAGER', 'DEPARTMENT')
  ) LOOP
    l_cnt := 0;
    OPEN cur FOR c1.list_of_values_query;
    LOOP
    FETCH cur INTO lov_d, lov_r;
    EXIT WHEN cur%NOTFOUND;
      l_cnt := cur%ROWCOUNT;
      IF l_cnt = 1 THEN
        htp.prn('var ' || c1.list_of_values_name || ' = [{');
      ELSE
        htp.prn(',{');
      END IF;
      htp.prn('"D":"' || lov_d || '","R":"' || lov_r || '"}');
    END LOOP;
    CLOSE cur;
    IF l_cnt = 0 THEN
      htp.p('var ' || c1.list_of_values_name || ' = [];');
    ELSE
      htp.p('];');
    END IF;
  END LOOP;
  htp.prn('</script>');
END;

This process will print LOV values as JSON objects.

We need JavaScript function that creates select list options from JSON objects.
Add to page JavaScript Function and Global Variable Declaration:

(function($){
 $.fn.htmldbCreateSelect = function(){
  this.filter("select").empty().each(function(i){
   lSelf = $(this);
   lov = eval(lSelf.data("lov"));
   if(lSelf.data("display-null")){
    lSelf.append($("<option/>",{"value":"","text":lSelf.data("display-null")}));
   }
   $.each(lov,function(i,d){
    lSelf.append($("<option/>",{"value":d["R"],"text":d["D"]}));
   });
   lSelf.val(lSelf.data("last-value"));
  });
  return this;
 }
})(apex.jQuery);


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("td .ir-edit-input")
    .change({region:this.triggeringElement},function(e){
     var $lThis=$(this), $lRegion=$(e.data.region);
     apex.server.process("SAVE_DATA",{
      x01:this.id,
      f01:$s_Split(this.value, 4000)
     },{
      dataType:"html",
      beforeSend:function(){
       $lThis.prop("disabled", true).parent("td").addClass("ui-autocomplete-loading");
      },
      success:function(pData){
       if(pData!=="OK"){
        alert(pData);
        $lThis.val($lThis.data("last-value")).prop("disabled", false).parent("td").removeClass("ui-autocomplete-loading");
        return false;
       }
       /* Refresh IR after value is saved */
       $lRegion.trigger("apexrefresh");
      }
     });
    })
    .htmldbCreateSelect()
    .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_val      VARCHAR2(32700);
  l_len      PLS_INTEGER := 0;
  l_data     CLOB;
  l_date_val DATE;
  l_num_val  NUMBER;
  l_arr      APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN

  l_len := APEX_APPLICATION.G_f01.COUNT;
 
  IF l_len > 0 THEN
    l_val := APEX_APPLICATION.G_f01(1);
  END IF;

  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      => l_val
    );

  ELSIF l_arr(1) = 'N' THEN

    BEGIN
      l_num_val := TO_NUMBER(l_val);
    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(l_val, '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
    );

  ELSIF l_arr(1) = 'CLOB' THEN
   
    IF l_len = 0
    OR COALESCE(LENGTH(APEX_APPLICATION.G_f01(1)), 0) = 0
    THEN
      APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
        p_collection_name => 'EMP',
        p_seq             => l_arr(2),
        p_clob_number     => 1,
        p_clob_value      => NULL
      );
    ELSE
     
      dbms_lob.createtemporary(
        lob_loc => l_data,
        cache   => TRUE,
        dur     => dbms_lob.session
      );
      dbms_lob.open(l_data, dbms_lob.lob_readwrite);
   
      FOR i IN 1 .. l_len
      LOOP
        dbms_lob.writeappend(
          lob_loc => l_data,
          amount  => LENGTH(APEX_APPLICATION.G_f01(i)),
          buffer  => APEX_APPLICATION.G_f01(i)
        );
      END LOOP;
      dbms_lob.close(l_data);
      APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
        p_collection_name => 'EMP',
        p_seq             => l_arr(2),
        p_clob_number     => 1,
        p_clob_value      => l_data
      );
    END IF;
  END IF;
 
  HTP.PRN('OK');
END;


And for last 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 98% 10% #EFEFEF !important;
}
.apexir_WORKSHEET_DATA td {
 padding-right: 22px !important;
 white-space: nowrap;
 vertical-align: top;
}
.apexir_WORKSHEET_DATA .ir-edit-input {
 color: inherit;
 text-align: inherit;
}


Now when you run page, you should have "editable IR" where is select list for manager and department, and text area for notes.

APEX 4.2 editable interactive report


See working example.

Sample is also available for download.

Comments

  • Jari Laine · 10 Jan 2018  

    Hi Mari,

    Unfortunately I don't have ready example or time to create one.
    But if you check this example code and try your self I'm sure you figure out how to do it.

    Regards,
    Jari

        
  • mari · 8 Jan 2018  

    Hi Jari,

    I don't want to use from application collection.

    please help me.

        
  • Jari Laine · 6 Jun 2016  

    Hi Jaya,

    You need change jQuery selectors in dynamic action to match APEX 5 IR css class

    Regards,
    Jari

        
  • Jaya · 5 Jun 2016  

    Hi Jari,

    I have uploaded the application in Apex 5.0. I am able to update the columns but when I do the pagination not able to update them in the collections. Can you please help me in this?

        
  • Joe Klein · 28 Apr 2016  

    HI Jari, sorry for the delay in responding. Somehow I lost the posting url.

    Anyway, I was using my Apex online workspace, which is version 5.

    Also, what username do I need to use to see your working examples?

        
  • Jari Laine · 23 Apr 2016  

    Hi Joe,

    What is your APEX version?

    Regards,
    Jari

        
  • Joe Klein · 22 Apr 2016  

    Hi Jari.

    I downloaded the sample and installed it, but for some reason the Manager and Department select lists do not have any values when I run the page. I checked and the LOV's and tables got created as part of the install. Any idea why they would be empty?

        
  • Rajesh · 29 Nov 2015  

    Hi Jari,

    I tried couple of your examples in Apex5.0.1 but non of them are working. I think it has to do with HTML5.0/JavaScript Code. I am very new to JavaScript so not sure what to fix. I will appreciate if you look at the issues and advise what fix is require to work in APEX 5.0

    Thanks

    Rajesh

        
  • Jari Laine · 1 Aug 2015  

    Hi Richard,

    I have one example.

    I try find time post instructions.

    Regards,
    Jari

        
  • Richard Legge · 31 Jul 2015  

    Hey Jari, hope you are well.. Ive got both a tabular report with a cascading LOV in my application, as well as an Editable Interactive Report with list boxes as per your "How-to"part 2.

    Now However I have a requirement to combine the two. I.e. for my editable Interactive Report to also have a cascading LOV... trouble is the cascading LOV is based quite specifically on Tabular Form.

    So, do you know how I could add cascading LOV functionality to Editable IR?

    Many Thanks

    Richard

        

Global Right Column

Search

Archives

Subscribe in a Reader