Friday 14 October 2016

APEX - Reports with Row Details



Overview:
1. Purpose.
2. Benefits.
3. Steps.  
4. Screenshots.
5. Conclusion.

1.  Purpose:
This document describes how to show a Apex Reports with Row Details          In Apex, Report is an important tool which is in classical, interactive & other formats. Suppose if we have many columns in an report, then we want to display only important columns on a report but with an button option in report brings other columns in an collapsible format from the same report itself.
Here we are going to achieve this functionality using Jquery and On Demand Application Processes.
   2.  Benefits:
Ø We can restrict the user to display certain columns in report but will show other columns in a collapsible way.
Ø Look and feel of an report will be increased.

  3.  Steps:
Step1: Create a new page
Step 2: Create a region èreportèClassic reportèName for the reportèSQL query.


SELECT p.product_id, p.product_name, p.CATEGORY, p.product_avail,
        (SELECT apex_lang.lang ('Details')
          FROM DUAL) AS details
  FROM demo_product_info p
Step 3: Edit DETAILS column attributes and make column as link
Link Text : #DETAILS#
Link Attributes : class="product-details" data-product="#PRODUCT_ID#"
Target : URL
URL : #

Step 4: Add to page JavaScript File URLs

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

Step5:  Add 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);

Step6: Add to page CSS Inline

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

Step7: 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;

Step8: 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:
1
2
3
4
5
6
7
$(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

4. Screenshots:






























5. Conclusion:
Thus a user can able to see the detail rows in a collapsible format from an report itself.

3 comments:

  1. hi
    How we can add "Expand all Collapse all" option in
    (i dont have any experience in javascript ) its urgent
    Help plz
    thanks

    ReplyDelete
  2. Thanks for sharing this great information on Oracle APEX. Actually I was looking for the same information on internet for Oracle Apex Interview Questions and Answers and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can learn more about Oracle APEX by attending Oracle APEX Training.

    ReplyDelete