Linked reports allow you to add custom drilldowns to cells or charts. Drilldowns are a means of exploring data points by "drilling down" into their background data.

Note: Drilldowns work in the Report Viewer, but the data is not shown in exported reports.

Drilldowns require you to have a child report, which is an Advanced or Express report that contains the background data for the parent report. You can create a drilldown on a Dynamic Cell or a chart by linking the child report to the parent report cell. Each data value, or each chart series, when clicked, filters the child report by its respective value before opening the resulting report in a window at the cursor.

Note: Currently, the prompt window will not appear when drilling down into a child report that contains prompting parameters. However, this can be partially bypassed by adding the prompting parameter in a hidden cell on the parent report so that the user will be prompted at runtime for the parameter value.

Since drilldowns are themselves reports, they can have interactive sorts and filters, dynamic visualizations, they can be exported, and they can even have their own drilldowns.

Default linking

By default, the data category that corresponds to the linking cell filters the closest joined category on the linked report. See Joins for more information. If there is no join path, then you must set the linked fields manually in the Fields page.

For example, a parent report links an Employees data field to a child report with an Orders category. The two categories are joined on Orders.EmployeeId >> Employees.Id. For each Employees row, its Employee.Id value filters the linked report down to the Order rows with matching Orders.EmployeeId values.

Tip: Filters on the parent reports do not cascade down to linked reports. Linking filters do not cascade down to grand-children, or child reports of the linked report.

Adding linked reports

To add a linked report drilldown:

1. Make an Advanced or Express Report that contains the drilldown data. This will be the linked child report.

   Linked reports typically open in a small window, so the child report should be simple and concise. Avoid large fonts, too much static content, or making it too large in size. You should also set the Report Options for No Data Qualify Display Mode to Show Report in order to show an empty drilldown instead of a popup window for links with no data.

2. In the parent report, select the cell to link, then click the Link Reports  icon. The cell can contain a data field, formula, or a column-based or row-based chart.

3. Select the child report, then click Okay.

Caution: Because the linked report is a separate report from the parent, if you move the linked report to another folder, or remove the linked category, the link will be lost. You will have to edit the parent report and add it again.

To remove a linked report drilldown:

1. Click the link  icon, or select the cell and click the Link Reports  icon.

2. Click the Remove Link  icon.

3. Click Okay.

Fields

You may want to link on different fields or categories than the default join. The Fields page allows you to specify which categories and fields are used to determine the drilldown data.

The Fields page is suitable for the following situations, among others:

• The default join is not the link you want to use

  Example: Linking on related fields other than the Id field, such as "Region"

  Employees.Region >> Orders.ShipRegion

• No join exists between the From and To categories

  Example: Categories have related fields but are not joined, such as Orders and Suppliers

  Orders.ShipCity >> Suppliers.City

• The From and To categories are the same

  Example: Fields are related to other fields in the category, such as Employee X supervises Employees Y and Z

  Employees.Id >> Employees.ReportsTo

Tip: The From fields from the parent report filter the To fields on the child report.

To specify the linked fields:

1. Select the From Category and To Category from their respective lists.

2. Click  Add for each set of linked fields to add.

   Use multiple linked fields to show only the drilldown rows that satisfy all the link conditions.

3. For each set of linked fields, select the From Field and To Field.

4. When finished, click Okay.

Formula

The Formula tab allows you to specify a custom formula in order to further filter the data passed from the linked report. The formula must return True or False. The formula is evaluated for each row in the parent report, and if the condition is not met, the data is excluded from the linked report. See What are formulas for help with using conditional formulas.

• Add a data field by dragging and dropping it into the Formula pane or double-clicking it. Or enter it manually using the following format: {DataCategory.DataField}.

Caution: Linked report formulas support only one data field. If multiple data fields are used, all but the first will be ignored.

• Add a Parameter by entering it manually using the following format: @ParameterName@.

• Add a function by dragging and dropping it into the Formula box or double-clicking it. Or enter it manually.