multiselection prompt value in 'evaluate()'

Question

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:

idlist_of_items1item1:item2:item4:item72item13item4:item84item1:item75item2: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 promptShow row with iditem11, 2, 4item1, item71, 4item1, item8-item2, item41, 5item4, item83

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

chris.du · Answer

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

Jerry Casey · Answer

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

Jerry Casey · Answer

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.