I have been working on a client assignment to implement OBIEE and design and develop dashboards and get them up and running.
One particular requirement was to bring a BI Publisher report into a dashboard. The BI Publisher Report was developed much before the OBIEE came into limelight. The BI Publisher Report is scheduled to run everyday and email the report to certain group of users. Since we have been playing around to set the BI Dashboard and have one dashboard up and running for a specific business area, and so decided to extend this for other areas as well.
For one such area where we have the BI Publisher Report has been developed, rather than re-developing the report we have decided to bring the BI Publisher report into the dashboard.
The BI Publisher report is
-SQL Based Report
-Has 2 date parameters.
This exercise is broke down into 2 pieces, one create report in BIPublisher and second placing the BIP Report into Dashboard and creating Dashboard prompts to pass values into BIP report parameters.
BI Publisher
After creating the datasources and the required SQL to pull the data from underlying database objects,
- Declare the date parameters that will be used in the SQL to constrain the data, as shown below -
- The above declared parameters are used in the SQL that pulls data from underlying table, as shown below -
- Save the changes and run the report by clickion on "view".
- The results are as shown below in the report -
- That looks great!!! the report shows the data as per the specified parameters.
The next step is to place the above BI Publisher report into a dashboard and define dashboard prompts to pass values into the BI Publisher report.
The first step is to define the Dashboard prompts which allow the end-users to send the values to the BIP Report parameters. One important thing to rember while creating the Dashboard prompts is that, the Presentation variable MUST BE same as the parameters defined in the BI Publisher. The creation of Dashboard prompts is as shown below.
Once the dashboard prompts are defined, the next step is to add the BIP report into the dashboard.
First add a section to the Dashboard page, and then drag and drop the dashboard prompts created earlier, which can be seen on the left pane.
And then, add another section and add the BI Publisher Report using the "BI Publisher Report" Dashboard Objects as shown below.
Save your changes by clicking on "Save". Navigate to the dashboard page, and you should see the BI Publisher Report added to it.
There you go!! Your BI Publisher Report has been added to the Dashboard, and you can pass values to the report.
Hi, thanks for your post, it is super helpful. What if the report is based off of a Web Service? I am not able to get this to work in this case. Thank you!
ReplyDeleteUnfortunately I did not try this will update you when I test this.
ReplyDeleteRespectable!
ReplyDeleteI am new to OBIEE. It becomes a challenge for me to pass variables from dashboard prompts and to catch their value in Answer's Request.
I would like to illustrate it with an example.
Request based on the direct databse request..
Select NAME, AGE, CITY, ADDRESS
From emp
Where DOB = ‘@{p_date}’
Now DOB is in TIMESTAMP format,
it looks like ‘11/1/2009 12:00:00 AM’.
Dash board Prompt also depends upon the same column and have also same TIMESTAMP format.
In next step
I create a dashboard page
Place prompt and report there
Saved the dashboard
Now I select a date from prompt list and press go button
It shows no result.
I tried several combinations in @1 syntax e.g.
Where DOB = ‘@{p_date}’
Where DOB = ‘@{p_date}{1-nov-2009} ’
Where DOB = cast (@{p_date} as date)
Where DOB = cast(to_char(@{p_date}, ‘dd-mmm-yyyy’) as date)
.........................
And many other combinations
I am looking for any help/tips in this regard
Thanking in anticipation
usman.fawad@hotmail.com
(kindly provide me solution at my email ID)
Hi ya,
ReplyDeleteYou can use evaluate function for this as in -
where DOB = EVALUATE('TO_DATE(%1,%2)','@dashboard variable','date format of the dashboard variable''). In our case we used, calendar to pick the dates and the date format is - dd/mm/yyyy
Regards
Mahesh
The problem with this is that it has that ugly control panel between the prompt and the report and there seems to be no way to get rid of it.
ReplyDeleteGreat article.
ReplyDeleteThanks
Nice Attempt...........
ReplyDeleteYou know if there are some restrictions on number of character from dashboard variables on publisher variables?
ReplyDeleteI have two presentation variables that have to move up to 8000 character from dashboard to publisher, but if the total characters go to 3750, this values are not imported in publisher, that don't respond nothing, no publisher sessions are started.
Nice article. When I go to the OBIEE Dashboard to add the BI Publisher report into the dashboard, the BI Publisher Reports is not an option under the Dashboard Objects on the left side of window. Do you know how I can add the BI Publisher Reports option in 10g or 11g?
ReplyDeleteFound the issue. Needed to change Privileges to allow access to BI Publisher reports within Dashboard.
DeleteYour title is exactly what I need to do, However I do not follow directions. I want to retrieve user id from OBIEE dashboard and place as a parameter on bi publisher report, how can I do this?
ReplyDelete