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.