multiselection prompt value in 'evaluate()'

chris.du

Hi all,

I have a strange requirement, which I'm not sure how to implement in OBIEE.

In my table I have a column with a list of items in a string, something like:

id	list_of_items
1	item1:item2:item4:item7
2	item1
3	item4:item8
4	item1:item7
5	item2:item4

I have created a Dashboard prompt listing available items ( let's say a list like item1, item2, item3, item4, ..., item 10 ) and created a presentation variable (param_ms) for this.

My Analysis should only show this rows that contains all selected values in my prompt.

selected values in prompt	Show row with id
item1	1, 2, 4
item1, item7	1, 4
item1, item8	-
item2, item4	1, 5
item4, item8	3

In PL/SQL I would compare this as nested table with 'multisubset of' condition.I tried to call a PL/SQL function in a column formula using evaluate, but how can add my presentation variable.

I tried: EVALUATE('my_function(%1, %2)', "my_table"."list_of_values", @{param_MS}). But that's not working, because every selected value in my variable is handled as single value for a placeholder.

Has anybody an idea how to check this?

Best Regards

chris

Jerry Casey

Hi Chris,

I haven't done this, but what I might try looks like this, without using EVALUATE:

When you use this syntax: @{param__ms}{item1,item7}, the value passed to the filter looks like this: 'item1','item7'.

I'm wondering if you can replace the comma in that result with '||%'.  Then, you can nest that result between '%'s, getting something like:

list_of_items LIKE '%'||REPLACE('@{param__ms}{item1,item7}',',','%||')||'%'

the sql would hopefully look like: List_of_items LIKE '%Item1%Item7%'

Just an idea,

Good luck!

jerry

EDITED to remove ['@'] format modifier.

Jerry Casey

I tried this with a CSV field of my own.  The query responds correctly to this filter:

I built a dashboard prompt with checkbox options, using a variable prompt, and it works:

I was mistaken about the need to include the "['@']" format modifier.  In fact, it prevented the filter from working.

I hope this helps.

Jerry

chris.du

Hi Jerry,

sorry for my late answer ...

Your answer helped me a lot. I got it also working using this filter Expression:

"MyTable"."list_of_items" LIKE '%' || TRIM(BOTH FROM REPLACE(REPLACE(CAST('''@{param_MS}''' AS VARCHAR(4000)) , '''', '') , ',', '%'))  || '%'

So I needed also to do the CAST, because the variable was handled like a table column name.

Many thanks for your help!

Regards

chris