OBIEE navigation between different dashboards, passing multiple parameters

In this post I'll show how to navigate from the source dashboard page on the one dashboard to the target dashboard page on the second dashboard with passing multiple parameters.

I want to navigate from the Test Page 2 on Development 2 dashboard:



To the Test Page 2 on Development 3 dashboard and pass parameters to target dahboard prompts:



The source report columns and filter:

PRODUCTS.PROD_CATEGORY
CHANNELS.CHANNELS_CLASS
SALES.QUANTITY_SOLD
PRODUCTS.PROD_CATEGORY is prompted

The target report columns and filter:

CHANN.CH_CLASS
PROD.PR_NAME
SALES.Q_SOLD
PROD.PR_CAT is prompted
CHANN.CH_CLASS is prompted

Note that I'm using different subject areas with different BMM logical table and column names for the source as well as for the target report.

The one more important thing is the target dashboard prompt in which we'll pass parameters:



Let's go back to the source report.

I realy tried all options to call dashboard page on the different dashboard but with no luck, the only solution that works fine I'll explain later, but now let's see what have I try in the source report.

Solutions that don't work well

PortalPageNav javascript function to call target dashboard with target dashboard page in the column PRODUCTS.PROD_CATEGORY custom text format (data format tab on the column properties):



But with this we can use only current column value (PRODUCTS.PROD_CATEGORY, referencing it with @) and we want to pass CHANNELS.CHANNEL_CLASS, so this solution is not good.

I tried with the second filter in PortalPageNav but we can pass only static values further, only one filter is dynamic (with @):

PortalPageNav(event, '/shared/Development/_portal/Development 3', 'Test Page 2','PROD','PR_CAT',"'@'", 'CHANN','CH_CLASS','Direct')

The next thing that I tried is writing the similar code with thread text as HTML option, but still no progress, we can't put PRODUCTS.PROD_CATEGORY and CHANNELS.CHANNEL_CLASS columns in the PortalPageNav instead of static values.

I tried to write Answers column in HTML form fields and call PortalPageNav to read it with document.getElementsByName or document.myform.field.value, instead of static values, but still no luck.

And the last one attempt is using DASHBOARD URL to navigate to dashboard page on the different dashboard:



No luck again, with DASHBOARD URL we can navigate between dashboard pages that are located on the same dashboard, like in the code below:



Working solution

The only solution that works fine with passing multiple filters (dynamic) and call another dashboard page on the another dashboard is using HTML code with PortalPageNav javascript function in the narrative view of the source report.

With this we can easily reference PRODUCTS.PROD_CATEGORY and CHANNELS.CHANNEL_CLASS columns with @1 and @2 (like in my example) and passing parameters to the target dashboard prompt fields on the target dashboard page, target dashboard.

HTML code with PortalPageNav function in the source report narrative view:



<--a href class="Nav" onclick="JavaScript:PortalPageNav(event, '/shared/Development/_portal/Development 3', 'Test Page 2','PROD','PR_CAT', '@1', 'CHANN', 'CH_CLASS', '@2');">@1 and @2
<--/a>

We display all rows in the narrative.

Test

The source dashboard page on the first dashboard:



Ok, at first sight is not pretty, but the functionality is important here. We'll navigate with Electronics as category and Direct as channel class.

The result is new opened window with target dashboard page on the target dashboard. We successfully pass two filters to the target dashboard prompt and we see the target report:



NQQuery.log for the target report:



All parameters are transferred to the target.

If we want to navigate to the second dashboard and open it in the same window we use target="_self":

<--a href class="Nav" onclick="JavaScript:PortalPageNav(event, '/shared/Development/_portal/Development 3', 'Test Page 2','PROD','PR_CAT', '@1', 'CHANN', 'CH_CLASS', '@2');" target="_self">@1 and @
<--/a>

Dashboard URL navigation from source to target dashboard page and unicode replacement

This post is translated from Croatian, from one of my previous posts. For navigation between reports we use GO URL, but for navigation between dashboard pages we use DASHBOARD URL. Basic documentation about this can be found in the presentation services administration guide http://download.oracle.com/docs/cd/E10415_01/doc/bi.1013/b31766.pdf on the page 210 (referencing dashboard content in external portals or applications using
the oracle BI presentation services DASHBOARD URL. I'll not explain basic syntax how to use DASHBOARD URL. This can be found in documentation or other blogs.

Example (with unicode replacement):

I will explain dashboard url navigation between two pages on the same dashboard. Dashboard pages, columns, etc, are based on Croatian terminology, so I will try to translate it, although it's not so important, the technique is.

So, there are two dashboard pages on Development dashboard, revenues, which give us some measure for products from the fact table and the second is šifarnik proizvoda which give us some detailed information about products, in this case only product group is shown in the detail part.



I'll explain reports on each dashboard pages later.

The basic idea on the first page is that the user enters some value on the dashboard prompt field and we need to give a possibility to enter the code or the product name. Also, the user want to use LIKE condition it the report.

This is the dashboard prompt in the first page (the name of the field is 5):



We set here presentation variable v_ulaz_var. This is very important to set. We'll use it later in te second report in the second dashboard page to remember what user enters in this prompt when we return back from the second report on the second dashboard page to this one (first report on the first dashboard page). We want to return value that user enters, not reseting it.

Abbreviations:

The first report on the first dahboard page -> FRFDP
The second report on the second dashboard page -> SRSDP

The first report on the first dashboard page (FRFDP):



The first two columns are product name and the code, the third one is the measure and the last one is important for DASHBOARD URL navigation.

Note that we enter manually filter for LIKE condition in case that the user enters product name or the product code.

Now, here is our next step. We want to go to the another report (SRSDP) and parse the product code from the current row column from FRFDP, and the result will be detailed information for that product (product code).

But we need one more thing here. We want to parse the value that we set in the v_ulaz_var, which in presentation variable that we set in the first page. And we want to show it in the second report (SRSDP). So for this case we will do some tricks.

In the second dashboard page we make a dummy dashboard prompt first:



Inside it we can parse what ever we want to from the first dashboard page (current row column report values, constants, presentation variables from dashboard prompts from the first page, etc). In every field we set new presentation variables that we can use in the SRSDP. So, this is a trick that works here. And in any case we can return any value to dashboard prompts on the first page.

Note that when using DASHBOARD URL you are parsing value to a dashboard prompt fields and the GO button is automatically executed and you get the report. So beware what you are putting on your dashboard page.

Here are fields and presentation variables on dummy dashboard prompt:

Field 1, presentation variable v_var1 -> we'll parse current row product code (šifra proizvoda) value from the FRFDP inside it.

Field 2, presentation variable v_var2 -> we'll parse harcoded string 'Dva' inside it.

Field 3, presentation variable v_var3 -> we'll parse harcoded string 'Tri' inside it.

Field 4, presentation variable v_var4 -> we'll parse v_ulaz_var presentation variable from the dashboard prompt from the first dashboard page inside it.

The second report on the second dashboard page (SRSDP):



The first two columns are product name and the code and the third is product group.

In the column four (Varijable) we concatenate all presentation variable from the dummy dashboard prompt, to see if our navigation works fine and if all parameters are successfully transferred:



The last column (Povratak) we'll explain later, because it's a part of DASHBOARD URL for returning back to the previous dashboard page and return v_var4 into v_ulaz_var.

In this report we have a filter that is important, because we are parsing product code (Šifra proizvoda) from FRFDP to SRSDP. We are using v_var1 presentation variable in which we parse product code before:



Now, let's go to FRFDP. There is a column Go dashboard navigation in which we'll place DASHBOARD URL code to call a second dashboard page and parse parameters to a dummy dashboard prompt and according to that implicitly call the second report on the second dashboard page (SRSDP).

Column GO dashboard navigation on FRFDP:



Basic explanation:

PortalPath=/shared/Development/_portal/Development/&Page=%u0160ifarnik%20proizvoda

%u0160 and %20 is required because of unicode translation (Šifarnik proizvoda).

Col1=1 -> target field on the dummy dashboard prompt on the second page is 1.
Val1=... -> source field from FRFDP.

In the field 1 in the dummy dashboard prompt we are parsing product code (Šifra proizvoda).

If the product code (šifra proizvoda) has spaces inside the data (for example, PR 01), we need to replace space ' ' first with a plus sign '+' and then the plus sign '+' replace with ASCII 2B.

Now, let's go to again to SRSDP. There is a column Povratak in which we'll place DASHBOARD URL code to return back to the previous page without reseting dashboard prompt from it, so basicly we are returning parameter from the presentation variable v_var into field 5 in the first page dashboard prompt. In case that we don't do that, when returning back with DASHBOARD URL to first page we would get all rows in the FRFDP, and we don't want that, we want to apply what we have in the prompt in the previous state.

Column Povratak on SRSDP:



