Single Article

Load more rows to report while scroll

Category APEX and jQuery

Here is how create report that can be scrolled vertically. When user scrolls bottom of report it fetch more rows from database.

 

Copy your standard report template to new name. Edit new template and change Before Rows:

NOTE! If you use old or custom templates you need change table classes accordingly.

<table cellpadding="0" border="0" cellspacing="0" summary="" #REPORT_ATTRIBUTES# id="report_#REGION_STATIC_ID#">#TOP_PAGINATION#
<tr><td><div id="#REGION_STATIC_ID#_top">
<table cellpadding="0" border="0" cellspacing="0" summary="" class="report-standard"></table>
</div></td></tr>
<tr><td><div id="#REGION_STATIC_ID#_bottom">
<table cellpadding="0" border="0" cellspacing="0" summary="" class="report-standard">

Changes After Rows:

</table></div><div class="CVS">#EXTERNAL_LINK##CSV_LINK#</div></td></tr>
#PAGINATION#
</table>

Create classic report example from query:

SELECT e.EMPNO,
  e.ENAME,
  e.JOB,
  e.MGR,
  m.ENAME AS MNAME,
  e.HIREDATE,
  e.SAL,
  e.COMM,
  e.DEPTNO,
  d.DNAME,
  d.LOC
FROM emp e,
  dept d,
  emp m
WHERE e.deptno     = d.deptno
AND e.mgr          = m.empno(+)
  CONNECT BY LEVEL < 4
ORDER BY e.ENAME

Your query need have ORDER BY clause. Do not enable column heading sorting.

Edit report region and add to Region Footer:

<span role="alert" style="display:none;" id="apexir_LOADER">
<img src="#IMAGE_PREFIX#ws/ajax-loader.gif" alt="Processing Request" />
</span>

Edit Report attributes and change Report Template to your new template and Pagination Scheme to No Pagination Selected. Set Number of Rows to e.g. 50.

 

Create hidden item Px_START_ROW. Create before header computation for this item that always static assign value 1 for item.

 

Add to page JavaScript Function and Global Variable Declaration:

function setVscroll(pReqId,pHeight){
 var t=$("#"+pReqId+"_top");
 var b=$("#"+pReqId+"_bottom");
 b.find("table tr:first").children("th").each(function(i){
  var w=$(this).width();
  $(this).width(w);
  b.find("table tr:eq(1)").children("td").eq(i).width(w);
 });
 t.find("table").append(b.find("table tr:first")).find("tr").append(
  $("<th/>",{"class":"header","css":{"padding":0,"width":"16px","border-left":"1px solid #CCC"}})
 );
 t.find("table").css({"width":"100%"});
 b.find("table").css({"width":"100%"});
 b.css({"overflow-y":"scroll","height":pHeight,"border-bottom":"1px solid #CCC","overflow-x":"hidden"}).scrollTop(0).scroll(function(event){
  var self=$(this);
  var lTbl=self.children("table");
  if(self.scrollTop()>lTbl.height()-self.height()){
   $.ajax({
    type:"POST",
    url:"wwv_flow.show",
    dataType:"html",
    data:{
     p_flow_id:"&APP_ID.",
     p_flow_step_id:"&APP_PAGE_ID.",
     p_instance:"&APP_SESSION.",
     p_request:"APPLICATION_PROCESS=GET_MORE_ROWS",
     x01:pReqId.substr(1)
    },
    beforeSend:function(){
     /* Show loading image */
     $("#apexir_LOADER").show();
    },
    success:function(r){
     if(r.length===0){
      self.unbind("scroll");
     }else{
      lTbl.append(r);
     }
    },
    complete:function(){
     /* Hide loading image */
     $("#apexir_LOADER").hide();
    }
   });
  }
 });
}

Create dynamic Action. Select Advanced

  • Name: Set report scroll
  • Event: After Refresh
  • Selection Type: Region
  • Region: {select your report region}
  • Condition: No Condition
  • Action: Execute JavaScript code
  • Fire On Page Load: True
  • Code:
     setVscroll(this.triggeringElement.id,313);
  • Selection Type: None

Dynamic action JavaScript second parameter is height in pixels you like use for report.

 

Create On Demand Process GET_MORE_ROWS:

DECLARE
  binds   DBMS_SQL.varchar2_table;
  desctab DBMS_SQL.DESC_TAB;
  curid   PLS_INTEGER;
  colcnt  PLS_INTEGER;
  l_start PLS_INTEGER;
  l_sta   PLS_INTEGER;
  l_end   PLS_INTEGER;
  l_more  PLS_INTEGER;
  l_val   VARCHAR2(4000); 
  l_col   VARCHAR2(4000);
  l_sql   VARCHAR2(32700);
BEGIN
  l_more := 50;
  l_start := nv('Px_START_ROW') + l_more;
  :Px_START_ROW := l_start;
  l_end := l_start + l_more;
  /* Get region query */
  SELECT region_source
  INTO l_sql
  FROM apex_application_page_regions
  WHERE application_id = :APP_ID
  AND page_id          = :APP_PAGE_ID
  AND region_id        = APEX_APPLICATION.G_X01
  ;
  /* Get bind variable names */
  /* NOTE! wwv_flow_utilities.get_binds is undocumented APEX internal function. We should not use it. */
  binds := wwv_flow_utilities.get_binds(l_sql);
  /* Build pagination query */
  l_sql := '
    SELECT * FROM (
      SELECT row_number() OVER (ORDER BY 1) AS "DERIVED$01",
        q.*
      FROM (' || l_sql || ') q
    ) WHERE "DERIVED$01" BETWEEN :B_START AND :B_END
  ';
  /* Open cursor and set where clause bind variables */
  curid := dbms_sql.open_cursor;
  dbms_sql.parse(curid, l_sql, dbms_sql.NATIVE);
  /* Set start and end row bind variables */
  dbms_sql.bind_variable(curid, ':B_START', l_start);
  dbms_sql.bind_variable(curid, ':B_END', l_end);
  /* Loop binds from region query and set value */
  FOR i IN 1 .. binds.COUNT LOOP
    dbms_sql.bind_variable(curid, binds(i), v(LTRIM(binds(i), ':')));
  END LOOP;
  dbms_sql.describe_columns(curid, colcnt, desctab);
  FOR i IN 2 .. desctab.count LOOP
    dbms_sql.define_column(curid, i, l_val, 4000);
  END LOOP;
  l_sta := dbms_sql.execute(curid);
  /* Loop query to output HTML for new rows */
  WHILE dbms_sql.fetch_rows(curid) > 0 LOOP
    htp.prn('<tr class="highlight-row">');
    FOR i IN 2 .. colcnt LOOP
      dbms_sql.column_value(curid, i, l_val);
      l_col := desctab(i).col_name;
      htp.prn( 
        '<td class="data" headers="' || l_col || '">' || l_val || '</td>');
    END LOOP;
    htp.prn('</tr>');
  END LOOP;
  dbms_sql.close_cursor(curid);
END;

 

NOTE! If you use old or custom templates you need change classes accordingly. Check classes from loop that output HTML for new rows. Change Px_START_ROW to code according your hidden item name.

 

See working example.

Social Buttons

Comments

  • Karthik S 29 Mar 2018  

    Hi Jari,

    I need to implement the same logic in Interactive report. So please guide me or share if there are any plugins

        
  • Jari Laine 13 Feb 2016  

    Hi Scott Mike,

    Unfortenately it requres more work do same in interactive report.

    Maybe you check this ready plugin
    http://apex-plugin.com/oracle-apex-plugins/dynamic-action-plugin/ir-plugin-package_108.html

    Regards,
    Jari

        
  • Koloo Enock 12 Feb 2016  

    Hi Jari,

    iam trying to add vertical scrolling (exactly how you did it for classic report) to my interactive report in apex 4.x?

    Any Suggestion?

    thank you.

        
  • Eric Sacramento 10 Jun 2015  

    Parfait my friend !!!

        
  • scott 4 Nov 2013  

    Jari,

    I am using this important functionality you created and now i need your help please. After creating a report with vertical scroll bar ir works great with over 1000 records but when i download to excel only 501 records are Shawn in the excel sheet. What am i missing please ?

    thank you.

        
  • Jari Laine 12 Jul 2013  

    Hi scott,

    You do not have enough rows on report.

    Edit Report attributes and set Number of Rows to e.g. 50 or more. Or set your report height smaller by adjusting dynamic action JavaScript second parameter.

    Regards,
    Jari

        
  • scott 12 Jul 2013  

    Jari,

    I need your help with vertical scrollbar on classic report ive created based on your instruction here. The scrollbar just appear but you cant scroll on it, can you please take a look..

    wkspace=proj2010

    user/pwd=demo123/demo123

    app and page= 15763 / page15

    .regards

        
  • Damir 29 Oct 2012  

    Jari, main problem is to create correct region and get scroll bar in report .... And changing theme might be a problem for CSS lammers like I'm. :-) Rg Damir Vadas

    http://damir-vadas.blogspot.com/

        
  • Jari Laine 29 Oct 2012  

    Hi Damir,

    Ok, great you did get it working.

    You can download this sample from scroll_report_apex4.zip

    Regards,
    Jari

        
  • Damir 26 Oct 2012  
    I got it working. THX no need to assistance. Damir     

Global Right Column

Search

Archives

Subscribe in a Reader