Linked Reports (Drilldowns)

Linked Reports are reports linked from cells or charts as a means of exploring data points by “drilling down” into their background data. Use the Advanced Report Designer to add them.

The terms Linked Reports and Drilldowns are used interchangeably throughout the application and this document, but they refer to the same concept.

Note

Drilldowns work in the Report Viewer and Dashboard Viewer, but the linked data is not included in exported reports or dashboards.

Drilldowns require a child report, which can be an Dashboard, Advanced, CrossTab or Express Report that contains the background data for the parent report. Drilldowns can be created on a Dynamic Cell or a chart by linking to the child report from the parent report. Clicking each cell value or chart series will filter the child report by its respective value before opening it. Depending on system configuration, the child report will open at the mouse cursor location, in the center of the screen or in a new report tab.

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.

Caution

Adding a drilldown to a cell may override or interfere with any linked Action Event on the cell. Adding a linked report to a chart may override some aspects of the chart’s basic interactivity.

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

Linking Types

Default Linking

By default, the Data Field in the parent report’s linking cell filters the closest joined Data Field on the child report. The article on Joins has more information on data field joining. If there is no direct join path, then fields may be linked manually in the Fields tab of the Linked Report Wizard.

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. Therefore, clicking on an Employee name in the parent report will drilldown to a list of orders that the employee made.

Tip

Linking filters do not cascade down to grand-children, or child reports of the linked report.

Manually Defining Link Fields

To link on different fields or categories than the default link, use the Fields tab of the Linked Report Wizard. The From fields from the parent report filter the To fields on the child report. The Fields tab is suitable for the following situations:

Tip

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

• The default join is not the desired link 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

Conditionally Link with Formula

Note

This feature is disabled in system configuration by default. Contact the system administrator for more information.

Use the Formula tab of the Linked Report Wizard to conditionally enable drilldowns on specific values. By default, once a drilldown is setup, all records on the report can drilldown to the child. By adding a Formula, only values meeting the condition will permit drilling.

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, drilling down to the child report is not enabled.

For example, the following formula could be used to create a drilldown to a report showing a suppliers list if and only if there are items on order or the quantity in stock is less than the product’s reorder level. Those products that do not meet that criteria cannot be drilled down.

Or({Products.UnitsOnOrder} > 1, {Products.UnitsInStock} <= {Products.ReorderLevel})

Therefore, only Queso Cabrales, Gorgonzola Telino, and Marscarpine Fabioli meet the criteria. Clicking on the other products will have no effect.

See the Formulas article for assistance with conditional formulas.

Adding Linked Reports

1. Create a new Dashboard, Advanced, CrossTab or Express Report that contains the drilldown data. This will become the linked child report.Linked reports typically open in a small window, so the child report should be simple and concise. Avoid large fonts, a lot of static content, or making it too large in size. The child report‘s General Option for No Data Qualify Display Mode should be set to Show Report in order to show an empty drilldown instead of an error message.
2. In the parent report, select the cell to link then click the Link Reports icon in the toolbar. This will open the Linked Report Wizard.
   

   The Linked Report Wizard in v2021.1+.

3. Select or search for the desired child report. This will use default linking to drilldown to the child report.
   

   To manually define link fields:

   

   The Fields tab of the Linked Report Wizard

   1. Select the From Category and To Category from their respective dropdowns.
   2. Click the Add button for each set of linked fields to add. Use multiple linked fields to show only the drilldown rows that satisfy all of the link conditions.
   3. For each set of linked fields, select the From Field and To Field.

   To conditionally link with formula:

   

   The Formula tab of the Linked Report Wizard

   1. 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 conditional formulas support only one data field. If multiple data fields are used, all but the first will be ignored.
   2. Add a Parameter by entering it manually using the following format: @[email protected].
   3. Add a function by dragging and dropping it into the Formula box or double-clicking it, or enter it manually.
4. Click Okay to close the Linked Report Wizard.

Caution

Because the child is a separate report from the parent, if the child report is moved to another folder, or the linked field is removed from the report, the link will be broken.

Modifying Linked Reports

1. Click the Link icon in the corner of the linking cell, or click the cell and then click the Link Reports icon in the toolbar.
2. Interact with the Linked Report Wizard just as if the link is being added (continue at step 2).

Deleting Linked Reports

Or, right-click the cell with the linked report in it, then click Remove Linked Value.