Posted by: daveb78 | July 18, 2008

Getting OBIEE Dashboard Prompt Values Into BI Publisher

Update:  The scenario described below involved an attempt to pass parameters from a Dashboard to a BI Pub report using OBIEE running in a stand-alone OC4J.  Attempting to accomplish the same goal using OBIEE running in OAS was much easier.  It worked as the documentation describes and required no work-around.

I recently developed a proof-of-concept implementation of OBIEE for a client. As part of this I wanted to have a dashboard page with prompted values that would be passed to BI Publisher and then used as parameter values in a report that I had previously built. This seems like a pretty standard requirement, but it was actually a bit tricky to get it working.

The BI Pub report uses a single SQL query against a materialized view as a data source. The materialized view’s sole purpose is to encapsulate the very complicated report logic, thereby allowing my BI Pub report and template to be simple and easy to understand. The materialized view is not included in the OBIEE metadata. If it was in the metadata it may have been easier to create an answers query against it and then use that as my BI Pub report data source.

The first thing I tried was to create the prompt and, for each of its elements, declare presentation variables with the same name as the relevant parameters defined in my BI Pub report. I then embedded the BI Pub report into the dashboard page. Unfortunately the report did not recognize the presentation variables as report parameters. The progress icon appeared, but the report just hung.

The Solution:

I created an Answers query as a Narrative View with all the query filters were set to “As Prompted”. I then used the Narrative View interface to build up a URL that would execute the BI Pub request, just dropping in variables as needed. I then wrapped the URL in HTML tags so that it would render as a link on the dashboard page. Next I added the query to the dashboard page that had the prompt I created earlier. The dashboard page now displays the prompt and a link. Clicking the link generates a BI Pub request which in turn pops up my report as a PDF.

I will post screen shots soon…

…Screenshots, finally:

Advertisements

Responses

  1. I am trying to do something similar except I want to pass prompts to a request that is a Link from the same dashboard as the prompt. I’ve set a presentation variable in the dashboard prompt and then have a narrative view request run on this dashboard that refers to the presentation variable in its filter. I then modified the request (that is run as a link from the dashboard) to have its filter refer to the results from the “hidden” narrative request but it doesn’t work. I test my dashboard by choosing a value from the prompt and clicking Go. Then I click on the link that will run my request. The request runs but returns nothing, only indicating no rows found. Any ideas?

    • John, if i understand your problem correctly, it seems that the technique described in my post would work for you. I just added some screen shots that may be helpful.

      Dave

  2. I was wondering if you can provide more info like printscreens, I need to do something similar at my current project

    • Serge, I have added a couple of screenshots to the original post illustrate the technique. Hope this helps.

      Dave


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: