Thursday, July 22, 2010

OBIEE using prompt as report selector

Yes, I know Venkat did it before:
http://oraclebizint.wordpress.com/2008/01/17/oracle-bi-101332-selecting-reports-from-dashboard-prompts-and-guided-navigation-sections/
So I will do it again so you can all reference mine ;-)
Create a prompt with a dropdown list of your reports:
image
image
If you can use an LOV instead off the CASE 1=0 construction!
put it all in a presentation variable
Add some dummy reports which only rows if the right pReports are selected:
image
image
Add the prompt and your real reports to your dashboard, give each report a separate section:
image
Set the Guided Navigation:
image
image
And Now you have a report selector:
image
image
Till Next Time

7 comments:

Ash said...

As the SQL is processed for all reports at the back end it leads to performance issues.
Can we expect something better in OBIEE 11g?

Ash

David said...

Hello John

I have several reports on the same page.
I need to use a prompt for the User to change the period of the semester report. however, it is necessary for the prompt change only the selected reporting period and not all reports of the page.

Could you help me with this?

Thanks

John Minkjan said...

@David,

How did you set the scope of the prompt? Have you considered using presentationvariables?

regards

John

vinod said...

Hello Join

This is regarding
OBIEE using prompt as report selector.

Selecting Reports from Dashboard Prompts and Guided Navigation Sections

I have tried doing the same as per the steps given by you for Selecting Reports from Dashboard Prompts and Guided Navigation Sections.

First Step

CASE WHEN 1=0 THEN "Sales Geo"."Sub Region" ELSE 'Booking By Region' END in column area


Second Step

SELECT CASE WHEN 0=1 THEN "Sales Geo"."Sub Region" ELSE ‘Booking By Country’ END FROM Revenue UNION ALL SELECT CASE WHEN 0=1 THEN "Sales Geo"."Sub Region" ELSE 'Booking By Region' END FROM Revenue


But as I just wanted to see the dasboard with these ‘Booking By Country' and 'Booking By Region' values, I am getting error as

"Error Generating Drop Down Values
Odbc driver returned an error (SQLExecDirectW).
Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27002] Near : Syntax error [nQSError: 26012] . (HY000)
SQL Issued: SELECT CASE WHEN 0=1 THEN "Sales Geo"."Sub Region" ELSE ‘Booking By COuntry’ END FROM Revenue UNION ALL SELECT CASE WHEN 0=1 THEN "Sales Geo"."Sub Region" ELSE 'Booking By Region' END FROM Revenue"

can you please help me out to get this resolve

Regards
Vinod

vinod said...

Hello Join

This is regarding
OBIEE using prompt as report selector.

Selecting Reports from Dashboard Prompts and Guided Navigation Sections

I have tried doing the same as per the steps given by you for Selecting Reports from Dashboard Prompts and Guided Navigation Sections.

First Step

CASE WHEN 1=0 THEN "Sales Geo"."Sub Region" ELSE 'Booking By Region' END in column area


Second Step

SELECT CASE WHEN 0=1 THEN "Sales Geo"."Sub Region" ELSE ‘Booking By Country’ END FROM Revenue UNION ALL SELECT CASE WHEN 0=1 THEN "Sales Geo"."Sub Region" ELSE 'Booking By Region' END FROM Revenue


But as I just wanted to see the dasboard with these ‘Booking By Country' and 'Booking By Region' values, I am getting error as

"Error Generating Drop Down Values
Odbc driver returned an error (SQLExecDirectW).
Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27002] Near : Syntax error [nQSError: 26012] . (HY000)
SQL Issued: SELECT CASE WHEN 0=1 THEN "Sales Geo"."Sub Region" ELSE ‘Booking By COuntry’ END FROM Revenue UNION ALL SELECT CASE WHEN 0=1 THEN "Sales Geo"."Sub Region" ELSE 'Booking By Region' END FROM Revenue"

can you please help me out to get this resolve

Regards
Vinod

Prakash Saini said...

Hi,I've used his approch in one of my dashboard with 3 reports ,but the catch here is when i select the Report1 from Prompt it shows the Report 1 with the data & shows Report 2 & 3 with blank informations.

Is there any way to display only one report at a time??

Thanks
Prakash

Kavi said...

Hi John

This was a very useful post and thank you for this wonderful blog. I implemented the above mentioned technique to create a dashboard prompt with list of report names. However, I have used 3 reports in the list and soon as I go to the dashboard page with this prompt, SQL for all the reports on this page gets generated on the backend. Even when I select one of the reports from the drop down list, I see many other SQL still running on the backend. Since our report list is long, this puts a lot of load on the database. Is there a way to stop the queries from being generated and wait for the user’s response? I would appreciate your reply.