Logical Sql in a GO Url

There are a lot of good posts on how to us the GO Url functionlity in Oracle BI EE. One thing which isn’t covered very often is the use of ‘OR’ in a prompted-GO Url.

If you want to filter sales <= 5000 or sales >= 15000 it seems not possible via the ‘standard’ GO Url structure. An alternative is using Logical Sql in the GO Url;

saw.dll?Go&SQL=select+Region,Dollars+from+SupplierSales

In this case ‘SupplierSales’ is the Subject Area.

It is possible to add a where-clause to the select statement. I created the following example:

The first link contains a Go URL with Logical Sql.

saw.dll?Go&SQL=SELECT+"Dim Time"."Calendar Month", "Fact Sales Amounts"."Sales Amount"+FROM+Sales

The result of this link;

The second link has a Go URL also, but this one includes Logical Sql with a where-clause.

saw.dll?Go&SQL=SELECT+"Dim Time"."Calendar Month", "Fact Sales Amounts"."Sales Amount"+FROM+Sales+WHERE+"Fact Sales Amounts"."Sales Amount"+<=+5000+OR+"Fact Sales Amounts"."Sales Amount"+>=+15000

The result of this second link;

Unfortunately these forms of the Go URL return tabular results only.

Extend Dashboard funtionality by using Presentation Variables

It’s possible to extend the functionality of your Oracle BI Dashboard by using Presentation Variables. Nothing new here. For some more details about variables in Oracle BI EE and how to use the please have a look here.

A colleague of mine pointed me to two examples of how to use these variables in your Oracle BI Dashboard. Maybe nothing new as well, but nice to mention anyway.

Add a Presentation Variable to a section heading

If you want, you can add an heading to your section. This is pretty straight forward.

Default, when you add a section to your dashboard, the section is named; ‘Section 1′.

 

 If you want to give this section a maeningful name and add it to your dashboard page, you can rename and display the section.

The dashboard page will show the text acccordingly.

If you like you can make this heading dynamic by using a Presentation Variable. First you will have to make a dashboard prompt to set the Presentation Variable.

 

Secondly you can reference the Presentation Variable. In this case, add; @{PV_SUPPLIER} to the section heading.

Now the dashboard page will show the heading, including the Presentation Variable.

When you change the selection in the dashboard prompt the text will also change.

Using an iFrame to show different views of the same Answer based on Guided Navigation and a Presentation Variable

Picture the following simple requirement:

I have a set of Suppliers. Each Supplier has a Supplier Type.

On my dashboard, there is a prompt on Supplier Type.

When I select ‘Oracle’; show a Pivot table. If ‘Non-Oracle’ is chosen, then there should be a ‘Line Chart’ for ‘Scamander Solutions’. ‘Oracle BI By Bakboord’ will show a ‘Bar Graph’.

You could achieve this by making multiple Answers, Guided Navigation and some hard-coding. Another way of achieving this requirement, is using Guided Navigation, iFrames and a Presentation Variable in combination with an GO Url. iFrames require the use of Presentation Variables, because they cannot capture the values you select in a prompt directly.

Edit a dashboard page and add two sections with Guided Navigation to switch between the views for the Supplier Type. In the section for Supplier Type = ‘Non-Oracle’ add two Text Objects for the iFrames.

Add the folowing code to the Text Object for the iFrame:

<iframe src=
http://localhost:9704/analytics/saw.dll?Go&Path=/shared/OBIBB/Presentation%20Variable%20iFrame&Action=Navigate&P0=2&P1=eq&P2="Dim%20Supplier"."Supplier%20Type"&P3=@{PV_SUPPLIER_TYPE}&P4=eq&P5="Dim%20Supplier"."Supplier%20Name"&P6=Oracle%20BI%20By%20Bakboord&Options=rmf&ViewName=staticchart!1 width=100% height=375 frameborder=none>
</iframe>

 Now I am able to switch between different views, without having to create different Answers

 

Follow

Get every new post delivered to your Inbox.

Join 671 other followers

%d bloggers like this: