Skip to Main Content

Sunday, November 2, 2014

APEX 4.2 interactive report with row detail

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

Pagination

Comments

  • Jari Laine 4 Jan 2021

    Ok. Unfortunately I can't check how this works on APEX 19.2.

    When I find time, plan is post instructions where code not depend to jQuery UI button as example in apex.oracle.com

  • Developer 4 Jan 2021

    Hey, thanks for the quick response. I suspected it might be related to the version (I am using 19.2). It is cool, I just updated the java to not change from the plus to minus, and just keep it at plus all the time. Not the best but at least the button stays consistent

  • Jari Laine 4 Jan 2021

    Hard to say, but I suspect it relates APEX version. What version of APEX you use? I know this solution not work without changes on APEX 20.2 because jQuery UI is deprecated.

  • developer 4 Jan 2021

    this is a cool way to show additional information.
    question: the above works the only thing that is a bit off is that the button when clicked turns from plus to minus but does not turn back to a plus when clicked again. Is there something I missed?

  • 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