Free SQL Help for Devs, DBAs, and the Curious

Welcome to another edition of SQLSupport.org's blog. Today, we'll be delving into the powerful, yet often underutilized feature of SQL Server Reporting Services (SSRS), known as Bookmark Links and Jump-to functionality. In this article, we will explore how to use these features effectively in SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1, and Azure SQL to enhance your reports and users' experience.

In SSRS, bookmark links enable you to create a hyperlink to another part of the same report or to an external URL. This feature allows users to navigate through the report quickly, which significantly enhances user experience. The Jump-to functionality, on the other hand, allows users to jump to other reports or web pages. In this way, we can create drill-down or drill-through reports as per the business requirements.

Let's start with how to create a bookmark in SSRS. Suppose we have a report which displays sales data for different regions. Our aim is to allow users to click on the region name, which navigates them to the detailed sales data for that region. Here is how we can achieve this using SQL Server 2017:

SELECT Region, SUM(Sales) as TotalSales
FROM SalesData
GROUP BY Region
ORDER BY TotalSales DESC;

In this SQL query, 'Region' is the field on which we would create a bookmark. To create a bookmark, go to the textbox properties of the 'Region' field and in the 'Bookmark' section, enter an expression that evaluates to unique values (like Region in this case). Now, wherever you want to create a link to this bookmark, use the Jump to bookmark action and specify the same expression.

Now, let's discuss the Jump-to functionality. Suppose we have the same report as above, but now we want users to click on the region name, which will open another report showing detailed sales data for that region. Here is how we can achieve this using Azure SQL:

SELECT Region, SUM(Sales) as TotalSales
FROM SalesData
GROUP BY Region
ORDER BY TotalSales DESC;

In this SQL query, 'Region' is the field on which we would create a Jump-to report action. To create a Jump-to report action, go to the textbox properties of the 'Region' field and in the 'Action' section, select 'Go to report'. Specify the report and pass 'Region' as a parameter.

Bookmarks and Jump to actions in SSRS are powerful features that can greatly enhance the user experience by enabling quick navigation and drill-down capabilities in your reports. We hope this article has provided you with a solid understanding of how to use these features effectively in your SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1, and Azure SQL environments.