9 Replies Latest reply on Jun 25, 2013 12:33 PM by metalray

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

metalray Jun 6, 2013 12:12 PM

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.

1. Re: OBI Dashboard & Dasboard prompt to trigger BI Publisher Report in Dashboard

metalray Jun 6, 2013 7:55 AM (in response to 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.
Like Show 0 Likes(0)

Actions
2. Re: OBI Dashboard & Dasboard prompt to trigger BI Publisher Report in Dashboard

SriniVEERAVALLI Jun 6, 2013 9:56 AM (in response to metalray)

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
Like Show 0 Likes(0)

Actions
3. Re: OBI Dashboard & Dasboard prompt to trigger BI Publisher Report in Dashboard

metalray Jun 6, 2013 11:57 AM (in response to SriniVEERAVALLI)

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.
Like Show 0 Likes(0)

Actions
4. Re: OBI Dashboard & Dasboard prompt to trigger BI Publisher Report in Dashboard

SriniVEERAVALLI Jun 6, 2013 1:02 PM (in response to metalray)

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"
Like Show 0 Likes(0)

Actions
5. Re: OBI Dashboard & Dasboard prompt to trigger BI Publisher Report in Dashboard

metalray Jun 6, 2013 2:54 PM (in response to SriniVEERAVALLI)

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.
Like Show 0 Likes(0)

Actions
6. Re: OBI Dashboard & Dasboard prompt to trigger BI Publisher Report in Dashboard

SriniVEERAVALLI Jun 6, 2013 3:03 PM (in response to metalray)

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
Like Show 0 Likes(0)

Actions
7. Re: OBI Dashboard & Dasboard prompt to trigger BI Publisher Report in Dashboard

metalray Jun 6, 2013 3:26 PM (in response to SriniVEERAVALLI)

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.
Like Show 0 Likes(0)

Actions
8. Re: OBI Dashboard & Dasboard prompt to trigger BI Publisher Report in Dashboard

metalray Jun 24, 2013 9:55 AM (in response to metalray)

Anyone an idea if there is a better way to do that?
Like Show 0 Likes(0)

Actions
9. Re: OBI Dashboard & Dasboard prompt to trigger BI Publisher Report in Dashboard

metalray Jun 25, 2013 12:33 PM (in response to metalray)

I gave up on it and now the whole parameter passing happens using the descriptive strings rather than IDs.
Like Show 1 Likes(1)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.