6 Integrating Oracle BI Presentation Services into Corporate Environments Using HTTP and JavaScript

This chapter describes the HTTP and JavaScript methods used to integrate Oracle BI Presentation Services into a corporate environment. Describes Go URL, how to use Go URL to use SQL to pass filters, and Dashboard URL.

This chapter includes the following sections:

Section 6.1, "Incorporating Oracle Business Intelligence Results into External Portals or Applications"
Section 6.2, "Referencing Dashboard Content in External Portals or Applications"
Section 6.3, "Using the Oracle BI Presentation Services Go URL to Issue SQL and Pass Filters"
Section 6.4, "Example of an Oracle Business Intelligence Third-Party SQL Tool Integration"
Section 6.5, "Retrieving Links to Dashboard Pages Using Scripts"

6.1 Incorporating Oracle Business Intelligence Results into External Portals or Applications

This section describes how to use the Oracle BI Presentation Services Go URL to incorporate results into external portals or applications. It contains the following topics:

Section 6.1.1, "About the Oracle BI Presentation Services Go URL"
Section 6.2.2, "Structure of the Basic Oracle BI Presentation Services Dashboard URL"
Section 6.1.3, "Optional Parameters for the Oracle BI Presentation Services Go URL"

6.1.1 About the Oracle BI Presentation Services Go URL

The Oracle BI Presentation Services Go URL command is for use in incorporating specific Oracle Business Intelligence results into external portals or applications. The Go URL is used when you add a result to your favorites, or add a link to a request to your dashboard or an external Web site. It has a number of forms and optional arguments that can be used to control its behavior.

Note that you can use frame busting to prevent attackers from framing an application in an inline frame. For more information about using this method of security, see "Protecting Pages in Oracle BI EE from Attack" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

You can post the Go URL as a Form or issue it as a URL. If you are issuing parameters as part of a URL, they need to be escaped properly. You need to replace spaces with plus ( + ) signs, and so on. For example, to pass East Region as a value, type East+Region.

When called from within an Oracle BI Presentation Services screen, such as a dashboard or an HTML result view, the URL should begin with the following characters:

saw.dll?Go

When called from another screen on the same Web server, the URL should begin with the following characters:

/analytics/saw.dll?Go

When referenced from a screen on a different server (or sent through email, and so on), the URL should begin with the fully qualified server name or IP address:

http://server_name_or_ip_address/analytics/saw.dll?Go

When invoking Go URL, you must make sure that a User-Agent string header is set.

To test these commands, you can enter the fully qualified version into the Address field of the browser.

6.1.2 Structure of the Basic Oracle BI Presentation Services Go URL

The basic Go URL command needs the full catalog path to the request to execute. It returns the default result view, which is defined in the request.

For example, the following go URL command returns the default result view as defined in the request, where SB2 is the name of the request to execute.

saw.dll?Go&Path=%2Fshared%2FTest%2FSB2

6.1.3 Optional Parameters for the Oracle BI Presentation Services Go URL

You can modify the behavior of the Go URL command by adding one or more of the following parameters. If an invalid URL is specified (for example, you type a parameter incorrectly), the browser displays a "The page cannot be found" error with the detailed text of "HTTP 400 - Bad Request."

Note:

In parameter descriptions, SB2 is the name of the request to execute.

User ID and Password. The user is prompted for user ID and password if this information has been omitted from the request.

This is the format, where uuu is the user ID and ppp is the password:
```
&NQUser=uuu&NQPassword=ppp
```
Example:
```
saw.dll?Go&Path=/Shared/Test/SB2&NQUser=user1&NQPassword=rock
```
This logs on as user1 with a password of rock, and executes the request.
Link Options. The results will include links.

This is the format:
```
&Options=x 
```
The x can be one or more of the following letters:
- m = Modify Request
- f = Printer Friendly
- d = Download to Excel
- r = Refresh Results
Example:
```
saw.dll?Go&Path=/Shared/Test/SB2&Options=md
This displays results with the links Modify Request and Download.
```
Printer Friendly. Results are in a printer-friendly format, without the paging controls, hot links, and so on.

This is the format:
```
&Action=print
```
Example:
```
saw.dll?Go&Path=/Shared/Test/SB2&Action=Print
```
Application Friendly. Results are displayed in an application-friendly format, such as for Microsoft Excel, without the paging control, hot links, and so on.

This is the format:
```
&Action=Extract
```
Example:
```
saw.dll?Go&Path=/Shared/Test/SB2&Action=Extract
```
The Extract action also acts as a Navigate action (read Section 6.3.2, "Passing Filters to the Oracle BI Presentation Services Go URL Through a URL (Navigation)") so you can filter the results that are returned by the call.
Specific View. This shows an individual result view rather than the default compound view.

This is the format, where xx is the name of the view:
```
saw.dll?Go&Path=/Shared/Test/SB2&ViewName=xx
```
Example:
```
saw.dll?Go&Path=/Shared/Test/SB2&ViewName=Chart
```
Assuming that the request contains a Chart view named Chart, this displays just the Chart view.
Specific Style. This shows the results using a specified style. If the style does not exist, the default is used.

This is the format, where xx is the name of the style:
```
saw.dll?Go&Path=/Shared/Test/SB2&Style=xx
```
Example:
```
saw.dll?Go&Path=/Shared/Test/SB2&Style=Lime
```
This uses the style named Lime to show the results.
Result Format. This controls the format of the results.

This is the format, where xx is XML or HTML:
```
saw.dll?Go&Path=/Shared/Test/SB2&Format=xx
```
Example:
```
saw.dll?Go&Path=/Shared/Test/SB2&Format=XML
```
This shows results in XML.

6.1.3.1 Displaying All Records in a Table

There are two ways to display all the records in the table:

Set the Rows per Page property on the Table view to 10,000, and then use the basic Go. This is the easier of the two methods.

Issue the following URL:

saw.dll?Go&Path=/users/Administrator/a&Action=Scroll&P5=-1&ViewID=o:go~r:report~v:compoundView!1~v:tableView!1

6.2 Referencing Dashboard Content in External Portals or Applications

This section describes how to use the Oracle BI Presentation Services Dashboard URL. It contains the following topics:

"About the Oracle BI Presentation Services Dashboard URL"
"Structure of the Basic Oracle BI Presentation Services Dashboard URL"
"Optional Commands and Parameters for the Oracle BI Presentation Services Dashboard URL"

6.2.1 About the Oracle BI Presentation Services Dashboard URL

The Oracle BI Presentation Services Dashboard URL is for use in incorporating or referencing the content of a specific dashboard in external portals or applications. It has a number of forms and optional arguments that can be used to control its behavior.

You can post the Dashboard URL command as a Form or issue it as a URL. If you are issuing parameters as part of a URL, they need to be escaped properly. You need to replace spaces with plus ( + ) signs, and so on. For example, to pass East Region as a value, type East+Region.

When called from within an Oracle BI Presentation Services screen, such as a dashboard or an HTML result view, the URL should begin with this:

saw.dll?Dashboard

When called from another screen on the same Web server, the URL should begin with this:

/analytics/saw.dll?Dashboard

When referenced from a screen on a different server (or sent through email, and so on), the URL should begin with the fully qualified server name or IP address:

http://server_name_or_ip_address/analytics/saw.dll?Dashboard

To test these commands, you can enter the fully qualified version into the Address field in the browser.

6.2.2 Structure of the Basic Oracle BI Presentation Services Dashboard URL

The basic Dashboard URL command needs no parameters. It displays the user's default portal after authenticating the user.

The following is the format of the basic Dashboard URL command:

http://server_name_or_ip_address/analytics/saw.dll?Dashboard&PortalPath=path of your dashboard

Note that PortalPath is the path of the dashboard and that when the user clicks the URL, the user is taken to the first page of the dashboard.

Example

http://localhost:9704/analytics/saw.dll?Dashboard&PortalPath=/shared/Paint Demo/_portal/Paint Dashboard

You can add the optional Page argument to specify which dashboard page displays when the user clicks the URL. Use the page number from the dashboard page tab as the value for this argument. This is the format of the URL including the Page argument.

http://server_name_or_ip_address/analytics/saw.dll?Dashboard&PortalPath=path of your dashboard&Page=page of your dashboard

Example

http://localhost:9704/analytics/saw.dll?Dashboard&PortalPath=/shared/Paint Demo/_portal/Paint Dashboard&Page=page 3

6.2.3 Optional Commands and Parameters for the Oracle BI Presentation Services Dashboard URL

You can add optional commands and parameters to the Dashboard URL. The below examples show and explain various uses of commands and parameters.

User ID and Password Example

You can modify the behavior of the Dashboard URL command by adding the user ID and password parameters. If the parameters are omitted, then the user is prompted for user ID and password information, unless the user chose the option to have logon information remembered when last logged on. If using a Session ID or Ticket, pass it as the NQUser parameter. In the parameter description, SB2 is the name of the request to execute.

This is the format where the user ID is uuu and the password is ppp:

&NQUser=uuu&NQPassword=ppp

Example URL:

http://localhost:9704/analytics/saw.dll?Dashboard&PortalPath=/shared/Paint Demo/_portal/Paint Dashboard&NQUser=user1&NQPassword=rock

PortalPages Example

You can modify the behavior of the Dashboard URL command by adding the PortalPages command. This command opens a dashboard page without the common header. You can use this command to create a link or image with the specified dashboard page as the destination and open the dashboard page in a new browser window.

This is the format where the catalog path portal page is xxx and the dashboard page name is yyy.

PortalPages&PortalPath=xxx&Page=yyy&Done=close

Note the following command descriptions:

PortalPath – This parameter contains the dashboard's catalog path.
Page – (Optional) This parameter contains the dashboard page name.

Example URL:

http://localhost:8080/analytics/saw.dll?PortalPages&PortalPath=%2fshared%2fdashboardfolder1%2f_portal%2fdefault&Page=page%202&Done=close

Go Example

You can modify the behavior of the Dashboard URL command by adding the Go command and specifying the catalog path to an analysis. This command opens an analysis without the common header. You can use this command to create a link or image with the specified analysis as the destination and open the analysis in a new browser window.

This is the format where the path is xxx and Done is close.

Go&Path=xxx&Done=close

Note the following parameter descriptions:

Path – This parameter contains the analysis' catalog path.
Done – This parameter is obsolete for the analysis' path.

Example URL:

http://localhost:8080/analytics/saw.dll?Go&Path=%2fusers%2fadministrator%2fdashboard_actionlink_report&Action=Prompt&Done=close

PortalGo Example

You can modify the behavior of the Dashboard URL command by adding the PortalGo parameter and specifying the catalog path to the analysis. This parameter opens an analysis in a dashboard. You can use this parameter to create a link or image with an analysis as the destination, and open the analysis in the existing browser window.

This is the format where the portal path is xxx, the path is yyy, and Done is zzz.

PortalGo&PortalPath=xxx&Path=yyy&Done=zzz

Note the following parameter descriptions:

PortalPath – This parameter is the dashboard's catalog path. It is in this dashboard that the analysis is displayed.
Path – This parameter is the analysis' catalog path.
Done – This parameter is used by the Return link and contains the return location.

Example URL:

http://localhost:8080/analytics/saw.dll?PortalGo&PortalPath=%2fusers%
    2fadministrator%2f_portal&Path=%2fusers%2fadministrator%2fSalesInMarket

6.3 Using the Oracle BI Presentation Services Go URL to Issue SQL and Pass Filters

This section explains how to use the Go URL command to issue SQL, and how to pass filters to be used for navigation. It contains the following topics:

Section 6.3.1, "Issuing SQL Commands and Passing Filters"
Section 6.3.2, "Passing Filters to the Oracle BI Presentation Services Go URL Through a URL (Navigation)"

6.3.1 Issuing SQL Commands and Passing Filters

The Go URL command can be used to issue Oracle Business Intelligence SQL. These forms of the Go URL return tabular results. The basic options from &Style= and &Options= can be used here as well.

To issue Oracle Business Intelligence's simplified SQL, include the escaped SQL as a parameter to the Go URL. For example:

saw.dll?Go&SQL=select+Region,Dollars+from+SupplierSales

where the FROM clause is the name of the Subject Area to query.

Alternatively, the command IssueRawSQL can be used to bypass the Web processing and issue SQL directly against the BI Server.

6.3.2 Passing Filters to the Oracle BI Presentation Services Go URL Through a URL (Navigation)

The Go URL can also be used to pass context such as filters to a destination request. This is done by adding additional parameters to the call. You need to make sure that any columns you are passing are set up in the destination with Is Prompted filters, or specific default filters.

6.3.2.1 Navigation Parameters

The basic syntax of the navigation command is the same as presented in the section Section 6.2.2, "Structure of the Basic Oracle BI Presentation Services Dashboard URL", but with the addition of the Action=Navigate parameter, and then population of the P1 - Pn parameters, as necessary.

By default, you can add up to 100 parameters to the URL. However, you can adjust the number of parameters by modifying the Prompts/MaxPromptedURLParams setting in instanceconfig.xml. For more information see "Using a Text Editor to Update Configuration Settings" in the Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

&Action=Navigate

&P0=n where n is the number of columns you wish to filter, currently 1 - 6.

&P1=op where op is one of the following operators.

Operator	Meaning
eq	Equal to or in.
neq	Not equal to or not in.
lt	Less than.
gt	Greater than.
ge	Greater than or equal to.
le	Less than or equal to.
bwith	Begins with.
ewith	Ends with.
cany	Contains any (of the values in &P3).
call	Contains all (of the values in &P3).
like	You need to type %25 in place of the usual % wildcard. See the examples that follow.
top	&P3 contains 1+n, where n is the number of top items to display.
bottom	&P3 contains 1+n, where n is the number of bottom items to display.
bet	Between (&P3 must have two values).
null	Is null (&P3 must be 0 or omitted).
nnul	Is not null (&P3 must be 0 or omitted).
&P2=ttt.ccc	In this parameter, ttt is the table name and ccc is the column name. If the table or column contains spaces, it must be quoted with double-quotes. Spaces should be escaped as %20, for example, Measures."Dollar%20Sales".
&P3=n+xxx+yyy+...+zzz	In this parameter, n is the number of values, and xxx, yyy, and zzz are the actual values. Note: If the value of P3 begins with a numeric character, the entire value must be enclosed in quotes. For example: saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=top&P2=Customers.Region&P3="7West"

Note:

The settings for &P1,&P2, and &P3 are repeated for &P4-P6, &P7-P9, &P10-P12, &P13-P15, and &P16-P18 as necessary, depending on the value of &P0.

6.3.2.2 Navigation Examples

This returns records for the East and Central regions:

Saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=eq&P2=Customers.Region&P3=2+Central+East

This returns records for like Regions E....t:

saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=like&P2=Customers.Region&P3=1+E%25t

This returns the top two regions by dollars sold:

saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=top&P2="Sales%20Facts".Dollars&P3=1+2

This is an example where the number of arguments is not included in the syntax:

saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=top&P2=Customers.Region&P3=Central

Note:

You can omit the number of arguments only if just one argument value is included.

This returns records with between 2,000,000 and 2,500,000 in sales:

saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=top&P2="Sales%20Facts".Dollars&P3=2+2000000+2500000

This returns records for Regions beginning with the letter E:

saw.dll?Go&Path=vate&P0=1&P1=bwith&P2=Customers.Region&P3=1+E

This returns records for Regions containing the letter E and having more than 20 million in sales:

saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=2&P1=cany&P2=Customers.Region&P3=1+e&P4=gt&P5="Sales%20Facts".Dollars&P6=1+20000000

Oracle BI Presentation Services navigation is currently supported from charts, table and pivot table views, HTML views, and external applications and Web pages. The destination search should have filters defined on columns for which it wants to receive context. These can be specific filters or, usually, the Is Prompted filter. In addition to the Table.Column value specifically referenced in the navigation call, all filters from the source request that have corresponding table.columns in the destination, are applied to the destination. Therefore, the appropriate context from a source can be passed to the destination.

6.3.2.3 Navigation Using JavaScript

Navigation can currently be accomplished using the custom text/date formatter for a column. The central concept is that you add a column you want to navigate from to your search. You then choose Custom Text Format from the properties for the column, and enter HTML that calls one of the two provided JavaScript functions. This technique can be used to perform many actions, including sorting columns, calling custom JavaScript functions, and so on.

Oracle BI Presentation Services includes two JavaScript functions that enable navigation from Table and Pivot views: GoNav and PortalNav. (These functions are located in ORACLE_HOME/bifoundation/web/app/res/b_mozilla/viewhelper.js.) The former handles navigation to a specific search. The latter handles navigation to a specific dashboard. A description of their syntax follows, along with example Custom Text formats that you can use to implement navigation.

Note:

To control the look of the navigable text using the style sheet, Oracle BI Presentation Services is standardized on the class=Nav.

GoNav function

function GoNav(event, sPath, sTbl, sCol, sVal, sTarget)

where:

event = event indicator.

sPath = the catalog path of the destination search.

sTbl = the logical table name to filter.

sCol = the logical column name to filter.

sVal = the value to filter by.

sTarget (optional) = "_blank" to open a new browser window with the results.

Sample Custom Text Format for GoNav Call

The GoNav and PortalNav calls can be wrapped in an HTML statement (include the quotes):

[html]"<font class=nav onclick=\"JavaScript:GoNav(event, '/shared/topaz/performance/transaction details','
    Transaction','Quality','"@"');\">"@"</font>

Table 6-1 explains the elements of this example.

Table 6-1 Elemental Analysis of a GoNav Call

Element	Description
[html]	Tells Oracle BI Presentation Services to interpret the following text as HTML. Note that every "less than" character (<) must be preceded by a double quote (") if the intent is to use it in an HTML tag.
"<font	An HTML tag that a JavaScript call can be attached to. You could potentially use <div>, <span>, <a>, and so on.
class=nav	The CSS style class used for formatting of the HTML tag.
onclick=\"JavaScript:GoNav('event, /shared/topaz/performance/transaction details','Transaction','Quality','"@"');\"	The method to call a JavaScript function. When the user clicks on the contents of this HTML tag, then the JavaScript function is called.
>	The end of the font tag.
"@"	Instructs Oracle BI Presentation Services to replace the at sign (@) with the actual column value. When [html] is used, the @ symbol must be enclosed with double quotation marks (").
</font>	The closing tag to match the <font> tag.

This example of GoNav places this HTML on the dashboard:

<a href="javascript:GoNav(event, '/shared/topaz/performance/transaction details','Transaction','Quality','Some value');">Click here to navigate to Transaction Details with ''Some value'</a>

PortalNav Function

function PortalNav(event, sPortal,sTbl,sCol,sVal)

event = event indicator.

sPortal = the catalog path of the destination portal.

sTbl = the logical table name to filter.

sCol = the logical column name to filter.

sVal = the value to filter by.

Sample Custom Text Format for PortalNav Call

Make sure to include the quotes exactly as shown.

"<font class=nav onclick=\"JavaScript:PortalNav(event, '/shared/topaz/_portal/transaction analysis','
    Transaction','Type','"@"');\">"@"</font>"

6.3.2.4 Navigation from HTML Results

This is the same as described in Section 6.3.2.3, "Navigation Using JavaScript", but rather than using a custom formatter, type in the HTML syntax with static values in place of the @ signs.

6.4 Example of an Oracle Business Intelligence Third-Party SQL Tool Integration

This section illustrates the requirements for integrating a third-party SQL tool with Oracle Business Intelligence by describing an example integration, using Microsoft Access. Because Oracle Business Intelligence is designed as a middleware platform for enterprise data access and integration, common report writers and business intelligence tools can communicate natively with the BI Server.

Most third-party SQL tools require the user to include join conditions within queries to avoid cross-joins. A cross-join occurs when a request does not have a WHERE clause, which, in turn creates a Cartesian product of the tables involved in the join. The size of a Cartesian product is the number of rows in the first table multiplied by the number of rows in the second table.

To integrate Microsoft Access with the BI Server, the BI Server Administrator must expose the keys within the Presentation layer of the Oracle BI Administration Tool.

6.4.1 Example of integrating a third-party SQL tool

Drag and drop the keys from the Business Model and Mapping layer to the Presentation layer and save the repository.
Open Microsoft Access, select the option Blank Access Database, type the name oracle-analytics.mdb when prompted, and click Create.
After creating the new Microsoft Access database, right-click in the white section of the screen and select Link Tables.
From the Files of Type drop-down list box, select ODBC Databases.

The Select a Source Dialog appears, and prompts you for a Data Source Name.
Click the Machine Data Source tab, locate the Analytics_Web DNS, and click OK.

The Oracle BI Server requires a login.
Type your user ID and password.

The Import Objects dialog box appears.
Click the Select All button, or highlight the desired logical tables from Oracle Business Intelligence.

The import may take a while to complete.
When the import completes, right-click in the white section of the screen and select Relationships:
1. Add the desired tables and drag and drop the keys from the dimension tables (Period, Market, Product) to the fact table (Sales Measures).
2. Drag and drop Period Key over the perkey column, and repeat for each corresponding key to create the joins.
Now, you can test and run a request.
Select Create query in Design view from the Queries button:
1. Select Markets, Products and Sales Facts.
2. Add Region, Brand, Units and Dollars, respectively, and then click Run.

6.5 Retrieving Links to Dashboard Pages Using Scripts

You can retrieve links (both bookmark links and prompted links) to dashboard pages by using the JavaScript functions described in Table 6-2 in your custom scripts on your dashboard pages.

Note:

For these JavaScript functions to work, the HardenXSS element must be set to false. For more information, see "Making Advanced Configuration Changes for Presentation Services" in the Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

For more information about bookmark links and prompted links, see "About Creating Links to Dashboard Pages" in Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition.

Table 6-2 JavaScript Functions for Retrieving Links to Dashboard Pages

JavaScript Function Description

JavaScript Function	Description
`LinkToPage(bInlineDrill,bGetBookmarkOnly)`	Sets the value of the variable `saw.bookmarkURL` to the bookmark link. Set the arguments as follows: `bInlineDrill` — Set to true if the Page Options button is displayed on the page; otherwise set to false. `bGetBookmarkOnly` — Set to true to prevent the bookmark URL from being refreshed in the Address Bar of the browser. Set to false (or omit) to allow the bookmark URL to be refreshed in the Address Bar of the browser.
`GetPURL()`	Returns the prompted link as a string.

LinkToPage(bInlineDrill,bGetBookmarkOnly)

Sets the value of the variable saw.bookmarkURL to the bookmark link.

Set the arguments as follows:

bInlineDrill — Set to true if the Page Options button is displayed on the page; otherwise set to false.
bGetBookmarkOnly — Set to true to prevent the bookmark URL from being refreshed in the Address Bar of the browser. Set to false (or omit) to allow the bookmark URL to be refreshed in the Address Bar of the browser.

GetPURL()

Returns the prompted link as a string.