How to pass values to different dashboard prompts interactively

There is a scenario where I have to navigate from one dashboard to another while keeping the values selected as the criteria in the next dashboard. OBIEE has a pretty standard functionality that allows you to navigate. In 10G version, you can define the navigation path in the column property while in 11G, you add action links to it.

All these are great and simple, but there is a problem when the prompts on these dashboards are different. In OBIEE, if the prompts have different expressions either because they are made from different columns or they are using different logical expressions, they are being treated differently, so while the navigation will still happen, but the value from 1 prompt will not be passed as a filtered value to another prompt. What if I have 2 date columns that are aliases of the same date table, and I want to be able to navigate from one to another using prompts from both columns? This sometimes become a practical challenge. In the eyes of users, they won't understand the difference between the 2 prompts so they only expect to be able to navigate from 1 to another. So this requires some workaround.

Now lets look at this scenario to understand better the issues. I have 2 dashboards that each contains date and hour prompts at least:

Now if you select a date and hour period from dashboard 1 and navigate to dashboard 2, the date and hour values should be expected to come along as well. Well, this should be typically the expectation if the date and hour prompts are exactly the same column with the same expression across both dashboards. However, in my case it is not. Therefore while the navigation still works, but when you get to dashboard 2, the prompted date and hour values are still blank, the report will run with either default prompt values or run without any filtered values.

So let's look at how these prompts are different in my scenario:

The prompt in dashboard 1 is done as the following:

Here, the date prompt comes from date column as it is, the content of the hourly prompts are using the following expression:
Starthour prompt: case when 1 = 1 then hour number end

Endhour prompt: Case when hour number = 0 then 24 else hour number end.

The reason for this type of implementation can be found here, it was meant to solve a different problem.

Now the set up for prompts on dashboard 2 are found below:

Now the reason was this implementation was discussed  if you are interested to know.

Basically, these 2 dashboards content very different functionality, therefore it required different programming behind the scenes. The date prompt on 1 dashboard  is using between operator while is equal operator on another dashboard. The hour prompts on dashboard 2 have to be evaluated based on presentation variables from another prompt (Time scale granularity ), which is not required for the same prompts on dashboard 1. Long story short, I can't pass values from dashboard 1 to dashboard 2 because these are being considered different fields by OBIEE even if users think they are the same.

Therefore, in order to make it work, you can't just convince the users that this can't be done nor will you hope they will  understand the technicality of OBIEE. This will only create bad impression about the product. Hence, we have to look into ways to get around with this requirement. The idea of how to fool OBIEE into treating these prompts the same without compromising the individual functionality of each dashboard.

discussed the requirement and passing values from 1 dashboard to another dashboard as we navigate, and also the issues and challenges I was facing in this particular. In this post, we will continue exploring the solutions.

So like I said before, I need to find a way to fool OBIEE into thinking these prompts are the same first of all. The first difference I see is that the date prompt in one dashboard is using a between operator while the other prompt is split into 2 date prompts having equal operator for each. So I decide to split the first prompt into 2 and define the same expression in the prompt to make it the same as the other prompt.

Of course, I have to make sure that the functionality of the original prompt is kept the same. I am doing it by assigning presentation variables to both new prompts.

Now, moving onto the prompts on dashboard 1 and see what we can do about that. Since there is only 1 date prompt on dashboard 1, I will not make an effort to further configure it. That will, only 1 date prompt value from dashboard 2 will be passed to dashboard 1.

The main effort here is to configure the hour prompts on dashboard 1 so that it will be treated the same as dashboard 2.

Being aware of the fact that the hour prompts in dashboard 2 needs presentation variable (time scale granularity) to determine its content, we will have to make it the same in dashboard 1. But we have to do it in such way that these prompts still present the same values even if there is no presentation variables being chosen because it doesn't need it at all.

For the start hour prompt, I have entered the following expression:

Starthour: case when '@{varDateSelect}' = 'Hour' then Date."Hour Number" when '@{varDateSelect}' = 'Day' then null else Date."Hour Number" end

So in this case, when vardateselect is unspecified, the content will still be Date."Hour Number defined in the 'else' clause.

For end hour prompt, I have entered the following expression:

end hour: case when '@{varDateSelect}' = 'Hour' then Date."Hour Number" when '@{varDateSelect}' = 'Day' then null when 1=1 then Date."Hour Number"  end

This is the same logic as start hour. The only difference is that I have 'when 1 =1' as a condition. This is to fool OBIEE into thinking that these 2 prompts are different so that they each will allow different prompt values at the same time without being synchronized by OBIEE. Yet, both expression will yield the same result.

The below 2 expressions will also be defined in the hour prompts on dashboard 2. That way, OBIEE will look at them as the same, mean while the functionality of the prompts on dashboard 2 will remain the same because the prompt values will always be according to the vardateselect variable, which means the 'else' condition will never be used.

Now, let's create some reports to deal with passing the LSP values:

This report will return list of LSPs and the column header and value will be holding the navigation path to dashboard 1. More importantly, the default filter value for this report is 'Not Found', which means initially the report will always return nothing until a new prompt value being passed.

Now lets put it all together on dashboard 2:

Here as you can see, I have defined the guided navigation on the section that holds this LSP report. That way initially, this report is invisible from the dashboard like illustrated from below:

Now let's test the feature by selecting the date and hour range, LSP names on dashboard 2 and click 'go'. You should see that the LSP report being rendered and the results will show shortly:

The below shows the reports based on the selected values from the prompts on dashboard 2. You can see the LSP report having both header and data highlighted. So the idea is that by clicking on the header, all of the LSPs coming from the multi-select prompt will be passed along to dashboard 1. If clicking on a specific LSP, then only that LSP will go through along with the dates and hours.

So let's check it out by clicking and we get to dashboard 1:

As you can see, all of the dashboard prompts on dashboard 1 are filled with values coming from dashboard 2, because OBIEE thinks they are all the same so accepted the interaction.

Of course, another way of defining the navigation is by embedding a navigation link on the dashboard like so:

and the dashboard display will be like this: