Drilldowns in Advanced Reports

This article explains how to setup drilldowns in dashboards and reports.

Updated over a week ago

Drilldowns 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.

Click on the below links for details:


Dashboards as Child Reports

Dashboards can be drilled down to as child reports. Dashboard Drilldowns open in the standard Dashboard Viewer in a new tab. There are a few special considerations when drilling down to a Dashboard:

When drilling down to a Dashboard, filters on the parent report will affect the Dashboard tiles differently.

Linking filters are filters created during the linking process. Interactive filters are filters created on a Dashboard, or from Report Viewer Options in the Report Designer.

Report Filters are filters created in the parent report using either the Report Wizard or the Filters menu of the Report Designer.

Tile Type

Linking Filter Behavior

Report Filter Behavior

Existing Advanced, Express or CrossTab Report

if the Filter Field is already on the report in the tile, or if it can be directly joined to the other Data Objects on the report, the filter is applied to the tile.

if the Filter Field is already on the report in the tile, the filter is applied to the tile. If not, the filter is not applied.

Existing

ExpressView

if the Filter Field is already on the report in the tile, or if it can be directly or indirectly joined to the other Data Objects on the report, the filter is applied to the tile.

Visualization

if the Filter Field is already in the visualization in the tile, or if it can be directly or indirectly joined to the other Data Objects in the visualization, the filter is applied to the tile.

In the Dashboard Viewer's Filters Pane, all filters that are a result of the linking process except for non-prompting filters from the parent report are visible.


These filters are:

  1. Filters from the Fields tab of the Linked Report Wizard

  2. Filters that are a result of the formula in the Formula Tab of the Linked Report Wizard

  3. If the parent report is on a Dashboard, interactive filters from parent and parent-of-parent (aka grandparent) reports

  4. Prompting filters from parent report

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


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.


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:

  • 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


Adding Linked Reports

  1. Add a new Dashboard, Advanced, CrossTab or Express Report that contains the drilldown data. This will become the linked child report.

  2. 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.

  3. 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.


  4. Select the desired child report. This will use default linking to drilldown to the child report.

To manually define link fields:

  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:

  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.

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

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

    3. Click Okay to close the Linked Report Wizard.


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 Report

  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. Click the Remove Link icon.

  3. Click Okay.


Next Collection: Templates - Filters


Did this answer your question?