Skip to Main Content
Forums

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

OBI Dashboard &Dashboard prompt to trigger BI Publisher Report in Dashboard

metalrayJun 6 2013 — edited Jun 25 2013
Hello,
I have some trouble with my dashboard prompt to filter BI Publisher Reports (on the same dashboard as the prompts).
The creation of a logical column with a sort value = ID (int) and a display value of = text (varchar) worked
fine and I can select the logical column and test it but when I put it on my dashboard and click on "apply"
nothing happens. The BI Publisher reports do not "load" with the values from the prompt.
The 2 prompts are set up to write to a presentation variable that is named exaclty like the parameters
in the BI Publisher Report data model.
Any ideas why this is not working? Do I have to tell the BI Publisher Reports that the input value they
wait for comes from a dashboard prompt or so?

PS: When I open the report only (not the dashboard) put in my two parameters and press execute it works fine.
This post has been answered by metalray on Jun 25 2013
Jump to Answer

Comments

metalray
I learned a bit more and figured out that the presentation variables get set wrong "<center><font Size=’4′><font color=”#000000″>@{P_OFFICE}</font></font></center>".
P_OFFICE gets populated with the display value of the repositories logical column i..e the dashboard prompts source, rather than the sort value (the ID).
Why is that? I extra created logical columns to have a behind the scenes ID value for every display value.
SriniVEERAVALLI
I assume your PS variable name something like P_OFFICE, and in the BI Pub report use as :P_OFFICE in your data source query.
Do not refer the PS variable as @{P_OFFICE} in BI Pub report. I doubt on the datatypes, values from PS variable.
1 person found this helpful
metalray
Hi. That helped. I get the right value now in the dropdownlist. When defining the dashboard prompt I wrote some custom SQL:

SELECT "A11_DIM_PERIOD"."DESCTEXT", "A11_DIM_PERIOD"."KEY" FROM "promptsource" WHERE "A11_DIM_PERIOD"."DESCTEXT" IS NOT NULL ORDER BY "A11_DIM_PERIOD"."KEY". Now the prompt shows the display values but as soon as I select on and hit apply the dropdownlist text changes to the Key values (whicih is fine for my parameters, because they need to be key values rather than text) but this is unfortunate for the front end user, they will wonder "hey, why did the drop down list value I selected just change". Right now, I dont know why. The prompts work fine just odd that their display data changes from rpd logical column description field to sort field.
SriniVEERAVALLI
When defining the dashboard prompt I wrote some custom SQL:
SELECT "A11_DIM_PERIOD"."DESCTEXT", "A11_DIM_PERIOD"."KEY" FROM "promptsource" WHERE "A11_DIM_PERIOD"."DESCTEXT" IS NOT NULL ORDER BY >"A11_DIM_PERIOD"."KEY"
In Prompt we go by only one column, in your query I see 2 columns. Since you need only Key use the below

SELECT "A11_DIM_PERIOD"."KEY" FROM "promptsource" WHERE "A11_DIM_PERIOD"."DESCTEXT" IS NOT NULL ORDER BY "A11_DIM_PERIOD"."KEY"
metalray
Hi Srini,
Thanks for the reply. I do need to key but I need the description(text) value too. The end user can not select the "Office Location" by ID/KEY only :)
The ID/KEY is used in the data warehouse because the fact tables only contains ID's but the dimension table has ID and Text value. I need to have both in the dropdownlist.
SriniVEERAVALLI
In that case you need to go with 2 prompts in BI and 2 parameter in BI Pub

SELECT "A11_DIM_PERIOD"."DESCTEXT" FROM "promptsource" WHERE "A11_DIM_PERIOD"."DESCTEXT" IS NOT NULL ORDER BY "A11_DIM_PERIOD"."KEY"

SELECT "A11_DIM_PERIOD"."KEY" FROM "promptsource" WHERE "A11_DIM_PERIOD"."DESCTEXT" IS NOT NULL ORDER BY "A11_DIM_PERIOD"."KEY"
1 person found this helpful
metalray
So the end-user has to select everything double? that is not too great.
It seems the "apply" of my dashboard prompts doesnt to anything unless I switch the
"automatic execution" in my report settings on. Which is really not what I prefer to do.
That means on every visit of the dashboard the report is executed even before the user
has the chance to select some meaningfull parameters.
metalray

Anyone an idea if there is a better way to do that?

metalray
Answer

I  gave up on it and now the whole parameter passing happens using the descriptive strings rather than IDs.

Marked as Answer by metalray · Sep 27 2020
1 person found this helpful
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 23 2013
Added on Jun 6 2013
#business-intelligence-applications, #business-intelligence-foundation, #business-intelligence-suite-enterprise-edition-obiee
9 comments
1,205 views