This procedure includes the following:
Note: This topic is also covered in the Create Combined Queries webinar.
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:
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.
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.
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
or measure object 
) 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.
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).
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.
Combined Queries and Merged Queries
There are several key differences between combined queries and merged queries.
Combined Queries |
Merged Queries |
|---|---|
| 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
- Create the first query by choosing result objects and creating query filters.
- See Create a Query for information on how to create a query.
- Click Add a Combined Query .
- If needed, double-click the join to select Intersection or Minus. Union is the default.
- Create the query filters.
- (Optional) Repeat steps 2-4 to create additional combined queries.
- Click Run.
