OBIEE - Download to File POC #2

Objective:  The objective of this POC is to download large amounts of data directly to a user’s machine.  Nothing but a prompt should render to their screen.  To accomplish this, I will create a Dashboard Page that only contains prompts.  When a user clicks the GO button, the Dashboard page should execute a report and pass the values in the prompt to the report.  The report will not return the results to the screen.  It will only allow you to Open or Save the file in .csv format.

This is accomplished by replacing the Go button with some custom JavaScript, and calling the GO URL with the custom JavaScript.

Step #1 - Start with any Answers report.  Place a couple of is prompted filters on your report.  Save this report as “Revenue Detail”.

Step #2 - Create a prompt that contains prompts for Month and Brand.

Step #3 - Place the prompt created in Step #2 on a new dashboard page.

Step #4 - Place a text object below your prompt.  Put the following script into the text box, and click the contains HTML box.  This will suppress the existing Go button.

<script> try{    var aElm = document.getElementsByTagName('span');    for(var i=0; i<aElm.length; i++){       if(aElm[i].innerHTML.indexOf('GFPDoFilters') > -1){          aElm[i].style.display = 'none';       }    }     } catch(e){alert('XXX '+e);} </script>

Step #5 - Place a second text object below the Suppress Go object you just created.  Add the following JavaScript in the text box, and click the contains HTML checkbox.  This script was mostly copied from the popular GlobalGo button script.  I've modified it slightly so you don't need to change any JavaScript behind the scenes (since I only had one Go button in my example).  This code will work with an Out Of The Box implementation of OBIEE.

START SCRIPT

<script> function GlobalGo(){

try{

 var aElm = document.getElementsByTagName('table');

//Validation variables

 var aSpanElm = document.getElementsByTagName('span');

 var sCaption;

 var errMsg = '';

 var i = 0;

//Convert function

function convert(str) {   

    str = str.replace(/'/g,'"').replace(/\s/g,'%20').replace(/\$/g,'%24').replace(/\&/g,'%26').replace(/\+/g,'%2B').replace(/,/g,'%2C').replace(/\//g,'%2F').replace(/\:/g,'%3A').replace(/\;/g,'%3B').replace(/\=/g,'%3D').replace(/\?/g,'%3F').replace(/\@/g,'%40');  

    return str; 

}

 //Put all Dashboard Prompt values into a variable +++++++++++++++++++++++++++++++++++

 for(var a=0; a<aSpanElm.length; a++){

   

   //Get Caption

   if(aSpanElm[a].className=='GFPCaption'){

     sCaption = aSpanElm[a].firstChild.data;

     

   }

   //Check if required value exists

   if(aSpanElm[a].className=='GFPControl'){

   

     var aInput = aSpanElm[a].getElementsByTagName('input');    // multi-select and text boxes

     var aOption = aSpanElm[a].getElementsByTagName('option');  // dropdown

     //check if time field has a value

     if(sCaption=='Month'){ 

       for(var c=0; c<aOption.length; c++){

         if(aOption[c].selected && aOption[c].value.trim().length > 0){

           var TimeSelected = aOption[c].value;

         } 

       }

     } //Endif check time field

     //Check if Brand has value(s)

     if(sCaption=='Brand'){ 

       for(var c=0; c<aInput.length; c++){

         if(aInput[c].value.trim().length > 0){

           var BrandSelected = aInput[c].value;

         } 

           else

            {

              BrandSelected = '""'

            }

       }

     } //Endif check Brand field

     //END ********** 

   } //Endif GFPControl

 } //End Put all Dashboard Prompt values into a variable ++++++++++++++++++++++++++++++++++ 

//Removed reserved characters from the URL

TimeSelected=convert(TimeSelected);

BrandSelected=convert(BrandSelected);

//Prepare the URL************

var url = location.href;

var baseURL = url.substring(0, url.indexOf('/', 14));

var MonthURL = '&col1=Time."Month"&val1=' + '"' + TimeSelected + '"'

var BrandURL = '&col2=Products.Brand&val2=' + BrandSelected

var myURL = baseURL + '/analytics/saw.dll?Go&path=/users/administrator/Revenue%20Detail&Action=Navigate&Format=csv&Extension=.csv' + MonthURL + BrandURL

  winRef=window.open(myURL);

//End URL Section ************

}

  catch(e){alert('XXX ' + e);}

}

String.prototype.trim = function() {

  return this.replace(/^\s*|\s*$/, "");

}

</script>

<div class="XUIPromptEntry minibuttonOn" align="left"><a href="#" onclick="javascript:GlobalGo();">Go</a></div>

<div class="XUIPromptEntry minibuttonOn" align="left"><a href="#" onclick="return PersonalizationEditor.removeDefaultSelection(false) ">Clear</a></div>

END SCRIPT

Step #6 - Test your dashboard.

A couple of notes on the JavaScript in Step #5.

1.  This section of the code is how you capture drop down values.

     //check if time field has a value

     if(sCaption=='Month'){ 

       for(var c=0; c<aOption.length; c++){

         if(aOption[c].selected && aOption[c].value.trim().length > 0){

           var TimeSelected = aOption[c].value;

         } 

       }

     } //Endif check time field

2.  This section of the code is how you catpure multi-select values.

     if(sCaption=='Brand'){ 

       for(var c=0; c<aInput.length; c++){

         if(aInput[c].value.trim().length > 0){

           var BrandSelected = aInput[c].value;

         } 

           else

            {

              BrandSelected = '""'

            }

       }

     } //Endif check Brand field

3.  I had to write a function to remove URL reserved characters from all of my prompt values.  If you do not convert these characters, the GO URL will not work.

BrandSelected=convert(BrandSelected);

4.  Once you have your prompt values captured and converted, you can pass them to the GO URL.

var BrandURL = '&col2=Products.Brand&val2=' + BrandSelected

var myURL = baseURL + '/analytics/saw.dll?Go&path=/users/administrator/Revenue%20Detail&Action=Navigate&Format=csv&Extension=.csv' + MonthURL + BrandURL

That's it for this post.  Until next time...