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