Create a Master Detail Relationship

Create a master detail relationship to deliver information that would otherwise require two or more reports. For example, you can combine a list with a chart. The list can contain product lines and the chart can show details for each product line.

Master detail relationships must appear in nested frames to produce the correct results. You can create a master detail relationship in two ways:

• Use a parent frame for the master query and a nested frame for the detail query.

• Associate a report page with the master query and use a data container, such as a list or crosstab, on the page for the detail query.

Note that you cannot display parent data in the child frame or child data in the parent frame. Also, you cannot perform calculations across master detail queries.

You can use a master detail relationship to show data from separate data sources in a single report. However, the data sources must be contained in the same package.

If you are working with an SAP BW data source, you cannot use a data item from the master query that contains non-ASCII values.

To create a master detail relationship using queries that reference two different dimensional data sources, you must first link the members by aliasing the levels that contain the members.

Steps

1. To use a parent frame for the master query and a nested frame for the detail query, do the following:

   • In the Insertable Objects pane, on the Toolbox tab , drag a List, Repeater Table, or Repeater to the report.

   • Add a second data container to the object you previously inserted.

     You can insert a list, crosstab, chart, repeater table, or repeater into a list. You can add a list to a repeater table or repeater.

   • Add the data items you want to both data containers.

2. To associate a report page with the master query and use a data container on the page for the detail query, do the following:

   • Click anywhere in the report page.

   • In the Properties pane, click the select ancestor button  and click Page.

   • Click the Query property and then choose a query from the drop-down list.

   • In the Insertable Objects pane, on the Toolbox tab, drag a data container to the report.

   • Click OK to create the detail query.

3. To link a data item in the master query to a parameter in the detail query instead of to another data item, create the parameter in the detail query.

   Use parameters when you want to filter values at a lower level in the detail query.

   • Pause the pointer over the query explorer button  and click the detail query.

   • In the Insertable Objects pane, on the Toolbox tab, drag the Filter object to the Detail Filters box.

   • In the Expression Definition box, create the parameter .

   • Click OK.

4. Pause the pointer over the page explorer button , and click the report page.

   • Click anywhere in the report page.

   • In the Properties pane, click the select ancestor button and click Page.

5. Click the data container containing the details.

6. From the Data menu, click Master Detail Relationships.

7. Click the New Link button.

8. In the Master box, click the data item that will provide the primary information.

9. To link the master query to a data item, in the Detail box, click the data item that will provide the detailed information.

10. To link the master query to a parameter, in the Parameters box, click the parameter that will provide the detailed information.

11. Repeat steps 7 to 10 to create other links.

    Tip: To delete a link, click it and press the Delete key.

12. Click OK.

Tip: To avoid seeing the same data item twice in the report, click the data item in the data container driven by the detail query, and click the cut button. This removes the item from the report display, but keeps it in the query.

Link Members from Two Dimensional Data Sources

If you create a master detail relationship using queries that reference two different dimensional data sources, you may need to create a relationship between levels with the same member captions but different Member Unique Names (MUNs). For more information about MUNs, see Recommendation - Use Member Unique Name (MUN) Aliases.

For example, you may want to link the Americas member in the Sales territory level in the Great Outdoors cube with Americas in the GO Subsidiary level in the Great Outdoors Finance cube. To create the relationship, you must alias the levels that contain the members that you want to link.

Steps

1. In the parent frame or the report page, double-click the level that contains the member that will provide the primary information.

   The Data Item Expression dialog box appears.

2. In the Expression Definition box, use the expression in the following function:

   caption(expression)

   For example, caption([great_outdoors_company].[Sales Territory].[Sales Territory].[Sales territory])

3. Click OK.

4. Repeat steps 1 to 3 for the level in the data container that contains the details.

   A caption alias is created for each level. You can now use the caption alias for each level to create a master detail relationship using member captions, which are the same, instead of MUNs, which are different.

You can now create the master detail relationship between the two levels.