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

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Presentation Service (SAW/OBIPS)

1 - 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.

Advertising

3 - 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”

Advertising

4.1 - 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
Advertising

5 - Examples

With the Navigate to a Web Page action and an Alias

Where:

5.2 - 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>'

5.3 - 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 [email protected]{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>'

5.4 - 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.

6 - 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>'

7 - 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

8 - Documentation / Reference