Paginated SSRS Reports with Drill-through Navigation
By Tom Nonmacher
In this blog post, we will explore the creation of paginated SQL Server Reporting Services (SSRS) reports with drill-through navigation. We will be focusing on SQL Server 2012 and 2014, MySQL 5.6, DB2 10.5, and Azure SQL. Paginated reports are optimized for a report layout that extends across multiple pages. They are a perfect solution when you must display large amounts of data grouped or broken down in various ways, and they also provide a detailed, organized document that is easy to understand. Drill-through navigation, on the other hand, allows the user to click on a part of the report to see additional details.
Firstly, let's create a basic paginated report. For example, in SQL Server 2012 or 2014, you would use T-SQL to select the data you want to display:
-- T-SQL code for SQL Server
SELECT *
FROM Orders
WHERE OrderDate BETWEEN '2016-01-01' AND '2016-12-31';
Similarly, for MySQL 5.6, you would use the following code:
-- MySQL code
SELECT *
FROM Orders
WHERE OrderDate BETWEEN '2016-01-01' AND '2016-12-31';
And for DB2 10.5, the code would be:
-- DB2 code
SELECT *
FROM Orders
WHERE OrderDate BETWEEN '2016-01-01' AND '2016-12-31';
After you have retrieved the data, you can create a report in SSRS. To enable drill-through navigation, you can create a separate report with the detailed data and then link the main report to this detailed report. For example, you could create a hyperlink on each order ID in the main report; when a user clicks on an order ID, the detailed report for that order will open.
A noteworthy point here is to ensure that the detailed report uses a parameter to filter the data. For instance, the order ID can be used as a parameter. Here's an example of how you would filter the data in the detailed report using T-SQL:
-- T-SQL code for SQL Server
SELECT *
FROM OrderDetails
WHERE OrderID = @OrderID;
In conclusion, paginated SSRS reports with drill-through navigation are a powerful tool for displaying large amounts of data in a user-friendly way. They allow users to view a summary of the data and then drill down to the details when necessary. This functionality can be implemented using various database systems, including SQL Server 2012 and 2014, MySQL 5.6, DB2 10.5, and Azure SQL.