Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

multiselection prompt value in 'evaluate()'

Received Response
1
Views
3
Comments
chris.du
chris.du Rank 2 - Community Beginner
edited Aug 13, 2024 5:24PM in Oracle Analytics Cloud and Server

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_items
1item1:item2:item4:item7
2item1
3item4:item8
4item1: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 promptShow row with id
item11, 2, 4
item1, item71, 4
item1, item8-
item2, item41, 5
item4, 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

Answers

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    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
    Jerry Casey Rank 6 - Analytics Lead

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

    pastedImage_0.png

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

    pastedImage_1.png

    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
    chris.du Rank 2 - Community Beginner

    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