APEX 4.2 interactive report with row detail

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.