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

Intersection of data based on multi select prompt in OBIEE 10g

Received Response
14
Views
7
Comments
3212463
3212463 Rank 3 - Community Apprentice
edited Aug 13, 2024 6:02PM in Oracle Analytics Cloud and Server

Hello all,

I am working on OBIEE 10g environment and want to design solution for below Requirement as:

Need to have multiselect prompt for User selection.

After selecting multiple users from prompt, we need to give intersection of measure data.

Eg, if user 'a', 'b', 'c' selected from prompt, then in report query it should appear as

user = 'a'

and user = 'b'

and user = 'c'

In 10g there is no provision to set presentation variable on multi select prompt. If I simply say 'user is prompted' in report query it works as user in ('a','b','c'), which gives union of data and not intersection.

Please suggest how it need to handled, we have DB2 database.

Any i/p for functions (on OBI or on DB2) which can aggregate stings (concat multi record o/p in single record) will also help a lot.

Thanks

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    That's not really how querying with an *AND" operator works. A WHERE clause with this

    3212463 wrote:user = 'a'and user = 'b'and user = 'c'

    ...will basically never yield anything because the attribute "user" can never be 'a' AND 'b' AND 'c' at the same time.

    And since you already mention the term "intersection" - set operations also support "Intersect" so you can just have 3 requests intersecting in one analysis.

  • Gianni Ceresa
    Gianni Ceresa Mod

    Christian already said almost everything

    So you look for a set operation and not a by row operation. What you wrote just doesn't match, you can't with a simple query do what you asked (try to write the SQL doing what you want and I can bet whatever you want it's a bit more than "user='a' and user='b' and user='c' ".

    Redefine what you are trying to achieve and, if looking for an input on here, post a better example of what's the context because, as Christian said, as it is it will basically never yield anything because the attribute "user" can never be 'a' AND 'b' AND 'c' at the same time...

  • 3212463
    3212463 Rank 3 - Community Apprentice

    Hello, Thanks for your reply.

    Basically what I am looking for is: If I have Activity table and data as shown below:

    Activity     User

    A1               a

    A2               a

    A2               b

    A2               c

    A3               a

    A3               b

    and I select user a,b,c from multi select prompt (in 10g where I cant set presentation variable for multi select),

    then my report should list only those activities where all a, b and c are tagged.

    With above table data it should return A2 as o/p.

    @Christian: to implement intersect report in OBI, how should I pass a ,b ,c as separate i/p to different intersect queries, and number of users selected from prompt will also be dynamic.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner 
    3212463 wrote:@Christian: to implement intersect report in OBI, how should I pass a ,b ,c as separate i/p to different intersect queries, and number of users selected from prompt will also be dynamic.

    That's what presentation variables are used for. You don't pass column prompt -> column filter but presentation variable -> column filter.

    3212463 wrote:Activity UserA1 aA2 aA2 bA2 cA3 aA3 band I select user a,b,c from multi select prompt (in 10g where I cant set presentation variable for multi select),then my report should list only those activities where all a, b and c are tagged.With above table data it should return A2 as o/p.

    Yes that's and intersect and not an "AND".

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    For that kind of requirement you can maybe solve it with a bit weird workaround as long as things are exactly as posted.

    You will need a measure (in your example you don't have any measure) which count the number of users by activity (so a count / count distinct on the user). In the analysis you apply the filter on users and display the activity and the count of users by activity.

    In your example you want to get only rows where the count of users is 3.

    How to make that one dynamic based on the number of selected users in the prompt?

    You can count how many occurrences of the character separating values you have in the variable value with users (can't remember if it's a ',' or a ';').

    You add 1 to the count of that character and filter the country of users = the count of separator + 1.

    As I said it's a bit weird workaround...

  • 3212463
    3212463 Rank 3 - Community Apprentice

    Hello,

    Yes, my bad, I want intersection and not 'and', however, how to achieve it?

    As I mentioned we are using 10g so don't have option to use presentation variable with multi select prompt.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Then you'll need 3 prompts with 1 var each.

    OR you combine one normal multi-valued prompt with Gianni's solution - but you'll actually need to filter a sub-request instead of the main analysis.