FYI: GoURL Primer with OBIEE 11g

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.

Oracle has provided limited documentation on goURL parameters and with use cases that require end users to navigate to specific dashboards or request dashboards be embedded into pre-existing web pages, i've decided to create a Primer on commonly used GoURL functionality:

Common goURL Parameters:

Parameters	Syntax	Definition
NQUser	&NQUser=x	User ID
NQPassword	&NQPassword=x	Password
Path	&Path=x	Path of the answer to execute. You can find it in the properties page (Answers/Manage Catalog/ and click on the properties icon (a little hand) of the object that you want. See picture above
Link Options	&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
Printer Friendly	&Action=print	Results are in a printer-friendly format, without the paging controls, hot links, and so on.
Passing Filters	&Action=Navigate	To apply filters to the answer (see section below on Passing Filters)
Application Friendly	&Action=Extract &Action=Scroll	Results are displayed in an application-friendly format, such as for Microsoft Excel, without the paging control, hot links, and so on. The Extract action also acts as a Navigate action (read 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	&ViewName=x	This shows an individual result view rather than the default compound view
Specific View	&ViewID=go~x	This shows an individual result view rather than the default compound view where x is the name of the view
Specific Style	&Style=x	This shows the results using a specified style. If the style does not exist, the default is used.
Result Format	&Format=x	This controls the format of the results. This is the format, where x can be xml, html, txt (tab separator), csv (comma separator)
File Extension	&Extension=.csv	This controls the file extension of the download file
Language	&Lang=fr	This controls the language of the report. The value permitted are the values of weblanguage
done	&done=portalPages	parameter allows you to create a return link equal to a path that you have specified

Passing Filters

You can use the &Action=Navigate in conjuction with the with the parameters below to pass filters directly to a report via goURL. Use &P0=n where n equals the number of parameters you wish to filter and P1...to P6 with the one or more of the operators below:

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. example: saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=top &P2=Customers.Region&P3="7

Passing Filters Examples:

his 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

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

Generating SQL Statements using goURL:

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.

 

keywords: OBIEE 11g, goURL, answers, ad-hoc, analysis, analytics