Metric Selector

Objective:  The objective of this POC is to create a prompt that will allow a user to select a metric (from a list of metrics) and enter an amount to filter it by.

This is accomplished by creating three separate prompts and storing the values in Presentation Variables.  Then your report can use these Presentation Variables to filter your results.

Step #1 - Create an answers report that contains the metrics that you want to filter.  Save this report as "POC Metric Selector".

Step #2 - Create a prompt that will do the following:

• Allow the user to select a Year
• Allow the user to select a metric to filter
• Allow the user to select which operator they want to filter their metric with (<=,=,etc.)
• Allow the user to enter a value to filter their metric by

The first prompt on Year is self explanatory.

The second prompt on Quarter is not really a prompt on Quarter.  I had to choose a column in order to enter SQL, so I choose a column that wouldn't contain much data to begin with.  Once the column was on the report, I changed the "Show" drop-down box to SQL Results.  This allowed me to enter the following SQL:

SELECT CASE Time.Quarter WHEN '2006 Q4' THEN 'Revenue'

  WHEN '2007 Q4' THEN 'Month Ago Revenue'

  WHEN '2007 Q3' THEN 'Quarter Ago Revenue'

  WHEN '2007 Q2' THEN 'Year Ago Revenue'

  WHEN '2007 Q1' THEN 'Month Ago Billed Qty'

  ELSE 'Booked Amount' END as c1 

FROM "Sample Sales Reduced" 

ORDER BY c1

The important thing to notice here is the name I am assigning each column.  It has to match the Presentation Column Name exactly (Revenue, Month Ago Revenue, etc.).  This will not work if you change this name at all.

**Once this is complete, set a presentation variable = 'PV_METRIC_SELECTOR'

The Third Prompt is similar to the second prompt.  I choose Week for this prompt, and then I went into the column formula and changed the formula to ' '.  I only did this to show you options.  You don't have to do this if you don't want to.  Here is the SQL I entered into Prompt Three:

SELECT case Time.Year WHEN '2006' THEN '>=' 

  WHEN '2007' THEN '<=' 

  WHEN '2008' THEN '<>' 

  ELSE '=' END 

FROM "Sample Sales Reduced"

**Once this is complete, set a presentation variable = 'PV_METRIC_OPERATOR'

The Fourth Prompt is similar to the second prompt.  Instead of setting the control to Drop-down, I set the control to Edit Box.  I then assigned a presentation variable = 'PV_METRIC_VALUE'

    RESULTS

Save this prompt as "Metric Selector Prompt".

Step #3 - Modify the report you created in Step #1 (POC Metric Selector).  You will need to add a new filter to the report.  Choose any column from the catalog to filter.  Once the filter dialog box is up, choose Advanced --> Convert this filter to SQL.  Delete everything in the edit box and paste the following:

"@{PV_METRIC_SELECTOR}{Revenue}" @{PV_METRIC_OPERATOR}{<>} @{PV_METRIC_VALUE}{-123456789}

If you know how Presentation Variables work, then you can see that the default for this filter (if a user enters nothing in the prompt) is Revenue <> -123456789.  This should not affect your results (other than filtering out NULLS).

Step #4 - Build a dashboard page and place your prompt and report in different sections.  Then test away...

Notes:

• Typically in OBIEE you reference everything by "Table Name"."Column Name".  I learned through this POC that OBIEE is just like SQL, you only need to reference the Table Name if your Column Name exists in more than one Presentation Table.  Since best practices advise against this, you should not need to reference your Table Name to get this POC to work.
• If you have a requirement to enter the table name in your filter, you can always hard code it in front of the SQL filter you put on the report ("Table Name". "@{PV_METRIC_SELECTOR}{Revenue}" @{PV_METRIC_OPERATOR}{<>} @{PV_METRIC_VALUE}{-123456789})
• I would like to credit the many BLOGs out there that helped me achieve this result.  I got the idea for this from  http://www.biconsultinggroup.com/obiee-tips-and-tricks/using-a-dashboard-prompt-as-a-column-selector-for-multiple-r.html and  http://obiee101.blogspot.com/2008/05/obiee-making-column-selector-in-prompt.html.
• Wondering what the Logical SQL looks like?  DetailFilter: D0 Time.T05 Per Name Year = '2008' and 3-01 Booked Amt (Sum All):[DAggr(F0 Rev Base Measures.3-01 Booked Amt (Sum All) by [ D2 Market.M00 Mkt Key, D2 Market.M01 Market, D4 Product.P00 Product Key, D4 Product.P01 Product, D0 Time.T02 Per Name Month, D0 Time.T03 Per Name Qtr, D0 Time.T05 Per Name Year] )] >= 10000
• Wondering what the Physical SQL looks like?  I wish I could show you that, but since I created this off of an XML data source, the SQL is very hard to read.  I can tell you from real world experience that the filter on the metric is not sent to the database.  The filter is applied once the data is on the BI Server layer.

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.