Single Article

Custom pagination for APEX 3.2 interactive report with jQuery Part2

Category APEX and jQuery

I recently wrote about Custom pagination for APEX 3.2 interactive report with jQuery.

Here are the instructions how you can add custom pagination for  application all interactive reports at once in APEX 3.2.

Load jQuery library in page template header before substitution #HEAD#.

Create HTML region to page zero.
Set region Display Point to "Before Footer" and Template "No Template".
Insert to Region Source:

<script type="text/javascript">
$(function(){
 
 $('#htmldbIR_x1').live({
  'change':function(){
   gReport.navigate.paginate('pgR_min_row='+this.value+'max_rows='+$v('apexir_NUM_ROWS')+'rows_fetched='+$v('apexir_NUM_ROWS'));
  }
 });

 $('#apexir_WORKSHEET_REGION').bind('htmldbIrReady',function(){
  if($('#apexir_DATA_PANEL table.apexir_WORKSHEET_DATA td').length>0){
   var i=$.htmldbIrPagination();
   var ajaxReq=new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=GET_IR_PAGINATION',$v('pFlowStepId'));
   ajaxReq.addParam('x01',$v('apexir_NUM_ROWS'));
   ajaxReq.addParam('x02',i[2]);
   ajaxReq.addParam('x03',i[0]);
   $('#apexir_DATA_PANEL').find('td.pagination').prepend(ajaxReq.get());
   $('#htmldbIR_x1').val(i[0]);
  }
 });

});

(function($){
 
 $.htmldbIrBusyGrap=function(){
  if($('#apexir_WORKSHEET_REGION').length>0){
   gReport._BusyGraphic=function(pState){
    if(pState==1){
      $x_Show('apexir_LOADER');
    }else{
     if(!$('#apexir_WORKSHEET').data('htmldb')){
      $('#apexir_WORKSHEET').data('htmldb',{irReady:true});
      $.event.trigger('htmldbIrReady');
     };
     $x_Hide('apexir_LOADER');
    };
    return;
   };
   $('#apexir_WORKSHEET').data('htmldb',{irReady:true});
   $.event.trigger('htmldbIrReady');
  }
 };

 $.htmldbIrPagination=function(options){
  var p=$.trim($('#apexir_DATA_PANEL').find('td.pagination').find('span.fielddata').text());
  var a=new Array();
  var n=new Array();
  a=p.split(' ');
  $.each(a,function(i,v){
   if(!isNaN(v)){
    n.push(v);
   };
  });
  switch(options){
   case 'first':return n[0];
   case 'last':return n[1];
   case 'max':return n[2];
   default:return n;
  };
 };

})(jQuery);

addLoadEvent($.htmldbIrBusyGrap);
</script>

 

Go to Application Shared Components and select Application Processes.
Create new On Demand process called GET_IR_PAGINATION

DECLARE
  l_opt                 VARCHAR2(32700);
  l_show_rows           NUMBER;
  l_max_query_rows      NUMBER;
  l_first_row           NUMBER;
  l_last_page_start_row NUMBER;
  l_pages               NUMBER;
  l_start_row           NUMBER;
  l_end_row             NUMBER;
BEGIN

  l_show_rows      := APEX_APPLICATION.G_x01;
  l_max_query_rows := APEX_APPLICATION.G_x02;
  l_first_row      := APEX_APPLICATION.G_x03;

  IF l_max_query_rows > l_show_rows THEN
    l_pages          := FLOOR(l_max_query_rows / l_show_rows);
    l_start_row      := 1;

    IF l_show_rows > 1 THEN
      l_last_page_start_row := l_pages * l_show_rows + 1;
      IF l_last_page_start_row > l_max_query_rows THEN
        l_last_page_start_row := l_last_page_start_row - l_show_rows;
      END IF;
    ELSE
      l_last_page_start_row := l_max_query_rows;
    END IF;

    htp.p('<div id="htmldbIrCustPagination" style="padding-bottom:10px">');

    IF l_first_row > 1 THEN
      htp.prn('<a href="javascript:gReport.navigate.paginate(''pgR_min_row=1max_rows=' || l_show_rows || 'rows_fetched=' || l_show_rows || ''')">');
      htp.prn('<img align="absmiddle" alt="&lt;&lt;" title="&lt;&lt;" src="#IMAGE_PREFIX#srmvall.gif" />');
      htp.p('</a>');
    END IF;

    htp.p('<select id="htmldbIR_x1" size="1">');
    FOR i IN 0 .. l_pages
    LOOP

      l_opt     := NULL;

      l_end_row := (l_start_row + l_show_rows) - 1;

      IF l_end_row >= l_max_query_rows THEN
        l_end_row := l_max_query_rows ;
      END IF;

      l_opt :=
      '<option value="' 
      || TO_CHAR(l_start_row)
      || '">Rows ' 
      || TO_CHAR(l_start_row)
      || ' - '
      || TO_CHAR(l_end_row)
      || '</option>'
      ;
      
      IF l_start_row <= l_last_page_start_row THEN
        htp.p(l_opt);
      END IF;
    
      l_start_row := l_start_row + l_show_rows;

    END LOOP;

    htp.p('</select>');

    IF l_first_row < l_last_page_start_row THEN
      htp.prn('<a href="javascript:gReport.navigate.paginate(''pgR_min_row=' || l_last_page_start_row || 'max_rows=' || l_show_rows || 'rows_fetched=' || l_show_rows || ''')">');
      htp.prn('<img align="absmiddle" alt="&gt;&gt;" title="&gt;&gt;" src="#IMAGE_PREFIX#smvall.gif" />');
      htp.p('</a>');
    END IF;

    htp.p('</div>');

  END IF;

END;

Make sure that all your application interactive reports Pagination Type is "Row Ranges X to Y of Z".

Now you should have custom pagination on all application interactive reports like in this sample.

 

Comments

  • karthik · 14 Apr 2015  

    Got it. Thanks Jari. We are using apex 4.2

    Regards,

    Karthik

        
  • Jari Laine · 10 Apr 2015  

    Hi karthik,

    Did you check link I did post?
    You can add CSS to e.g. page template.

    BTW, witch version of APEX you use?

    Regards,
    Jari

        
  • karthik · 9 Apr 2015  

    Hi Jari,

    Thanks for your response but can u please specify where can i find css in interactive reports? The folowing is my pagination code.

    if(apex.jQuery('#apexir_DATA_PANEL table.apexir_WORKSHEET_DATA td').length>0){

    var i=$.htmldbIrPagination();

    var ajaxReq=new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=GET_IR_PAGINATION',$v('pFlowStepId'));

    ajaxReq.addParam('x01',$v('apexir_NUM_ROWS'));

    ajaxReq.addParam('x02',i[2]);

    ajaxReq.addParam('x03',i[0]);

    apex.jQuery('#apexir_DATA_PANEL').find('td.pagination').prepend(ajaxReq.get());

    apex.jQuery('.htmldbIR_x1').val(i[0]);

    }

    By default its displaying #apexir_LOADER when clicked on pagination. But I need to change it to apex.widget.waitPopup so that users will be unable to access interav=ctive reports while loading.

        
  • Jari Laine · 8 Apr 2015  

    Hi karthik,

    Easiest way customize IR loading is just add few lines CSS e.g. to page template. Please see Kevan Gelling blog post how do that.

    Regards,
    Jari

        
  • karthik · 7 Apr 2015  

    Hi

    I have to include page loading animation in the custom pagination, ie when users click pagination they should see apex.widget.waitPopup()loading symbol and display results after that. Can someone please help me with this. Thanks in advance

        

Global Right Column

Search

Archives

Subscribe in a Reader