Hi,

this blog post is about creating Set Operations , at Report side,

There are a few restrictions on using Combined Queries to get Union , Intersection or Minus queries. Some of them are

1 – One cannot perform Combined Queries over different DP;

2 – The objects used on the Result Set couldn´t appear on the query panel;

3 – The objects for both queries must be in the same order, be of the same type  and be on the same number (if you retrieve 3 objects from the first query, you must retrieve the same numbger on the other(s) query(ies).

The approach suggested allow users to combine different DPs, with different number of objects and with different types.

As I cannot demonstrate properly how it works using e-fashion, I will use two excel sheets . The Excel sheets represents some People registered for 2 different course, let´s say Course 1 and Course 2.

Course 1 contains the following information :

Student Code, Student Name , Student Addres, specialization and measure From DP1

Here´s the data for Course 1

student code student name student address specialization

measure From DP1

1 Roger St 1 13 Cooking 1
2 Maria St 2 14 Engineering 2
3 Nadia St 3 15 Writing 3
4 Robert St 3 16 Cooking 4
5 Marcus St 3 17 Engineering 5
6 Ana St 4 18 Writing 6
7 Emerson St 4 19 Cooking 7
8 Humberto St 5 20 Engineering 8
9 Claudia St a 21 Writing 9
10 Roberta St b 22 Cooking 10

Course 2 is composed by

Student Code, Student Name , Student Addres, Interest and measure from DP2

and here´s the data for Course 2

student code student name student address Interest

measure from DP2

1 Roger St 1 13 Repolrting 1
4 Robert St 3 16 Big Data 4
5 Marcus St 3 17 Visualization 5
6 Ana St 4 18 Repolrting 6
7 Emerson St 4 19 Big Data 7
11 Paulo St 4 20 Visualization 11
12 Sergio St 4 21 Repolrting 12
13 Margarida St 4 22 Big Data 13
14 Nair St 4 23 Visualization

14

All objects except [measure from DP1] and [measure from DP2] are dimension, both are measures.

The challenge here is to determine, at report side :

The Union of both queries and display it on a table .([student sode] coming from either queries

The same for Intersection ([Student Code] that belongs to Query 1 and Query 2 simultanesously) , Course 1 – Course 2 (]Student Code] that is in Query 1 but not in Query 2) and Course 2 – Course 1 ([Student Code] in Query 2 but not in Query 1) . Based on student code.

Firstly I will import both Excel sheetsa into WEBI Rich Client which gives the following tables

/wp-content/uploads/2014/12/tables_603811.png

Then I will merge both DP in [student code], [student name] and [student address] , and Create detail variables for the other dimensions ([specialization det] and [interest det]) based on the merged [student code] dimension

Create two measures variables [Test 1] = If( Not ( IsNull([val1]));1;0) and [Test 2] = If(Not(IsNull([val 2]));1;0)

The Union is given , by default droppoing the three merged dimensions and the detail variables on a table

Union.png

[Intersection] = If ([Test 1] = 1 AND [Test 2] = 1;1;0)

[Course 1 – Course 2] = If( [Test 1] = 1 AND [Test 2] = 0;1;0)

[Course 2 – Course 1] = If([Test 1] = 0 AND [Test 2] = 1)

Filter by the “1” on the desired Operation ( Intersection , Course 1 – Course 2, or Course 2 – Course 1 ).

Heres the table with all the test.

final table.png

Cheers,

Rogerio

Thanks to Mak 1 from BOBJForum for his help

To report this post you need to login first.

16 Comments

You must be Logged on to comment or reply to a post.

  1. Michael To

    Hi Rogerio, can you tell me if this works for BICS connections (SAP BW connection)? I could not get unions and intersections to work on BICS connections.

    (0) 
  2. George Kan

    This is not working for me. When I merge two dimension and then try the IsNull for each of them, it returns always 0 for both.

    (0) 
      1. George Kan

        Hi Ricardo. Thank you very much for your response. Yes actually thats what I am doing. I would like to determine which elements from the dimensions are common in both/exist in one or the other. Do you have any clue how to do that ?

        Thank you in advance 🙂

        (0) 
        1. Ricardo Rodriguez Tejada

          Hi Kan, As you are merging dimensions you can’t do it by dimension (as you have one common dimension in both sides), you should use whichever metric instead. Hopefully you make it work.Regards, Rick.

          (0) 
        2. Rogerio Plank Post author

          Hi Kan,

          Ricardo is right, following this procedure, what you need is , as he said, two metrics, one from each query, then use the IsNull operator in this metrics.

          Suppose you got, from Query1 two objects, [dim1] and [measure1], and from Query2 [dim1] and [measure2], and you merge the two queries on [dim1].

          When [measure1] IsNull for one value of [dim1], this means that this value did not come from Query1 , equally, if [measure2] IsNull for a value of [dim1] this means that this value did not come from Query2.

          Conversely, if the values of [measure1] Is Not Null, the value of [dim1] belongs to Query1, and the same is valid for [measure2] in Query2

          Hope this has clarified my explanation,

          Any further doubts feel free to contact me.

          Cheers,

          Rogerio

          (0) 
          1. George Kan

            Guys thank you very much that really helped! I just created a metric for each of the two dimensions and then used this one to do the comparison, as rogerio suggested! Really thank you! 🙂

            (0) 
  3. Martin Villard

    Hello

    thanks for this example and clear explanation.

    I’ve redone it.

    then I try to modify the DP by inserting in the first spreadsheet  a second specialization “Engiennering” for Roger. Problem  : I get a multivalue for the [specialization det].

    Is There a workaround ?

    thanks

    Martin

    (0) 
  4. Patricia Ciavarelli

    Thank you so much for this post!  Someone from the BoB forums put me on to this as I was looking to make merged dimensions from custom variables.

    This was so useful and easy to get the hang of once I figured it out.

    THANK YOU!

    (0) 
    1. Rogerio Plank Post author

      Thanks for your comment Patricia,

      I take a look at BOB forum and see that Mak suggested you the post.

      Glad you like it !!!

      Regards,

      Rogerio

      (0) 
  5. Raja M

    Hi Rogerio,

    Does this work for reports based on BEX queries no universe ? I have 100 customersIDs in query1 and 20 customerIDs in query2. If 18 of 20  query2 customersIDs  exist in 100 customerIds of query1 I want to display all 100 customersIds and flag18 as exists in both.

    Thanks,
    Raja

    (0) 
    1. Rogerio Plank Post author

      Hi Raja,

      I´m not sure , I hava never worked with Bex , but I think that if Bex queries supports merged dimensions, it should work.

      Regards,

      Rogerio

      (0) 

Leave a Reply