OBIEE - Custom Export to Excel link

This post (done in OBIEE 11.1.1.7.140114) is about creating links to export a specific analysis to excel. One option is using URL, the other is with Java Script.

Why do I want this anyway? By default I have an export to excel option on the dashboard (report links). There are few specific reasons:

1. Sometimes I want to get a URL that immediately returns excel.
2. In my case a customer wanted a slightly different version of the analysis to be exported to excel. So the dashboard has analysis A and the export is done on Analysis B.

Using URL

The GO URL options are documented in the integrator's guide chapter 6, unfortunately there is no option of exporting to excel there (just mentioning of the &Action=Extract option where "Results are displayed in an application-friendly format, such as for Microsoft Excel, without the paging control, hot links, and so on.") The only export options mentioned there are XML, HTML, or CSV:

saw.dll?Go&Path=/Shared/Test/SB2&Format=xx

Example:

saw.dll?Go&Path=/Shared/Test/SB2&Format=XML

Unfortunatly the Excel option is missing in the documentation.

The GO URL that export in EXCEL 2007 format is:

saw.dll?Go&path=/shared/Test/SB2&Format=excel2007&Extension=.xlsx

 The GO URL that export in EXCEL 2003 format is:

saw.dll?Go&path=/shared/Test/SB2&Format=excel2003&Extension=.xls

If you prefer a complete URL, this one is working on my computer:

http://bdahab-lap:9704/analytics/saw.dll?Go&path=/shared/Components/test1/Overview1&Format=excel2007&Extension=.xlsx

For example the above link (starting saw.dll) in the dashboard:

The drawback of this option, if you want it in a dashboard: it always opens an extra tab in browser, when executed.

Using Java Script

This option is described here  by Satya Ranki, from http://satyaobieesolutions.blogspot.com. A very nice solution where he gets the java script by viewing the Dashboard source code (right click, and View Source).  Next you search a relevant string such as xls or xlsx. I'll take it from here and do some extra cleaning and understanding of the result, so the code will be easily reused.

The code i got was:

a class="NQWMenuItem NQWMenuItemWIconMixin" name="SectionElements" href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go&amp;ViewID=o%3ago%7er%3areport&amp;Action=Download&amp;SearchID=c2pa7kfl2gm6qvamee34fbbkmq&amp;Style=FusionFx&amp;Options=d&amp;ViewState=egpscuvajonthfs8esaug5qhku&amp;ItemName=Overview1&path=%2fshared%2fComponents%2ftest1%2fOverview1&Format=excel2007&Extension=.xlsx');"></a>

Lets create something useful from it:

<a href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go&amp;ViewID=o%3ago%7er%3areport&amp;Action=Download&amp;SearchID=c2pa7kfl2gm6qvamee34fbbkmq&amp;Style=FusionFx&amp;Options=d&amp;ViewState=egpscuvajonthfs8esaug5qhku&amp;ItemName=Overview1&path=%2fshared%2fComponents%2ftest1%2fOverview1&Format=excel2007&Extension=.xlsx');return false">Export</a>

Next I'll break it to lines for readability (code will stop working).

a href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go

&amp;ViewID=o%3ago%7er%3areport

&amp;Action=Download

&amp;SearchID=c2pa7kfl2gm6qvamee34fbbkmq

&amp;Style=FusionFx

&amp;Options=d

&amp;ItemName=Overview1

&path=%2fshared%2fComponents%2ftest1%2fOverview1

&Format=excel2007

&Extension=.xlsx'

);return false">Export2</a>

I don't want the ViewID and SearchID in the code. Similarly, the style part will have to go.

The result is not only cleaner, but better transportable:   

<a href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go

&amp;Action=Download

&amp;Options=d

&amp;ItemName=Overview1

&path=%2fshared%2fComponents%2ftest1%2fOverview1

&Format=excel2007

&Extension=.xlsx'

);return false">Export2</a>

 I'll remove the line breaks

<a href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go&amp;Action=Download&amp;Options=d&amp;ItemName=Overview1&path=%2fshared%2fComponents%2ftest1%2fOverview1&Format=excel2007&Extension=.xlsx'

);return false">Export2</a>

All this division to ItemName and Path seemed cumbersome to so I united them (in path) and removed ItemName.

<a href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go&amp;Action=Download&amp;Options=d&amp;path=%2fshared%2fComponents%2ftest1%2fOverview1&Format=excel2007&Extension=.xlsx'

);return false">Export3</a>  
I'll mark in yellow the parts you need to change, if you want to use it:
1. The Path 
2. The string the user sees in the link.

<a href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go&amp;Action=Download&amp;Options=d&amp;path=%2fshared%2fComponents%2ftest1%2fOverview1&Format=excel2007&Extension=.xlsx'

);return false">Export3</a>  

Now, as Satya advised, you can place it in a Text Object in Dashboard or Narrative in an Analysis (remember the Contains HTML mark, in both cases).

For example the following code:
<a href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go&amp;Action=Download&amp;Options=d&amp;path=/shared/Components/test1/Overview1&Format=excel2007&Extension=.xlsx');return false">Export to Excel</a>

Used in Dashboard text:

Used in Narrative:

Please note, the Java-Script option is sometimes sensitive and not as stable as the URL.