Monday, August 12, 2013

FYI: GoURL Primer with OBIEE 11g

11:15 AM

Share it Please
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:

ParametersSyntaxDefinition
NQUser&NQUser=xUser ID
NQPassword&NQPassword=xPassword
Path&Path=xPath 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=xThe 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=printResults are in a printer-friendly format, without the paging controls, hot links, and so on.
Passing Filters&Action=NavigateTo 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=xThis shows an individual result view rather than the default compound view
Specific View&ViewID=go~xThis shows an individual result view rather than the default compound view where x is the name of the view
Specific Style&Style=xThis shows the results using a specified style. If the style does not exist, the default is used.
Result Format&Format=xThis 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=.csvThis controls the file extension of the download file
Language&Lang=frThis controls the language of the report. The value permitted are the values of weblanguage
done&done=portalPagesparameter 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:

OperatorMeaning
eqEqual to or in.
neqNot equal to or not in.
ltLess than.
gtGreater than.
geGreater than or equal to.
leLess than or equal to.
bwithBegins with.
ewithEnds with.
canyContains any (of the values in &P3).
callContains all (of the values in &P3).
likeYou 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.
betBetween (&P3 must have two values).
nullIs null (&P3 must be 0 or omitted).
nnulIs not null (&P3 must be 0 or omitted).
&P2=ttt.cccIn 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+...+zzzIn 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

7 comments:

  1. excellent piece of information, I had come to know about your website from my friend kishore, pune,i have read atleast 8 posts of yours by now, and let me tell you, your site gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanx a lot once again, Regards, obiee training in hyderebad

    ReplyDelete
  2. Hi,
    I have been trying to get the Html response using go url. I followed this tutorial to do so. But unfortunately I am not able to get the HTML for the report. All i get is a HTML with signing in image with a javascript call to load the report once logged in. In my case I need full report HTML. Any Idea how can i achieve this?

    Thanks In advance :)
    Vishal

    ReplyDelete
  3. Your post is really great. Thank you for taking time to provide us this exclusive information with us. I really enjoyed by reading your blog.


    SAP FICO Training in Chennai

    ReplyDelete
  4. It’s really amazing that we can record what our visitors do on our site. Thanks for sharing this awesome guide. I’m happy that I came across with your site this article is on point,thanks again and have a great day. Keep update more information..

    House keeping Services in Mumbai

    ReplyDelete
  5. ERPTREE Offering Oracle fusion HCM online training, Oracle Fusion SCM Online Training, Oracle fusion financials online training, Oracle fusion hcm training, Oracle fusion scm training, Oracle fusion financials training,
    Oracle fusion dba online training in Hyderabad, Bangalore,Gurgon, Noida, India, Dubai, UAE, USA, Kuwait, UK, Singapore, Saudi Arabia, Canada,
    Delhi, Chennai, Kolkata, Pune, Mumbai, Ahmedabad.


    Oracle Fusion HCM Online Training

    ReplyDelete