Report navigation is a little like time travel…it works in one direction: going forward. If you have created multiple drill-through reports, having the ability to navigate backward to a previous report would be helpful, especially if there are multiple reports in the drill-through sequence. The report toolbar contains navigation controls, including a Back button for the previous report in a chain of linked reports but some users might not find this intuitive. A common user-experience paradigm in many well-designed web applications places a series of hyperlinks at the top of a page, indicating both the path that brought the user to the page they’re viewing and a path for navigating back to any page along that path. This is a breadcrumb trail of hyperlinks. Reporting Services provides a means to navigate to any published report but does not have a native mechanism to track report navigation. But, with a little creativity we can build one. This technique shows you how to build a series of breadcrumb hyperlinks that allow your users to see where they have been and to navigate back to any report along the trail. This technique will work with Paginated Report in the Power BI service, Power BI Report Server or SQL Server Reporting Services.

Recipe Ingredients

Product Versions (one of the following):

• SQL Server Reporting Services 2016 or newer
• Power BI Report Server
• Paginated Reports in Power BI or Power BI Desktop

What You’ll Need

• A series of reports with report navigation actions
• A report parameter used to track the breadcrumb trail
• A custom code function to build an ad-hoc query
• A dataset used to provide breadcrumb data
• A matrix data region containing textbox report actions

Difficulty Level: 300

Contributed by Paul Turley

This recipe contains a lot of detail, so I recommend that you use the completed reports from the book download samples as a reference point to check and validate your own design. Figure 1 shows an example of a breadcrumb trail in the report header of a detail report. Note the progressive drill-through path of reports the user opens en route to the current report.

Figure 1 – Sample report with breadcrumb links

In the production implementation of this technique, report actions can be implemented using any of the compatible report items, including textboxes, images, a grouped table, a matrix, a list, or chart data points as the report action source object. The purpose of this recipe is to demonstrate the breadcrumb technique, not the complexities of embedded report actions. As such, you will be using a simple textbox on the report body to navigate between sample reports. These reports will not have any data sources or datasets other than the one required for this technique.

Designing the Report

In the end, you will have four almost identical reports designed to demonstrate the technique. You will start by designing one report with the base functionality for all reports and then create three copies of this report, rename them, and make the necessary modifications.

Create a new report named Breadcrumbs Report 1.

Drag and drop the Report Name from the Built-in Fields to create a title textbox and style the report header as you typically would with a large, bold font, horizontal line, and so on.

In the report body, about an inch or three centimeters below the line, add a textbox to use as the report action source object. The user will click this text to navigate to the next report. Add some appropriate text to this textbox to indicate that it is a link to the second report. You can see this Figure 2.

Figure 2 – First report in design view

In a production report, this textbox would be replaced with other report items or data regions with appropriate report actions.

A report parameter is used to pass the report navigation history to the target report. With each new report in the navigation chain, the name of that target report will be added to a comma-separated string passed in the parameter. This parameter will only be used internally and not shown to the user.

Add a report parameter named CallingReports.

Set the parameter to accept a null value and set the visibility to Hidden (see Figure 3).

Figure 3 – CallingReports parameter

A dataset query will be used to drive a matrix data region to contain the report links. This query will be built using an expression and custom code function. Before you put this part into place, you must create a static query to generate the metadata for the dataset. This dataset will not actually consume any real data but must have a valid data source. Of course, a data source must have a valid connection string to satisfy this requirement.

Create a new dataset and name it LinksQuery.

Note: Any valid shared or embedded data source may be used for this dataset because data will not actually be read from a database.

Create or select a data source and select any local or remote server and any valid database. The localhost server and the Master database is used in this example.

For the query, type:

SELECT Null As Link

Figure 4 shows the Dataset Properties dialog with the query statement. This is only a temporary placeholder to generate a field object named Link. The actual query string will be generated in a custom code function and expression.

Figure 4 – LinksQuery dataset

A matrix will be used to display each of the report links, each separated by a right angle bracket: >. Only the column header cells will be used in the matrix.

Add a matrix data region to the header area of the report body.

Delete the Row Group and the related rows and columns.

Drag the Link field to the Column Groups list to create a new group and group header.

Add a new column to the right, inside the group.

Right-click in the header cell and choose Split Cells.

Right-click in the header cell for the new column on the right and add the following expression:

=IIF(Fields!Link.Value = Last(Fields!Link.Value, "LinksQuery"), "", ">")

Choose both of these cells and set the Font Color to Blue.

Set the text in the first cell to be underscored to make it look like a hyperlink. Compare the matrix shown in Figure 5 to validate your design.

Figure 5 – Matrix with link

The Link field cell will be used to display the name of the source report and to provide a link back to it in each column instance of this cell in the matrix.

Right-click the cell for the Link field and choose Text Box Properties. On the Text Box Properties dialog, move to the Actions page.

Create a report action by choosing the radio button Go to report.

For the destination report, click the Expression button (fx) and then create an expression to reference the Link field.

Click OK to close and save the expression in the Expression dialog.

Click the Add button to add a new target report parameter. If this report has previously been created, this parameter would be available for selection.

Type CallingReports in the Name column.

Click the Expression button (fx) next to the Value column and type the following (as one line) into the Expression window:

=IIF(IsNothing(Parameters!CallingReports.Value), Globals!ReportName,
Parameters!CallingReports.Value & ", " & Globals!ReportName)

Close and save the expression in the Expression dialog and then use Figure 6 to verify these settings.

Figure 6 – Matrix report action

Select the entire matrix and remove all borders using the toolbar or ribbon.

Resize the second row containing the data cells to reclaim this unused vertical space. Reduce the height of this row to eliminate unnecessary white space.

Select the textbox you added back in Step 3.

This textbox will be used to navigate to another report. The following steps are identical to the previous steps for the Link cell except for the destination report.

Right-click the textbox and choose Text Box Properties.

On the Actions tab, create a report action by choosing the radio button Go to report.

The destination report doesn’t exist yet so you will just type this value into the drop-down list. Type Breadcrumbs Report 2 into the Specify a report property.

Click the Add button to add a new target report parameter. If this report has previously been created, this parameter would be available for selection.

Type CallingReports in the Name column.

Click the Expression button (fx) next to the Value column and type the following (as one line) into the Expression window:

=IIF(IsNothing(Parameters!CallingReports.Value), Globals!ReportName,
Parameters!CallingReports.Value & ", " & Globals!ReportName)

Close and save the Expression dialog and use Figure 7 to verify these settings.

Figure 7 – Textbox report action

Click OK to save these settings and close the Text Box Properties dialog.

Edit the report properties and add the following code to the Code window:

Function ListToLinksSQL(List As String) As String
Dim sTargetReport() As String
Dim sReport As String
Dim sOut As String
sTargetReport = Split(List, ", ")
For Each sReport In sTargetReport
If sOut <> "" Then sOut &= " UNION "
sOut &= "SELECT '" & sReport & "' AS Link"
Next
Return sOut
End Function

Edit the dataset properties.

Replace the query command text with the following expression:

=Code.ListToLinksSQL(Parameters!CallingReports.Value)

Save and close the report.

Create three copies of the Breadcrumbs Report 1, named:

• Breadcrumbs Report 2
• Breadcrumbs Report 3
• Breadcrumbs Report 4

Tip: If you are using SSDT, the easiest way to add copies of a report to the project is to select the first report in the Solution Explorer and then use Ctrl-C and Ctrl-V to copy and paste a new file, then rename it. In Report Builder, use the Save As feature to save copies of the file to the same folder as the original.

Modify each report so that the Link field report action targets the next report in the sequence and the last report navigates back to the first.

The Breadcrumbs Report 2 report action should navigate to Breadcrumbs Report 3. Breadcrumbs Report 3 should navigate to Breadcrumbs Report 4, and Breadcrumbs Report 4 should navigate back to Breadcrumbs Report 1.

To test, preview the first report and click the link to navigate to the second report. Continue to follow the links on each subsequent report and note the accumulated breadcrumb links in the report header. Click any link to navigate to any of the reports in the series, as illustrated in Figure 8.

Figure 8 – Report navigation series

Final Thoughts

The breadcrumb link list is a common navigation paradigm and users will intuitively know how to use it based on their experience with websites and other applications. This is a tricky technique since Reporting Services doesn’t provide a built-in mechanism for tracking navigation history but once the code and expressions have been created, you can use this pattern in any reports that have a series of report navigations.