Thursday, 20 November 2014

Custom export of APEX Classic reports

APEX 4.2.5 - Custom HTML table export

Export your report table after you have modified it with JQuery

Recently I have had a request to change sum totals in my classic report table and then include these newly created figures in excel export.

Problem was that APEX build in download function was not including these figures in standardised excel report.

After some brain storming these were my thoughts:
1. Is there a way I can refresh report to use standard APEX functionality to retrieve new figures? No or at least not that I know of.
2. Can I use some other technology to do the same? Seemed to me to be fairly common question for all web base apps. Exporting any HTML table in excel this surely must be doable.
3.  Is there any APEX plugin that can do the same? Probably but similar to BI Pub reports it will need to generate report from specifically rendered query which is not exactly what I was after.

Just for the challenge itself I decided to explore option 2 as it seemed the most simple way to go with.

Solution can be applied to any HTML table but my code will need to be adjusted as I have specifically used some APEX internal tags in my jQuery selectors. So please be aware of that.

In my example APEX theme is Blue responsive #25. I have been looking on line for similar solution and this is what I have so far.

Code might seem complicated but there is only few important lines of code that are APEX specific.

One note here - there is a difference how IE handles this and how other browsers render this functionality.  Reason behind it E doesn't support some of HTML tags and properties used such as URL download property which other browsers seem to support.

Please note I generated this for my project needs but before you decide to use it make sure you go through it and understand how this is achieved.

Let's start by looking at these functions separately.

Firefox and non IE browsers function:
var tableToExcel = (function() {
       //DOWNLOAD FOR browsers not IE
        var uri = 'data:application/;base64,', template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns=""><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>', base64 = function(
                s) {
            return window.btoa(unescape(encodeURIComponent(s)))
        }, format = function(s, c) {
            return s.replace(/{(\w+)}/g, function(m, p) {
                return c[p];
       return function(table, name, filename) {
            if (!table.nodeType)
         //APEX specific class used here
                table = $("#"+ table + " .uReportStandard")[0]; //document.getElementById(table);
            var cln=table.cloneNode(true);
            var paras = cln.getElementsByClassName('ignore');

            while(paras[0]) {
            var ctx = {
                worksheet : name || 'Worksheet',
                table : cln.innerHTML
          //THIS renders some random file name
          //window.location.href = uri + base64(format(template, ctx))      
         // EXPLANATION for this part to follow          
          $(".uReportDownloadLinks").after("<a id='Mydlink' style=\"display:none;\"></a>");
          document.getElementById("Mydlink").href = uri + base64(format(template, ctx));
          document.getElementById("Mydlink").download = filename;
Function receives three parameters which represent TABLE_ID,  WORKSHEET name and FILE name. Some parts of function above can be found online but trick that I used is after line commented with //EXPLANATION...... that is specific.

Idea was to replace original download link that you get on APEX classic reports with your own. To be able to do this I changed the original link by adding an ID to it before this function can get to it.

Link that is under my Download is a simple JavaScript call
exportHTML('#report_REGION_ID#','#Report name#');
Of course replacing values with a real ID and file name.
On the other side this is IE specific function:
function exportIEToExcel(tableID, filename, sheetname) {
    //WORKS with IE 11
    // Declare the variables
    var Excel, Book;

    // Create the Excel application object.
    Excel = new ActiveXObject("Excel.Application");

    // Make Excel visible.
    Excel.Visible = true;

    // Create a new work book.
    Book = Excel.Workbooks.Add();


    //new sheet
    Sheet = Book.Worksheets.Add();

    //sheet name
    Sheet.Name = sheetname;       

    //LOOP TABLE ROWS and fill excell sheet.
    var Mytable = $("#"+ tableID );
    //APEX specific selectors   
    Mytable.find('.uReportStandard tr').each(function (i) {
    //for each header
    var $ths = $(this).find('th');
    $ths.each(function (k) {
           Book.ActiveSheet.Cells(i+1,k+1).Value = $(this).html(); 
        });//end headers   

    //for each column             
    var $tds = $(this).find('td');
    $tds.each(function (j) {
        //alert('Row ' + (i + 1) + 'Cell ' + (j + 1)+ ' HMTL:' + $(this).html());
        //write into excell cells
        Book.ActiveSheet.Cells(i+1,j+1).Value = $(this).text(); 
        });//end column                       
    });            //end rows         

        idTmr = window.setInterval("Cleanup();",1000);             
 } //end exportIEToExcel

function Cleanup() {
   //“CollectGarbage” fire JScript's garbage collection to release the reference  to Excel
} // end Cleanup()

You will notice it uses completely different logic than our non IE function. Same as the first one function example can be found online but my addition is APEX related selectors and Cleanup function which makes sure that there is no processes left hanging in your Task Manager after user has finished downloading file.

All there is to do before you call these functions is to add this into your page either as Dynamic Action or on page load that will manage already mentioned trick about the report link.

$(".uReportDownloadLinks a").attr("id","my_anchor"); //add ID to old link link
  var dLink = 'report_REVENUE';
  var filename = 'Revenue';
  $( "#my_anchor" ).attr( "href", "javascript:exportHTML('"+dLink+ "','" + filename+ "');");

 If you have a look with Firebug end result looks like:
<div class="uReportDownloadLinks">

Only known problem is that currently it doesn't support report pagination but I am sure someone will think of the way on how to adjust this code and hopefully let me know about it. :D

Till then all reports without pagination can be downloaded using above approach.

Hope this helps.


Wednesday, 19 November 2014

BI Publisher Desktop version and report template issues

Oracle BI Publisher Desktop and APEX reporting

BI Publisher Desktop template "problems" when running in APEX

Recently I had a request from a client to update some of existing reports that run in their APEX 4.2.0 version. As I have done numerous applications that interact with BI Publisher there were no reasons why I should not be able to assist with this request. 

Bearing in mind that I am no BI Publisher expert but have done some BI reporting this is a problem I ended up with. After deploying my changed template all new regions had characters in bold font. No mater how much I tried I couldn't reproduce this in BI Pub Desktop and my template.

Before I go into it just a note that BI Publisher was used here as APEX print server. There are several other posts around that describe all options how you can integrate the two so I will not cover this in my today's post.

Here is an example of my problem, this was my word template:
where all seemed fine some text is bolded some is not. Once deployed under Shared components -> Reports resulted in:
which was surprising as I never encountered this problem before when dealing with BI reports in APEX. 

It took me some time to notice where problem might be and without help of my BI colleague I probably would still be looking at it.

First thing we agreed that we should better check for BI Publisher version this report is running on. Why? 

There is this funny story with BI publisher Desktop Add-ons for MS Word that not all versions are compatible. We both have experienced this before but since change I did in above template was really a minor one (adding 1 table, fields and bolding few of them) it never occur to me this might be the problem.

To verify this all that was needed (after Instance settings provided me the details and the link to the server) was try to open my BI Publisher and check out the version installed.  It was vs
Now question was are Oracle BI Publisher Desktop and Oracle BI Publisher version mutually supported? And I am still am not for sure about this one. :)
Eventually even though we tried everything with an older version of BI Pub Desktop 10.x it hasn't sorted this problem so we turned back to my original template.

At the end it came down to, all problems were caused by this new table that was holding my new data. For some reason BI Publisher printer didn't like the formatting of it (still not sure why) so I had to recreate it from scratch using preexisting table as base for it. After lots of copy and pasting things started working again and report finally looked like it was expected.

This probably isn't directly related to APEX but it may help. I learned my lesson. :)

Till my next post,