Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
Showing results for 
Search instead for 
Did you mean: 
Former Member


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 codestudent namestudent addressspecialization

measure From DP1

1RogerSt 1 13Cooking1
2MariaSt 2 14Engineering2
3NadiaSt 3 15Writing3
4RobertSt 3 16Cooking4
5MarcusSt 3 17Engineering5
6AnaSt 4 18Writing6
7EmersonSt 4 19Cooking7
8HumbertoSt 5 20Engineering8
9ClaudiaSt a 21Writing9
10RobertaSt b 22Cooking10

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 codestudent namestudent addressInterest

measure from DP2

1RogerSt 1 13Repolrting1
4RobertSt 3 16Big Data4
5MarcusSt 3 17Visualization5
6AnaSt 4 18Repolrting6
7EmersonSt 4 19Big Data7
11PauloSt 4 20Visualization11
12SergioSt 4 21Repolrting12
13MargaridaSt 4 22Big Data13
14NairSt 4 23Visualization


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.



Thanks to Mak 1 from BOBJForum for his help

Labels in this area