OBIEE 11g

I recently started working on an OBIA (7.9.6.3) project and realized that 11g performs 5 - 10 x faster using Firefox or Chrome.  IE7 is a dog with 11g.  Since IE is the only supported browser at my client, we never thought to try a different browser.

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...

OBIEE - Download to File POC

Objective:  The objective of this POC is to create a Dashboard Page that runs an Answers report and displays a row count.  The Dashboard page should provide the option to view the results in OBIEE or download the data in .csv format.

Step #1 - Start with any Answers report.  Place a new column on the report, and move it to the first position.  Open the function and rename the column and table to Row Count.  Then delete the Column Formula and replace it with Max(Rcount(Column_Name)).  I usually pick the lowest level column on the report, but it doesn’t matter which one you pick.  Once you have your row count created, click on properties, and hide this column.

Step #2 - Create your compound layout.  This is the result you will see when you choose View Data.  If you have a title on your report, make sure you do not have the box checked for Display Saved Name.  The title will not show up unless you have something written in the title dialogue box.

Step #3 - Create a second Compound Layout.  This is one of the views that will be shown on the dashboard.  This view should only have your title and filter.  Don’t put your table results in this view, or you will lose the time savings from not having to render the output.

Step #4 - Create a Narrative view.  Click the button for “Contains HTML Markup”.  Then enter a message to let the users know how many rows are returned in the report.  This will help them decide if they want to view the report in OBIEE or download the results to a .csv file.

Set the Rows to display = 1

Append to this message some Javascript code that finds the request’s Answer ID (SID).  The SID will allow you to retrieve your results from memory rather than running the report again.  The GO URL will be used to retrieve the results from memory.  Special thanks to the following Blog for this script: Nicolas GERARD

** If you copy and paste this code, the single and double quotes get messed up.  You will have to do a search and replace to return them to their original form.

<div id="print"></div>

<div id="obiee_anchor_map"></div>

<script language="javascript">

function GetSid(nodeId) {

        var container = document.getElementById(nodeId);

            var sid = null;

            // Code to capture SID

            var x = container;

            do {

                        if (x.nodeName == 'TD' || x.nodeName == 'DIV') {

                                    sid = x.getAttribute('sid');

                                    if (sid != null && sid != '')

                                                break;

                        }

                        x = x.parentNode;

            } while (x != null);

       return sid;

}

function GetNqid() {

            var nameEQ = 'nQuireID=';

            var ca = document.cookie.split(';');

            for(var i=0;i < ca.length;i++) {

                        var c = ca[i];

                        while (c.charAt(0)==' ') c = c.substring(1,c.length);

                        if (c.indexOf(nameEQ) == 0) return c.substring(nameEQ.length,c.length);

            }

            return null;

}

function PrintInElement(VarToPrint) {

    var myElementField = document.getElementById('print');

    myElementField.innerHTML = myElementField.innerHTML + '<BR>' + VarToPrint;

}

//Build the report URL

var ReportURL = 'http://localhost:9704/analytics/saw.dll?Go&searchid='+GetSid('obiee_anchor_map') + '&nqid=' + GetNqid() + ' target="_blank"';

//Build the Download URL

var DownloadURL = 'http://localhost:9704/analytics/saw.dll?Go&searchid='+GetSid('obiee_anchor_map') + '&Action=Navigate&Format=csv&Extension=.csv&nqid=' + GetNqid();

//Build the hyperlinks

var ReportHyperlink = '<div class="XUIPromptEntry minibuttonOn" align="center"><A HREF=' + ReportURL + '>Show Results</A></div>';

var DownloadCSV = '<div class="XUIPromptEntry minibuttonOn" align="center"><A HREF=' + DownloadURL + '>Download Results</A></div>';

//Display the hyperlinks

PrintInElement(ReportHyperlink);

PrintInElement(DownloadCSV);

</script>

 **I’m not sure why the buttons show up twice in the preview pane.  They only show up once in the report.

Step #5 - Add this report and a prompt (if needed) to a dashboard page.  Add the report two times to the same section.  In the first report, display only Compound Layout 2 (created in Step #3).  In the second report, display only the Narrative view (created in Step #4).

 **Adding a report twice to the same dashboard only executes the report once.

 As you can see I cleared the cursors (see above), and then reran the report (see below).  After the rerun, there were two global filters executed and one report.

Step #6 - Run your report.

 Step #7 - Click Show Results.  A new tab/window will open, and the results will be displayed.

Step #8 - Click Download Results.  A Popup window will open asking you to open or save the file.

My First Blog

I am ready to join th Blogging world.  After becoming a consultant in September of 2010, I figured it was time to start giving back to the OBIEE developers of the world.  I have "borrowed" lots of ideas and code from other members of the Blogging community.  Most of my creations are not new or ground breaking, but they will hopefully save others time and headache.

My background:
8 years of ETL development and Data Warehouse architecture
2 years of Business Analysis
2 years of Project Management
3 years OBIEE report development

I am currently working for a local consulting company in Chicago, IL.