Skip to Content

How to Find Matches e No Matches in Dimensions from 2 Queries (Union, Intersection and Difference)


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


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


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


[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



Thanks to Mak 1 from BOBJForum for his help

You must be Logged on to comment or reply to a post.
  • 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.

    • Hi George. Probably this is because you are using the dimensions itself. You need to compare (Isnull) the metrics, in order to make it work.

      Regards, Rick

      • 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 🙂

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

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



  • 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 ?



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


    • Thanks for your comment Patricia,

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

      Glad you like it !!!



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


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



  • Hi Rogerio,


    I have a similar requirement


    I have 2 data providers Q1 contain Material and Q2 contains ISBN. I am merging both at report level.

    (Material and ISBN is list of numbers and both are string)


    Material   ISBN   Formual Needed

    111          111          A

    121          122         B

    123          124         C

    Now I want to write a formula based on that saying that

    if (Material =ISBN Then “A” (Eg:111)) ElseIf (if the number only in Material then “B”(eg. 121,123) Else (IF the number only in ISBN then “C”(eg.122,124 )))


  • Thanks Rogerio

    This is a truly useful technique, especially when clients consistently want to mix spreadsheet data with tables from a universe.

    I used to wonder what in the world the “Detail” qualification was for, but have since found it very useful, in particular for spreadsheet data.

    Cheers, Jay