7 Replies Latest reply on May 5, 2017 9:22 AM by Christian Berg

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

    3212463

      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

        • 1. Re: Intersection of data based on multi select prompt in OBIEE 10g
          Christian Berg

          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.

          • 2. Re: Intersection of data based on multi select prompt in OBIEE 10g
            Gianni Ceresa

            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...

            • 3. Re: Intersection of data based on multi select prompt in OBIEE 10g
              3212463

              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.

              • 4. Re: Intersection of data based on multi select prompt in OBIEE 10g
                Christian Berg

                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 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.

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

                • 5. Re: Intersection of data based on multi select prompt in OBIEE 10g
                  Gianni Ceresa

                  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...

                  • 6. Re: Intersection of data based on multi select prompt in OBIEE 10g
                    3212463

                    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.

                    • 7. Re: Intersection of data based on multi select prompt in OBIEE 10g
                      Christian Berg

                      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.