OBIEE - GO URL, the old (Px) and the new (COLx) way

OBIEE had always the ability to run GO URL links and pass parameters. It is documented in many blogs (here, here, here...) and Oracle Documentation (6.3.2). Since version 11 there is additional option that is less documented. we will talk about both in this post.

(Done on OBIEE 11.1.1.7.140527) 

Px

The best know option uses Px parameters. For example, the basic URL looks like:

 saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate

or 

http://SERVERNAME:9704/analytics/saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate

When we want to pass parameters in the URL it comes in groups of 3:

1. Operator
2. Column
3. Value/s

The P0 parameter states how many such groups are passed.

So we might have one group:  p0=1&p1=opp&p2="column"&p3=value

Or 2 groups: p0=2&p1=opp1&p2="column1"&p3=value1&p4=opp2&p5="column2"&p6=value2

p0 is limited to 1-6.

From Oracle Documentation:

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"

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 (1+2 means 1 value that is 2):
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

COLx

Since OBIEE 11 there is additional method, that to the best of my knowledge is the recommended one.  It's not limited to 6 prompts and covers additional things such as hierarchies and Variable Prompts.

Basically we have a simpler solution: we can work in triplets of (with possible additional psa parameter):

• col - column
• op - operator
• val - value/values

with no P0 equivalent.
Since we can use this method to express much more, it might look a bit more complicated. It isn't.


For example lets take a sample dashboard and fill few parameters:

 Lets even select prompt value from the hierarchical column (the one that you won't find in criteria filter, but rather as Override with Prompt in Selection Step):

Now in dashboard menu I will select Create Prompted Link:

What is the result (with extra line breaks for readability and %20, %2F and %22 replaced by space, / and ")?  

http://MYSERVER:9704/analytics/saw.dll?Dashboard
&PortalPath=/shared/Sample Lite/_portal/QuickStart
&Page=Overview
&Action=Navigate
 
&col1="Products"."Products Hierarchy"
&hierid1=Products 
&dimid1=H1 Products
&tblnm1="Products"
&val1="Brands"+"FunPod"+"Product Types"+"Cell Phones"
&psa1="Sample Sales Lite"
 
&col2="Time"."Per Name Year"
&val2="2008"+"2009"+"2010"
&psa2="Sample Sales Lite"
 
&col3="Offices"."Office"
&val3="Foster Office"+"Tellaro Office"+"Eden Office"
&psa3="Sample Sales Lite"

Lets see few more examples:

Hierarchical Prompt without value:

&col1="Products"."Products Hierarchy"&hierid1=Products Hierarchy
&dimid1=H1 Products
&tblnm1="Products"
&op1=prompted
&val1="*"+"all"
&psa1="Sample Sales Lite"

Day of Year between 1 and 365:
&col2="More Time Objects"."Day Of Year"
&op2=bet
&val2="1"+"365"
&psa2="Sample Sales Lite"


What happens when using Prompt that is based on a column with Descriptor ID? We add the code &formulaUse1=code, where 1 is the same number as in col1, and in val we use the Descriptor ID value (in our case 18 instead of the product name).

&formulaUse1=code
&col1="Products"."Product"
&val1="18"
&psa1='Sample Sales Lite"

We see few interesting things:

• There is no P0 equivalent, the groups are numbered sequentially. 
• When using several values, we don't have to set the number of values.
• For non hierarchical prompts, the basic set is of &col, &op, &val, &psa.
• For Hierarchical prompts, the basic set alo includes &hierid, &dimid, &tblnm.
• When the operator is equal, it is omitted.
• The code runs fine (in standard cases) without &psa as well. 

 So to summarize:

For x, the number of the parameter in GoURL:
&colx - Table_Name.Column_Name
&opx - operator (the table in the Px section above is useful for possible values). Omitted for op1=eq. 
&valx - the value of prompt. Unlike p3 we don't have to define the number of values. Enclose the value (") and if you have few values use the plus sign (+). Same is relevant for between values as well. Example "1"+"3"
&psax - the parameter Subject Area.

 Only for Hierarchies we use also:
&colx - Table_Name.Hierarchy_Name (in presentation)
&hieridx - ???
&dimidx - The Hierarchy name in Business Model
&tblnmx - The Table Name in Presentation
&valx - the values can be from different hierarchy levels

Example:

 

&col1="Products"."Products Hierarchy"
&hierid1=Products 
&dimid1=H1 Products
&tblnm1="Products"
&val1="Brands"+"FunPod"+"Product Types"+"Cell Phones"
&psa1="Sample Sales Lite"


Now what happens with Variable Prompt?

We have only &var and &val  (and &psa, if you insist):
 &var1=dashboard.variables['KUKIU']
&val1="1"
&psa1="Sample Sales Lite"

So the value of &var is: dashboard.variables['var_name']
 (%27 = ')