Sunil S. Ranka's Weblog

Superior Data Analytics is the antidote to Business Failure

How to Download Detail Data Directly in Excel through OBIEE

Posted by sranka on May 2, 2010

Hi All,

Recently I can across a requirement, where from the summary report client wanted to download the data directly to excel. Following was the use case :

Once Account Manager sees the opportunity total, he/she wants to see the detail data behind it. Eg. Line level details of install base, sales order number, Purchase Order details etc. Also they didn’t want too many clicks.

Following was the desired flow :

User sees the Opportunity Total –> User Clicks on the link and gets the detail data behind the Opportunity in an excel.

While working on this requirement challenges were different :

  • Browser hangs when we are trying to show too many rows (Over 3000 rows)
  • From UI perspective showing more than 25-35 rows, looses user attention.
  • Normal data rows include was more than 5000 rows

We looked at the various options of detail download :

  • WebService
  • Integrating with any external application which send data as an email
  • Any home grown tool

As the requirement was to get synchronous response, above tools/options were not viable.

After looking at the different venues, with my trust and belief in OBIEE architecture, I started finding my ways to find native solution. After spending few hours, to my surprise I was close to a solution which was native, scalable and easy to deployable .

Following Fig shows the detail :

Fig 1 – The base report which shows the total opportunity data

Fig 2 – This is the report which will be executed once download link is clicked on the base report.

Fig 3 – The popup report in the design mode.

This is the base report, clicking on Download Link, it will download data into an excel.

Base Report (Fig 1)

This is the popedup report

PopUp Report (Fig 2)

Report Design For PopUp Report

Report Design For PopUp Report (Fig 3)

Following piece of JavaScript was written in the PopUp report.

<!-- Following Java Scrip will be written as part of "Download JavaScript (Narrative View) Section" -->
<script>

<!-- Following code will remove all the dashboard page tabs,so that it looks like a popup window -->

var aElm=document.getElementsByTagName('TABLE');
for(var i=0; i<aElm.length; i++) {

 if ((aElm[i].className.indexOf("TabTable") > -1)
 || (aElm[i].className.indexOf("PortalBanner") > -1)
 || (aElm[i].className.indexOf("PortalBottomTable") > -1)){
 aElm[i].style.display = "none";
 }

}// end of table loop
<pre><!-- Following code will get SerachId and ViewId -->
</pre>
var aElm=document.getElementsByTagName('DIV');
var searchId;
var viewId;
for(var i=0; i<aElm.length; i++) {
 if (aElm[i].getAttribute("sid") != null){
 searchId = aElm[i].getAttribute("sid");
 }
 if (aElm[i].getAttribute("vid") != null){
 viewId = aElm[i].getAttribute("vid");
 }// end of if
}// end of table loop

// BaseURL is created "format=txt and Extension=.csv" is important
var baseDownloadURL = 'saw.dll?Go&PortalPath=<Dashboard>&Page=<PageName>&Action=Download&Format=txt&Extension=.csv&ItemName=<ReportName>&';

baseDownloadURL = baseDownloadURL + 'SearchID='+searchId+'&ViewID='+viewId;

Download(baseDownloadURL);

</script>

Hope This Helps

Sunil S Ranka
"Superior BI is the antidote to Business Failure"

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: