OBIEE - Adding "Export Entire Dashboard to Excel' link to dashboard

In the past I wrote about OBIEE - Custom Export to Excel link. Lets talk about dashboards...

As you might know in OBIEE 11.1.1.7 there is an option to export Entire Dashboard or Specific Dashboard page to excel. It exports the current page /dashboard. Here we will describe how to add such link to an Analysis or Dashboard and select the Dashboard manually.

(Done with OBIEE 11.1.1.7.140415)

This is what you can see on your OBIEE 11.1.1.7:

If you check the code behind it, you will see:

That is (with extra line breaks):

a class="NQWMenuItem NQWMenuItemWIconMixin" name="html"
href="javascript:void(null)"
onclick="return saw.dashboard.exportToExcel('/users/weblogic/_portal', 'page 1', false);"
id="popupMenuItem"
tabindex="0"
role="menuitem"
aria-label="Export Entire Dashboard">
<table class="MenuItemTable" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td class="MenuItemIconCell">
<img alt="" src="res/sk_blafp/catalog/exporttoexcel_ena.png">
</td>
<td class="MenuItemTextCell">Export Entire Dashboard</td>
</tr></tbody></table></a>

Since I prefer minimal code, it's:

<a href="javascript:void(null)" onclick="return saw.dashboard.exportToExcel('/users/weblogic/_portal', 'page 1', false);">Export Entire Dashboard</a>

Where:

• "Export Entire Dashboard" is just the string we present, the one with Hyperlink. 
• '/users/weblogic/_portal' is the path to dashboard. In this case it's "My Dashboard" of user weblogic.
• 'page 1' is the page name in Dashboard.
• false in "saw.dashboard.exportToExcel('/users/weblogic/_portal', 'page 1', false)" means to export Entire Dashboard, while true would export only 'page1', or any other page of the second parameter.

Now we can add it to Narrative or text in the Dashboard... And that's all.

Narrative example in an analysis:

Here is an example of exporting only page1 from dashboard test1:

<a href="javascript:void(null)" onclick="return saw.dashboard.exportToExcel('/shared/test1', 'page 1', true);">Export test1 Dashboard</a>

Adding text object to dashboard with that content:

Running it from the Dashboard:

 

The excel file is ready:

The top link in the above example is slightly more interesting since it exports the entire "My Dashboard" of user weblogic (naturally there is a security issue for non weblogic users) from another dashboard. It's the same code we have seen in the narrative above.

It works as well:

You might ask, how can I make it dynamic, so any users ca see his own "My dashboard"?

Well lets create an analysis with 1 column: VALUEOF(NQ_SESSION.USER)

And the following Narrative would do the job:

<a href="javascript:void(null)" onclick="return saw.dashboard.exportToExcel('/users/@1/_portal', 'page 1', false);">Export Entire Dashboard1</a>

(all I did was to replace "weblogic" in previous narrative with @1)

The next question is how to do the same with GO URL? I have no idea at the moment.

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.

OBIEE - creating new SmartView connection

I talked about Smart View in a previous post: OBIEE 11.1.1.7 - The Excel Ready Version, including Smart View. So we know Smart View is Hyperion tool that enables you to create new reports and run existing reports/Analysis, directly from Excel.
We know you can download the 32bit Office version from OBIEE home page and 64bit version from Oracle.

I wanted to write this post specifically how to create Smart View connections to OBIEE. All 3 types of them.

I use here the new Smart View 11.1.2.3.0, since I already upgraded to OBIEE 11.1.1.7.1 as described here: Installing OBIEE patch set 11.1.1.7.1 on Windows
I'm not aware of major differences regarding this post.


When we are using Smart View with OBIEE, there are 3 types of connections to OBIEE we can create in Smart View:

1. Private

2. Shared Local

3.Shared on the Server

Lets review the 3 options:

Private Connection:

From Smart View ribbon, click Panel.

Select Private Connection.

Press the right arrow and Create New Connection.

Select Oracle BI EE.

Enter the URL http://servername:port/analytics/jbips

In my case http://bdahab-lap:9704/analytics/jbips

Enter the user / password.

Save it as Private Connection.

Now you can start working.

Press Connect. Enter Password if needed...

You can select anything from the connection to run.

After you do it, you can see the Oracle BI EE menu.

And create new reports with Smart View.  (I couldn't find any option to open this menu without opening an existing report first... Anyone?).

Next time we enter Excel, we can select Panel -> Private Connection again. 

This time we only need to use the previous connection from the pull down.

Shared Local Connection

Create an XML file. For example SmartView.XML. All we care for is the XML extension.

In the file I used the following content:

 <?xml version="1.0" encoding="UTF-8"?>
<res_GetProvisionedDataSources>
<Product providerType="ExtensionProvider" id="27EE9B0D-D5F5-42c9-AFC5-44FEA770C693"
name="OBI EE-11.1.1.7" displayVersion="version">
<Server name="Oracle BI, Fusion Edition" context="OBI:http://bdahab-lap:9704/analytics/jbips"></Server>
</Product>
</res_GetProvisionedDataSources>

• Replace bdahab-lap:9704 with your server name and port.
• Replace "Oracle BI, Fusion Edition" (Server Name), with the Name you want to see in the connection in Smart View.

Save the file.

Copy this file (SmartView.xml) to the bin directory of SmartView installation (other locations are possible). In my case it's C:\oracle\SmartView\bin.

Enter Excel, in Smart View ribbon select Options / advanced.

Update the Shared Connections URL to point to your XML file. In my case: C:/oracle/SmartView/bin/SmartView.XML
(Ensure that you use forward slashes (/) as separators in the file path)

Now you can use Smart View Shared local connection:
As usual Panel / Shared Connections

select your connection from the Pull Down and Enter password.

Shared Server Connection

Is very similar to Shared Local Connection. The only difference, is locating the XML file on a shared server. In OBIEE, the logical place is analyticRes, described here: analyticsRes - having your own place on the OBIEE. So I copied the same xml file as described  above (I copy / paste the same text, except the file name):

Create an XML file. For example SmartViewProviders.xml.


In the file I used the following content:

 <?xml version="1.0" encoding="UTF-8"?>
<res_GetProvisionedDataSources>
<Product providerType="ExtensionProvider" id="27EE9B0D-D5F5-42c9-AFC5-44FEA770C693"
name="OBI EE-11.1.1.7" displayVersion="version">
<Server name="Oracle BI, Fusion Edition" context="OBI:http://bdahab-lap:9704/analytics/jbips"></Server>
</Product>
</res_GetProvisionedDataSources>

• Replace bdahab-lap:9704 with your server name and port.
• Replace "Oracle BI, Fusion Edition" (Server Name), with the Name you want to see in the connection in Smart View.

Save the file.

Place the file in the analyticsRes directory. In my case I created SmartView directory in the analyticsRes directory (D:\or\MWHOME\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\ ) and placed the file there.

Now we have access to it using the URL: http://bdahab-lap:9704/analyticsRes/SmartView/SmartViewProviders.xml (pay attention to upper / lower case letters.)

Please note: when I tried to use the file SmartView.XML, I used in the previous section, it didn't work. It did work after I renamed the file to SmartViewProviders.xml . Why? Sorry, you should look for a deeper person.

Most of the materials here are from the Smart View Users guide. There you can find also possible settings of the XML file, to fit additional EPM products.

The Smart View 11.1.2.3 documentation is located here.

(we can get to Smart View docs from this EPM documentation page.)

OBIEE 11.1.1.7 - The Excel Ready Version, including SmartView

One of important features of OBIEE 11.1.1.7 is the drastic improvement in Excel integration. 

1. SmartView.
2. Real Excel file exports.
3. Export of Dashboards to Excel.

Lets Summarize OBIEE Office capabilities in 11.1.1.6 vs. 11.1.1.7 in a table:  

11.1.1.6	11.1.1.7
Office Add-in for Excel	SmartView for Excel
Office Add-in for Power Point	--No change --
Excel ready export	Real Excel format export
--	Export of Dashboards to Excel
--	SmartView in MS Word
BI Publisher Word Temple	--No change --
BI Publisher reports with local Excel	--No change --
BI Publisher Excel Template Builder	BI Publisher Excel Template Builder Enhancements

 
 Now lets see examples of the 3 points above:

SmartView

SmartView is Hyperion tool that enables you to create new reports and run existing reports/Analysis, directly from Excel..

From the Home page of OBIEE you can download SmartView

 Lets zoom in:

And install it (add-in to Excel).

Now you can run it from Excel Menu. 

To begin, from the Panel create a new connection. Select Oracle BI EE.

And Enter URL.

Here come a critical (not so well documented) part. The Smart View URL is your regular OBIEE URL with jbips/ addition. For example here is mine: http://bdahab-il:9704/analytics/jbips/

A better explanation how to create new connections for SmartView are here: OBIEE - creating Smart View connection. 

I will do just a basic example:
Once connected you can view the content of the BI Server and select content. For example a Dashboard Page:

You are asked in this case:

I selected all objects and the result is, all report one bellow the other (for several thousand rows, in this case).

 Once you are connected to BI Server additional option appears in the menu:

 And in this option, with View Designer, you can create things from scratch, by selecting Subject Area and assigning columns to Page/Section/Row/Column/Measure in several types of presentations:

So this is what I requested:

And this is the result:

Now you can actually work, add filters, redesign... Lots of thing, all of them later.

 Now I'll cover the other 2.

Real Excel Export

 Why is it important? 2 reasons:

1. You had issues when opening Excel exports on mobile devices in the past. There shouldn't be any problem any more.

 2. It will prevent many little "issues" that could happen in non real Excel format export.

Export of Dashboard to Excel

Here is an example:

I exported Entire Dashboard. And now have several sheets in Excel with all Dashboard pages:

Graphs are pictures but all the rest is real data. As you can see at the bottom, there are several sheets in the Excel.