The process of joining the data from different data sources based on a common dimension is referred to as Merging Dimensions. This document will walk you through the process of querying multiple data sources, merging dimensions, creating variables, and adding the variable objects to a report table.

Note: For this example, it is assumed that you already have two data providers from queries from different universes. In this example, one query is named Instructors and the other is named HR Details. The example is that a user has a report of all instructors for the current term and the wants to pull in the Years of Service for each instructor. The instructor data comes from one universe and the Years of Service data from another.

Sort Available Objects by Query

• Make sure you are in Design mode (ALT + 2)
• In the Main panel, make sure you have selected Document Objects in the row of icons.
• Click the Settings icon and make sure it is set to Queries View.

Merge Dimension Objects

Merge Dimensions allows you to join the two data sources based on objects with common values. Note that the objects do not need to have the same name, but must be the same type (character, date, number). You can also merge two variables, or merge a variable with another object. The variables must be dimension objects and must come from different queries. You cannot merge objects coming from the same query.

• In the list of Document Objects, select the first object to merge, hold CTRL and select the other object to merge with it.

Note that the second object will be grayed out, but you will still be able to select it.

• Click one of the object's More icons and select Merge.

• The new Merged Dimension will display in the Document Objects list.

Create Variables for Objects to be Displayed with Other Data Source

Business Objects has a rule that only non-merged objects from a single data source may be displayed in a table (or block) of data. In order to include objects from multiple data sources into the same block of data, you must build variables for each object, and those variables must be created as Details of the Merged Dimension.

• In the Main panel, click the Add a Variable button.

• This will bring up the Create Variable window.

• First, give your variable a name. It cannot be the same name as any of the objects in your query.
• Next, change the Qualification to Detail.
• Click the More icon in the Select a dimension menu.
• This brings up the Objects and Variables window. Select the Merged Dimension you just created and click OK.
  

Note: Make sure you are selecting the Merged Dimension you just created and not the object from either query.

• In the Formula box, for this example, simply double-click the object from the Objects list and the formula =[Years of Service] appears.
• Click OK to create the variable.
• It now shows up in the Document Objects list.

• Now you can drag the Yrs of Srv variable into the Data Assignment section in the Format panel to add it to your table.

• Repeat these steps to create variables for all non-merged objects that need to be added to the report table.