Wednesday, February 26, 2014

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:


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.

30 comments:

  1. Hi Boris,
    Great post! Helped me a lot!
    Thanks
    Alex

    ReplyDelete
  2. will this one pull all the rows returned by the query, or max of 2500 rows?

    ReplyDelete
  3. It uses your system defaults defined in the EM, as it should.
    You can set any number greater than 2500 in Enterprise Manager.

    ReplyDelete
  4. am I able to create a URL that exports the XLS to a given mapped path, like d:\server\obieereports?

    ReplyDelete
    Replies
    1. You can export a specific analysis. Nothing more.

      Delete
  5. can we export all page reports(more than 1) in to different sheets(1 st report in sheet1 and 2nd report in sheet 2........n th report in n th sheet).

    Thanks in advance

    ReplyDelete
    Replies
    1. Don't know about each report in a page... I guess BI Publisher will do it, if you develop the report this way. But here is a link to export the dashboard with several pages: http://obieeil.blogspot.co.il/2014/07/obiee-adding-export-entire-dashboard-to.html

      Delete
  6. how to pass the dashboard prompt parameters to the report embedded inside the GO URL? I was able to export my report, but the dashboard prompt parameters are not getting passed.

    ReplyDelete
  7. As usual :). see for example Gerard Nico: http://gerardnico.com/wiki/dat/obiee/url_filter

    It's the older version of passing parameters, but it's working. For example exporting to excel the analysis /Shared/Sample/countries, with 1 parameter that states COUNTRIES.COUNTRY_NAME='Brazil'
    http://bdahab-il:9704/analytics/saw.dll?Go&path=/Shared/Sample/countries&Action=Navigate&P0=1&P1=eq&P2=COUNTRIES.COUNTRY_NAME&P3=%22Brazil%22&Format=excel2007&Extension=.xlsx

    ReplyDelete
  8. I have table structure is with spaces with two words. How can do that to Path? I tried all possible way but it is not working. Can you show me alternate way to do it.

    Thanks,
    Galav

    ReplyDelete
    Replies
    1. Example Share Folders/abc/abcd efg/hijk/"Report Name"

      Delete
    2. Usually you can write it without any special changes:
      Shared/abc/abcd efg/hijk/Report Name
      (Shared and not Shared Folders)
      If you are very troubled by the space you can write the explicit %20 :
      Shared/abc/abcd%20efg/hijk/Report%20Name

      Delete
    3. Hello Boris,

      This is what I am using to get the report but seems like need to play around with HTML and I tied doing with your report. I am getting report there with this link but seems like I am trying to do Export to excel. can you please help me out to get the export code to my below code.

      Test1


      Thanks,
      Galav

      Delete
    4. href="/analytics/saw.dll?PortalGo&Action=prompt&path=%2Fshared%2FAMS%2FBenficiary%2FState%2FDetailed Reports%2FTest1&Format=excel2007&Extension=.xlsx
      );return false"

      Delete
    5. Galav, Why is your Action=prompt? The code you pasted here seems partial.

      Delete
    6. Yes this window is not allowing me to put HTML code so I did remove "" so I want clarification on this. I am not able to get excel out when I put code in to Text on dashboard or in report. So was curious to know.

      Delete
    7. Galav,
      I'll answer in 2 parts because of HTML/javascripts limitation.

      If you want a URL, the code shoul be:
      saw.dll?Go&path=/shared/AMS/Benficiary/State/Detailed Reports/Test1&Format=excel2007&Extension=.xlsx

      Delete
    8. If you want a javascript, it's (in 2 parts):
      <a href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go&Action=Download&Options=d&path=/shared/AMS/Benficiary/State/Detailed Reports/Test1&
      Format=excel2007&Extension=.xlsx');

      Delete
    9. return false">Export to Excel

      and the /a closure

      Delete
    10. This comment has been removed by the author.

      Delete
  9. Worked out. I did fix that. here's the code..
    href="/analytics/saw.dll?Download&Path=%2Fshared%2FAMS%2FBenficiary%2FState%2FDetailed Reports%2FDetailed Data for Year%2C Quarter%2C Indicator%2C Geographic Area and Race - 7 Catagories&Format=excel2007&Extension=.xlsx'
    );return false"target="_blank">Export

    ReplyDelete
  10. Just open "a" and close it after this.

    ReplyDelete
  11. Hi,

    This works fine but I found one exception though. I try to export individually some views. One option in the URL to do this is ViewName where wou have to provide the view ID found in the report xml.

    This works fine, however, if the view has a table prompt, the current selection is not exported. Oddly enough, the native link provided when you edit the view and export from there takes into account the table prompt selection.

    ReplyDelete
  12. I using below 2 codes but every time report is showing No result

    below is the code i used
    href="/analytics/saw.dll?Download&Path=%2fshared%2fDrug%20Safety%20Adverse%20Events%2fAdverse%20Event%20Test%20Prompt%2fLL%20QS%20%3e%201000&Format=excel2007&Extension=.xlsx');return false"target="_blank">Export

    enclosed in a tag

    href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go&Action=Download&Options=d&path=/shared/Drug Safety Adverse Events/Adverse Event Test Prompt/LL QS > 1000&Format=excel2007&Extension=.xlsx');return false">Export to Excel

    Both code is not working, it used to work before.

    I am using 11.1.1.9.160119 version

    ReplyDelete
  13. Pretty! This has been a really wonderful post.

    Thank you for supplying this info.

    ReplyDelete
  14. Keep on working, great job!

    ReplyDelete
  15. Hi,

    How I can export a dashboard page in CSV format using custom action link?

    ReplyDelete
  16. Incredible points. Outstanding arguments. Keep up the good effort.

    ReplyDelete