Single Article

APEX 4.2 interactive report with row detail

Category APEX and jQuery

Here is how create interactive report with the functionality to show a detail row per each row on APEX 4.2.

Below you can see interactive report query I did use for this example:

SELECT p.product_id
,p.product_name
,p.category
,p.product_avail
,p.list_price
,(
  SELECT sum(quantity)
  FROM demo_order_items
  WHERE product_id = p.product_id
  ) AS units
,(
  SELECT sum(quantity * p.list_price)
  FROM demo_order_items
  WHERE product_id = p.product_id
  ) AS sales
,(
  SELECT count(o.customer_id)
  FROM demo_orders o
   ,demo_order_items t
  WHERE o.order_id = t.order_id
   AND t.product_id = p.product_id
  GROUP BY p.product_id
  ) AS customers
,(
  SELECT max(o.order_timestamp) od
  FROM demo_orders o
   ,demo_order_items i
  WHERE o.order_id = i.order_id
   AND i.product_id = p.product_id
  ) AS last_date_sold
,(
  SELECT APEX_LANG.LANG('Details')
  FROM DUAL
  ) AS details
FROM demo_product_info p

Edit DETAILS column attributes and make column as link

  • Link Text : #DETAILS#
  • Link Attributes : class="product-details" data-product="#PRODUCT_ID#"
  • Target : URL
  • URL : #

Add to page JavaScript File URLs

#IMAGE_PREFIX#libraries/jquery-ui/1.8.22/ui/minified/jquery.ui.button.min.js

and to Function and Global Variable Declaration

var gDetailCache = new Object();
(function($){
 $.fn.htmldbDetailRow=function(options){
  options=$.extend({},{
   "trIdPrefix":"D",
   "btnShowClass":"ui-icon-plusthick",
   "btnHideClass":"ui-icon-minusthick",
   "btnAjaxClass":"ui-icon-refresh"
  },options);
  this.each(function(){
   var $Self  = $(this).removeAttr("href").button({icons:{primary:options.btnShowClass},text:false}),
       $Row   = $Self.closest("tr"),
       $Ico   = $Self.children("span.ui-button-icon-primary"),
       lC     = $Row.children("td").length,
       lId    = $Self.data(options.btnData),
       lTrId  = options.trIdPrefix+lId,
       lClass = options.btnShowClass + " " + options.btnHideClass
   ;
   $Self.click(function(){
    $Tr=$($x(lTrId));
    if($Tr.length===0){
     $Self.button("option",{icons:{primary:options.btnAjaxClass},"disabled":true});
     apex.server.process(options.onDemanProcess,
      {x01:lId},{dataType:"text",success:function(d){
       var $Tr=$(
        '<tr id="' + lTrId + '">' +
        '<td class="' + options.tdClass + '" colspan="' + lC + '">'
        + d +
        '</td>' +
        '</tr>'
       ),lA=new Object();
       lA[lTrId]={d:$Tr,s:true};
       $.extend(gDetailCache,lA);
       $Row.after($Tr);
       $Ico=$Self.button("option",{icons:{primary:options.btnHideClass},"disabled":false})
       .children("span.ui-button-icon-primary");
      }
     });
    }else{
     $Tr.toggle(0,function(){
      $Ico.toggleClass(lClass);
      gDetailCache[lTrId].s=!gDetailCache[lTrId].s
     })
    }
   });
   if(lTrId in gDetailCache){
    gDetailCache[lTrId].d.children().attr({"colspan":lC});
    $Row.after(gDetailCache[lTrId].d);
    if(gDetailCache[lTrId].s){
     $Ico.toggleClass(lClass)
    }else{
     gDetailCache[lTrId].d.hide()
    }
   }
  })
  return this
 }
})(apex.jQuery);

Add to page CSS Inline

.prodinfo{
 padding:6px!important;
 font-size:12pt!important;
 color:#660000!important;
 font-weight:bold!important;
 text-align:center!important;
}

Create on demand process GET_PRODUCT_INFO

DECLARE
  l_info VARCHAR2(32000);
BEGIN
  SELECT product_description
  INTO l_info
  FROM demo_product_info
  WHERE product_id = apex_application.g_x01; 
  HTP.PRN(l_info);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    HTP.PRN('No additional information');
  WHEN OTHERS THEN
    HTP.PRN(sqlerrm);
END;

Create dynamic action

  • Name: IR detail row
  • 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('a.product-details')
    .htmldbDetailRow({
     onDemanProcess:"GET_PRODUCT_INFO", // on demand process name
     tdClass:"prodinfo",                // details class
     btnData:"product"                  // button data name
    });
    
  • Selection Type: None

Now when you run page you have button on each row to expand/collapse row details.

Please note that when you e.g. paginate to next page and back, example remember rows that were expanded =).
See working example.

IR detail row

Comments

  • developper · 25 Oct 2017  

    hi my Mr jari

    How we can add "Expand all Collapse all" option in

    (i dont have any experience in javascript ) its urgent

    Help plz

    thanks .

        
  • developper · 18 Oct 2017  

    hi Jari Laine

    can you help me for button show all (i dont have experience in javascript)

    thanks .

        
  • Jari Laine · 18 Oct 2017  

    Hi developper,

    Yes it is posible. See previous comments.

    Regards, Jari

        
  • developper · 17 Oct 2017  

    hello

    thanks for this tutoriel

    its possible to add button for show all report row detail

    thanks

        
  • Vedant · 31 Jan 2017  

    Thank you so much Jari. I'll surely update the post once I am done with it. Also, if yo get time please look into this.

    Thank you!

        
  • Jari Laine · 31 Jan 2017  

    Hi Vedant,

    I think both. Of course it depend on all minor details how you like it work e.g. on pagination or after filtering report.

    Good luck and please post your modifications here so others can also benefit from those.

    Regards,
    Jari

        
  • Vedant · 31 Jan 2017  

    Hi Jari!

    Should I check the javascript on page or Javascript On dynamic action?

        
  • Jari Laine · 31 Jan 2017  

    Hi Vedant,

    I don't have ready example and busy with another things.

    Start checking code. I'm sure you figure out how to do it.

    Regards,
    Jari

        
  • Vedant · 31 Jan 2017  

    Hi Jari.

    Thanks for your quick response. Actually I am new to apex so I don't have much idea with Javascript. Can you help me with it.

    Thanks!

        
  • Jari Laine · 31 Jan 2017  

    Hi Vedant,

    You should create button and modify JavaScript for your needs.

    Regards,
    Jari

        

Global Right Column

Search

Archives

Subscribe in a Reader