BusinessObjects: Merge Data from Multiple Queries

Overview

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

All of the queries must have at least one dimension object in common, for example, EmplID or DeptID. For background information, see Overview: Merging Data from Multiple Queries.

This procedure includes the following:

Note: This topic is also covered in the Merged Dimensions webinar.

Step-by-Step Process

Create Queries

Before beginning this procedure, you need to create a new report by clicking Web Intelligence in the Application section of the BusinessObjects home page and selecing the desired Universe.

  1. Create the first query.
  2. To create the second query, click Add Query.

    3. Screenshot of the Query panel showing the Add Query button

  3. Click Universe on the Select a Data Source window.
  4. Click OK.
  5. Select a Universe and data set.
    • In some cases, this will be the same universe as the first query.
  6. Click OK.
  7. Select the result objects.
    • Query 1 and Query 2 must have at least one dimension object Dimension Object Icon in common.
  8. Create the query filters.
    • To use the same prompts for multiple queries, make sure the prompt text and operators are identical.
    • Repeat steps 2-8 for any additional queries.
  9. Click Run.
    • When multiple queries are included in a report, the Run button becomes a drop-down list. If you click the drop-down arrow, there are options to run a specific query or run all queries. If you click Run and not the drop-down list, it runs all queries.
  10. Respond to any prompts.
  11. Click Run.

Merge Common Dimension Objects

Notes:

  • You must merge at least one dimension object in order to use objects from both queries in the same report.
  • It may be useful to switch from Alphabetical View to Queries View or Folders View on the Document Objects panel. Click Main Panel Main Panel Icon, then Document Objects Document Objects Icon. Click View modes and settings View modes and settings Icon and select the view.

    Screenshot of the Main panel showing the view nodes and settings menu.

  1. CTRL + click the dimension objects you want to merge in the Document Objects panel.

    2. Screenshot of the Document Objects panel showing values selected and the More menu expanded.

  2. Click the three-dot More menu for one of the objects and select Merge.
  3. Repeat steps 12-13 to create additional merged dimension objects. Depending on how you are viewing the objects in the Document Objects panel, the merged dimensions display:
    • Alphabetical view - under a single heading with an Expand Node arrow next to the object which you can click to see the individual merged objects.
    • Queries View and Folder view - in a Merged Dimensions folder.
  4. To change the name or source dimension of a merged dimension object, click the three-dot More menu for the merged dimension object and select Edit Properties.

    5. Screenshot of the Document Objects panel showing the More menu expanded next to the dimension object.

Edit a Merged Dimension Object

  • To remove an specific object from a merged dimension, click the three-dot More menu next to the object in the Document Objects panel and select Remove from merge. This only applies if there are more than two items in a merge.
  • To add a specific object to a merged dimension:
    • Click the object on the Document Objects panel.
    • CTRL + click the merged dimension object.
    • Click the three-dot More menu next to either selected object and select Add to merge.

    Screenshot of the Document Objects panel the More menu expanded with Add to Merge visible.

Create Detail Objects

Notes:

  • When creating a report that displays objects from multiple queries, you can include:
    • Any merged dimension objects.
    • Unmerged dimension objects from the first query you select. You must create detail objects for unmerged dimension objects from any additional queries.
    • Measure objects from either query. However, the measure objects may calculate differently because of the new objects in the report.
  • Save time by creating detail objects for the query with the fewest unmerged dimension objects. In this procedure, Query 2 has fewer unmerged dimension objects than Query 1. There is one merged dimension (Emplid).

  1. Click Add a New Variable Add Variable Icon on the Document Objects panel.
  2. Enter a Name.

    3. Screenshot of the top of the Create Variable window showing the name and qualification fields and the display list of dimensions button

  3. Select Detail from the Qualification drop-down list.
  4. Click Display list of dimensions Display Dimension List Button.
  5. Click the applicable merged dimension object on the Objects and Variables window and click OK.
    • If there are multiple values of the detail object for each value of the associated dimension, the #MULTIVALUE error displays in the table. You need to select a merged dimension object that has a one-to-one or many-to-one relationship with the detail object.
  6. Double-click the applicable dimension object in the Objects list.

    7. Screenshot of the Create Variable window showing a selected object in the Objects section.

  7. Click OK.
  8. Repeat steps 16-22 for any additional detail objects.

Create a Report with Data from All Queries

  1. Click or right-click the Report 1 tab and select New.
  2. Hold down the CTRL key and select the objects to display in the Available Objects panel. You need to select:
    • The merged dimension object(s)
    • Any dimension objects from the first query
    • Any measure objects from the second query
    • The detail objects you created for the second query

    Screenshot of the document object panel showing multiple objects selected

  3. Drag the selected objects onto the blank new report.
  4. If your detail object column displays the #MULTIVALUE error:
    1. Click the table once to select it.
    2. Click the Properties Panel Properties Panel icon, Report Element Panel Report Element Icon, then Display Settings Display Settings Icon.
    3. Uncheck the Avoid duplicate rows aggregation box under the Columns and Rows header.

Hide Blank Cells

Your table may display blank cells in rows where one data provider contains values and another does not. To set a filter to hide blank cells:

  1. Verify the report is open in Design mode.
  2. Click a cell in the column that displays blanks.
    • You may need to double-click the cell to select it, depending on if the table had previously been selected.
  3. Right-click on the selected cell.
  4. Click Data and select Add Filter.
  5. Click Settings Settings icon on the Select Value window.
  6. Select Is not Null box from the Operator drop-down list.

    7. Screenshot of the Settings window showing the Operator field and back button.

  7. Click Back.
  8. Click OK.

Turn on Auto-Merge Dimensions Option

When the Auto-merge dimensions option is turned on, dimension objects with the same name are automatically merged. It is off by default, but can be turned on for a specific document.

  1. Verify the report is open in Design mode.
  2. Click Main Panel Main Panel icon and Document Properties Document Properties icon.
  3. Expand the Data Options section.

    4. Screenshot of the Document Properties panel showing the data options and auto merge dimensions toggle.

  4. Click to toggle the Auto-merge dimension to Yes.
  5. Click Apply.