BusinessObjects: Create Combined Queries

This procedure includes the following:

Overview of Combined Queries

Combined queries are created within a single universe and return a single data provider. They differ from merged queries in that they must be created on the same universe. Although the following examples use two queries, it is possible to create eight or more combined queries. There are three ways to combine queries:

Graphic Union Query 1 and Query 2

Union Query: Displays all the results that appear in both Query 1 and Query 2. For example, you want to see all the students who took either Chem 125 or Chem 130.

Screenshot of Diagram of  Intersection Query

Intersection Query: Displays only the results that appear in both Query 1 and 2. For example, you want to see all the students who took Chem 125 and also took Chem 130.

Screenshot of Diagram of Minus Query

Minus Query: Displays all the results in Query 1 that are not in Query 2. For example, you want to see:

  • Donors who pledged last year but have not yet pledged this year.
  • Employees with health insurance who do not also have dental coverage.

Rules for Combined Queries

Each object in the first query's Result Objects must have a corresponding object in the second query. When you create a combined query, BusinessObjects puts the same results in Query 2 that you put in Query 1. If you add or remove objects from Query 1 but not Query 2, the query will not run. Note: The query filters do not have to match.

You can create advanced combined queries that do not have the same result objects in all queries. In order to match, the corresponding object in the additional queries must either be:

  • The same qualification (dimension Dimension object icon or measure object Measure Object icon) and data type (character string, numeric, date) as the object in the first query, or a constant OR
  • A null.

To see an object's type, hover your mouse over it in the Result Objects section of the Query Panel.

Screenshot of Object's type

Most universes in the Data Warehouse have a Report Objects folder. In the Blanks, Nulls, and Zeroes subfolders, there are special objects with null values. They are placeholders that allow the combined query to run properly.

  • A string object in Query 1 can be matched with a Blank object in Query 2 .
  • A date object in Query 1 can be matched with a Null Date object in Query 2.
  • A numeric object in Query 1 can be matched with a Null Number object in Query 2
  • A measure object in Query 1 can be matched with a Zero Measure object in Query 2.

In the following example, Query 1 contains three different types of dimension objects: Character string (EmplId), Number (Stdnt Car Nbr), and Date (Action Dt).

Screenshot for Results Objects Query 1

Query 2 includes EmplId. Stdnt Car Nbr is replaced with Null Number 1. Action Dt is replaced with Null Date 1. The two null objects are placeholders that allow the combined query to run. The placeholders must be in the same order as their equivalents in the original query.

Screenshot for Results Objects Query 2


Combined Queries and Merged Queries

There are several key differences between combined queries and merged queries.

Combined Queries
Combine Query icon
Merged Queries
Add Query button
Always within the same universe. Can use different universes at the same time.
Can select the join (union, intersection, minus). Can't select the join.
Must have identical or equivalent result objects. Result objects (type and number of) can be different.

Step-by-Step Process

  1. Create the first query by choosing result objects and creating query filters.
  2. Click Add a Combined Query Combined Query icon.
  3. Screenshot of the top of the Query Panel showing the Add a Combined Query icon.

  4. If needed, double-click the join to select Intersection or Minus. Union is the default.
  5. Screenshot of the Query panel showing the join button.

  6. Create the query filters.
  7. (Optional) Repeat steps 2-4 to create additional combined queries.
  8. Click Run.