I'm struggling to achieve certain things with obiee prompts which theoretically don't sound too complex, yet turn out to be difficult or impossible to implement. Any ideas, tips, or confirmations that this in effect is impossible, would be greatly appreciated.
1) Limiting a variable prompt by a column prompt
I have defined a column prompt based on column STORE. The prompt is required, and allows multiple value selection.
I also have defined a variable prompt based on the same column (choice list value = all column values). This prompt is required as well, but does not allow multiple value selection. The 'limit values' option of this prompt is set to the column prompt.
=> OBIEE does not implement the limitation for some reason, whatever selection I make in the column prompt, the variable prompt always returns the full set of values from the STORE column rather than the limited selection I expected.
(The idea behind this is being able to compare the values of 1 store to the average of a selection of stores. One would select stores x, y and z from the first prompt, and store x from the second, resulting in an analysis that sets off the results for store x against the average of stores y and z.)
2) Filtering an analysis query based on both a column prompt and a variable prompt
Same setup as above, but the variable prompt is not limited by the column prompt. In the analysis query, I added as filter:
"STORE" is prompted
OR "STORE" = '@{STORE_PROMPT_VARIABLE}'
When executed, the second part of the comparison is overwritten by the first part. For example, selection stores y and z from the column prompt, and store x from the second, does not result in the expected:
"STORE" is equal to y; z
OR "STORE" is equal to x
but in;
"STORE" is equal to y; z
OR "STORE" is equal to y; z
(The OBIEE version used is 11.1.1.6.8.)