Col1=5 -> target field on the first page dashboard prompt is 5.
Val1=v_var4 -> the source parameter is the presentation variable v_var4 that we set in the second page dummy prompt.

Finally, this is complete view, with both dashboard pages and sections inside them:





Note that in the second page we put dummy dashboard prompt on the separate section.

Test

At initial, the user place the value in the first page prompt and according to that we receive the result on the first page report. We don't know whether the user enters all product code or product name or just few strings (LIKE condition), both conditions are supported in the report filter. We press GO button and get the data:



We see that the product name LIKE condition (Naziv proizvoda LIKE 'Trk%') is satisfied and we receive one row in the result of the first report.

Also in the current row of the report we know the product code (Šifra proizvoda) which is hidden column (the value is PR-01, we don't see it).

With the column GO dashboard navigation we are going to second page and forwarding already mentioned parameters.

The result is the second page with the second report:



Note that all parameters are forwarded correctly and we get the desired data in the report.

We can hide dummy dashboard prompt with guided navigation applied on the section:



For guided navigation we use dummy report that always shows the data, so the section is always hidden in that case:



Now, we don't see dummy prompt:



After we press column Povratak (string Povratak na prvi page) we are going to the fist page without reseting filter to the first report, this is previous state:



Conclusion

This is the way and example that we can parse presentation variable from the first report to the second. Each report has its own page and its own dashboard prompt. We can hide the second page and when we call it from the DASHBOARD URL on the first page report it's look like we are calling a new report, the only difference brtween GO URL (which is only for reports) and this one is that we can parse real presentation variable to the target dashboard prompt on the target page and use it in the target report which is paced in the target page.

Dynamic dashboard prompts and columns used in multifunctional report, full guided navigation

Example:

Three dashboard prompts. One is showing only days or months selection and after pressing GO button we are showing second section with day values in dashboard prompt or third section with month values in dashboard prompt. The second and third sections are guided navigated and we only show one section depends on what user selects from the first dashboard prompt (first section), days or months. After we select value from the second dashboard prompt we get our report. The report is only one, with dynamic column in the group by part, depends on what user selects. For this example I used guided navigation applied on sections. I'll explain it in detail.

The first dashboard prompt applied on the first section:



Show/SQL Results:

SELECT case when 1=2 then TIMES.CALENDAR_MONTH_DESC
else 'Day'
end FROM "Normal model"
union all
SELECT case when 1=2 then TIMES.CALENDAR_MONTH_DESC
else 'Month'
end FROM "Normal model"

First section:



Dashboard prompt applied on the second section:



Column formula for date from:

case when 1=2 then TIMES.TIME_ID else cast ('1.1.1900' as date) end

Column formula for date to:

case when 1=2 then TIMES.TIME_ID else cast ('1.1.1901' as date) end

Second section:



Guided navigation report applied on the second section:



Column formula for pv_dyn_group_by column (we can delete it after we make filter):

'@{pv_dyn_group_by}'

The report will show data if the presentation variable from the first section (first dashboard prompt) pv_dyn_group_by is filled with Day harcoded value. So the second section is whowed only in this condition. TIMES.CALENDAR_YEAR is dummy column.

Dashboard prompt applied on the third section:



Third section:



Guided navigation report applied on the third section:



Column formula for pv_dyn_group_by column (we can delete it after we make filter):

'@{pv_dyn_group_by}'

The report will show data if the presentation variable from the first section (first dashboard prompt) pv_dyn_group_by is filled with Month harcoded value. So the second section is whowed only in this condition. TIMES.CALENDAR_YEAR is dummy column.

Target report and the section four:



Column formula for dynamic group by column:



Must be same datatypes inside it.

Filter (advanced, convert this filter to SQL option):



Section four:



Guided navigation report applied on the section four:



We need this guided navigation report that we applied on the section four because at initial we don't want to show target report, we show it only when one of the value of the presentation variable pv_dyn_group_by is selected, and that is Day or Month value, from the first dashboard prompt from the first section.

Filter (advanced, convert this filter to SQL option):



We applied guided navigation reports in the properties of the section, we do the same for the second and the third section (explained before):



Finally, this is complete view, with all sections:



Now, we are going to test this solution.

Test

Initial:



Select Day and press the first GO button:



Dashboard prompt with dates is shown:



Choose date from and to values and press the second GO button:



Target report for this case:



Now, change the value to Month and press the first GO button again:



Dashboard prompt with months is shown:



Choose month from and to values and press the second GO button:



Target report for this case: