OBIEE 10G/11G - Passing Filters through the Go and Dashboard URL

About

The Go URL and the Dashboard Url can also be used to pass filter.

This is done by adding additional parameters to the call.

You need to make sure that any columns you are passing are set up to receive the filters value. These can be done by applying :

a specific filters
or the “Is Prompted” filter

Example :
On the image below, the answers can only be filtered on the column “Rule Name” and “Data Quality Metrics”.

The advanced SQL filter are not allowed.

An URL must be encoded before that you can use it.

All spaces must be replaced with the %20 sign
When you cast a number as char, you may have to trim it to suppress the blank character
When you pass a string variable, you may have to replace the blank character by %20

By using the Navigate to (Web Page|HTTP Request) actions parameters, they are automatically encoded.

Articles Related

Syntax

The basic syntax of the navigation command is the same as the Go URL and the Dashboard Url, but with the addition of the parameters Action, P0 and P1.

Parameters Format	Definition
&Action=Navigate
&P0=n	where n is the number of columns you wish to filter, currently 1 - 6
&P1=op	where op is one operators (equal, not equal, … see below)
&P(mod 2) = P2,P4,P6,..	the name of the column to filter
&P(mod 2 + 1) = P3,P5,P7,..	the value of the column to filter

Values are separated by pluses
the operator parameter is not needed for equal (eq)
Double quotes are also optional if there are no white spaces inside the values.
If the value of P3 begins with a numeric character, the entire value must be enclosed in quotes.
The settings for &P1,&P2, and &P3 are repeated for &P4-P6, &P7-P9, &P8-P10, &P11-P13, &P14-P16, and &P17-P19 as necessary, depending on the value of &P0.

For example:

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

Navigation Parameters

P1 parameters

Operator	Meaning	Sign
eq	Equal to or in	=
neq	Not equal to or not in	<>, not in
lt	Less than	<
gt	Greater than >
ge	Greater than or equal to	>=
le	Less than or equal to.	⇐
bwith	Begins with	Begins with
ewith	Ends with	Ends with
cany	Contains any (of the values in &P3)	Contain any
call	Contains all (of the values in &P3)	Contain all
like	You need to type %25 in place of the usual % wildcard. See the examples that follow.	Like
top	&P3 contains 1+n, where n is the number of top items to display.	Top n
bottom	&P3 contains 1+n, where n is the number of bottom items to display.	Bottom n
bet	Between (&P3 must have two values).	Between
null	Is null (&P3 must be 0 or omitted)	Is Null
nnul	Is not null (&P3 must be 0 or omitted)	Is Not Null
&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”.	Table.Column
&P3=n+xxx+yyy+…+zzz	In this parameter, n is the number of values, and xxx, yyy, and zzz are the actual values.	List of Parameters

Examples

Navigate to a Web Page

With the Navigate to a Web Page action and an Alias

Where:

the ps alias has the presentation server value https://host:port/analytics/
and we use the Go URL syntax: saw.dll?Go&Path=@{Path}&Action=@{Action}&P0=1&P1=eq&P2=@{P2}&P3=@{P3}

Dashboard Prompt

With the help of the pre-build presentation service variable dashboard.currentPage.path, it's possible to build an URL to create a dashboard prompt that will not be saved in the selection.

'<a href=saw.dll?Dashboard&Path=' || replace('@{dashboard.currentPage.path}', ' ','%20') || '&Action=Navigate&P0=1&P1=eq&
P2=%22Datum%22.%22Week%20Code%22&P3=' || Datum."Week Code" || '>' ||  Datum."Week Code" || '</a>'

Conditional Data Navigation and filtering

With the help of the pre-build presentation service variable dashboard.location and with the name of the page in a column, you can make a navigation which is dependent of your data.

For example, the following url sends you on a different dashboard page by KPI. The name of the page is stored in the column Kpi.“Kpi Page Name”.

'<a href=saw.dll?@{dashboard.location}&Action=Navigate&P0=1&P1=eq&P2=Kpi.%22Kpi%20Code%22&P3=' || Kpi."Kpi Code" ||
'&Page=' || Kpi."Kpi Page Name" || '>' || Kpi."Kpi Name" || '</a>'

From the documentation

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

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

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.

PortalPageNav function

The javascript function PortalPageNav is a filter function and belong to the Dashboard Url functionality.

Syntax:

function parent.PortalPageNav(event, sDashPath, sDashPage, sTbl, sCol, sVal, sTarget) where:
  * event = event indicator.
  * sDashPath = the catalog dashboard path 
  * sDashPage = the page of the dashboard
  * 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.

With the data format :

where the sign @ reference the current column value

In a column formula:

'<font class=Nav onclick="Javascript:PortalPageNav(event, ''@{dashboard.path}'', 
''@{dashboard.currentPage.name}'', ''MyTable'', ''MyColumn'', ''' || MyTable."MyColumn" || ''');">' 
|| MyTable."MyColumn Name" || '</font>'

Support

An URL must be encoded before that you can use it.

All spaces must be replace with the %20 sign
When you cast a number as char, you may have to trim it to suppress the blank character
When you pass a string variable, you may have to replace the blank character by %20

CASE WHEN Fact."My Measure" < 500 then  
   '<a href=saw.dll?Go&Path=/shared/My%20Dashboard/Analyses/My%20Analyses&Action=Navigate&P0=5' 
   || '&P1=eq&P2=DimensieTable.MeasuresNumber&P3=' || trim(cast(MeasuresNumber as char)) 
   || '&P4=eq&P5=DimensieTable.MeasureCharacter&P6=' || replace(DimensieTable.MeasureCharacter, ' ', '%20')
   '>Drill</a>' 
ELSE 
   Null 
END

Documentation / Reference

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