Using Set Operations in OBIEE 11g ( Union / Intersect / Minus)

There are 4 Set Operations available in OBIEE in OBIEE Answers Report Level to leverage the usual set operations functionality that a normal SQL writer would do to join two disjoint queries.

4 Set Operations are:

Set Operator	Effect on Results
Union	Returns non duplicate rows from all requests.
Union All	Returns all rows from all requests, including duplicate rows.
Intersect	Returns rows that are common to all requests.
Minus	Returns rows from the first request that are not in the other requests.

In OBIEE you can join distinct Subject Areas using set operations as well. The below article talks about the requirement and then solution using Set Operations.

Requirement 1: Show Open and Closed Service Requests(SR) Count by Month.

You cannot get Open and Closed SRs count in same report unless you create a RPD column and its not advisable to create RPD column for everything which you can achieve by creating simple report.

Solution using Union Operation:

Create a Simple Report having three columns as Month , Open SR Count and Closed SR Count.
Add Column Formula for Month as Opened Month and Count(SR_NUMBER) as Open SR Count and Closed SR Count as sum(0) to give this column as Aggregation data type.
In the Filters , Add SR Open Year = 2015 to get Open SR Count for 2015 year. This filter will give you all the SRs Count that was opened in 2015 , irrespective of the current SR status.

See below :

Open SR Count = Count(SR_NUMBER)

Closed SR Count = SUM(0)

Month = SR Open Month

Now Click on The Button to combine /Union another analysis to get Closed SR Count.

 Select the Subject Area as Service Request and you will see that Set Operations button is now added and you can create another analysis now with different columns and filters. Select the operator you want to choose.

 Here you will have to drag the columns as :

Drag SR Closed Month column next to SR Opened Month

Drag SR_NUMBER Column to Open SR Count

Drag SR_NUMBER Column to Closed SR Count

Then go to edit the formula and edit formula for last two columns as :

 Second Column will be Open SR Count, Edit the Formula to sum(0) as results will come from previous analysis. Change the heading as Open SR Count.

Third Column will be Closed SR Count, Edit the formula as Count(SR_NUMBER) and change the heading to Closed SR Count.

Add a filter SR Closed Year = 2015, to fetch you all data for 2015.

Click on Results tab to see the results.

This report is fetching all SRs which were opened and Closed in Year 2015 by Month.

Note: You can see January Closed SRs more than Opened because the data exists in warehouse for years prior to 2015.

 This was how you will use Union Operation.

In Progress for other Operations.