Overview
This document provides background information on the process of merging data from multiple queries in BusinessObjects. For specific steps, see Merge Data from Multiple Queries.
The merge function in BusinessObjects makes it possible to create a report that displays query results from multiple data sets. You can merge:
- Queries on different universes (data sets) in the Data Warehouse
- Multiple queries in the same universe
You might merge multiple queries in a single universe in order to:
- Display all values of a certain field in a crosstab, including blank rows.
- Display data in a single row rather than multiple rows.
- Retrieve data from a single universe for two different levels of detail, time periods, or populations. For example, you want to display test score information about both applicants and admitted students in the same report.
- Create a report in a single universe that combines data from a Standalone folder or another unjoined folder with data from other folders. Merging is the only way you can do this. Otherwise, you will get a Cartesian product error.
In order to use data from multiple queries in a single report, all of the queries must have at least one dimension object in common, for example, EmplID or DeptID. You must connect (merge) at least one dimension object from the first data provider with a dimension object from the other(s). Some basic rules guide this process:
- Merge the appropriate dimension objects that are common to both or all queries and meet the following criteria:
- Common values
- Common data type (character string, numeric, or date).
- Local variables that you created cannot be merged.
- You can use measure objects
from either query—they do not need to be merged.
- A measure object may calculate differently in a merged report. Measure objects can only be calculated at the lowest common level of the merged dimension objects between two data providers. For example, if a data provider with Month is merged with a data provider showing Month and Week, the table displays data aggregated at the Month level.
Detail Objects
You can use any unmerged dimension object in the first query you select. Unmerged dimension objects in the remaining queries are grayed (unavailable). For each grayed dimension object that you want to use in the report, you must create a detail object to associate it with one of the merged objects in that query
In the table below, Query 1 has one merged object and three other dimension objects. Query 2 has the merged object plus one other dimension object. You need to create detail objects for every unmerged dimension object in Query 2. (We chose to create detail objects for Query 2 because it has fewer unmerged objects than Query 1.)
In the example below, you can use either of the common fields in Query 1 or 2 in your report because they are merged. You can also use Dimension 1b, 1c, and 1d. You cannot use Dimension 2b.
In this example, we create a new variable, Detail of Dimension 2b. The Qualification is Detail. Under Associated dimension, we select Common Dimension 2a.
For the formula, we click Dimension 2b from the Available Objects list, then click OK.
After creating all the necessary detail objects, you can create a report displaying data from both/all queries. You can select the following objects:
- All dimension objects, plus the merged object, from the first query you select.
- Detail objects that you associated with the merged object.
- Measure objects from any of the queries.