SQL Developer

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.

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.

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.

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"

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.

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"

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.