Paginated Reports eBook Introduction, Resource and Sample Data Downloads
Reporting Services and Paginated Reports offer several capabilities that provide interactive reporting. One of these capabilities is to navigate to, or execute, a secondary report from the current, primary report. This type of report, commonly referred to as a drill-through report, is generally used to see additional details in the context of some combination of summary information. For example, imagine a sales summary report that identifies each customer and total sales for the customer. A drill-through report would enable you to see detailed transactions for a sales summary. The drill-through source report provides the appropriate context to the drill-through target report via report parameters. This recipe will show you how to conditionally pass parameter values based on a level of grouping in the source report, and then conditionally filter the detail report based on required and optional parameters.
The basics of drill-through are pretty simple. On any given element, you enable navigation from the source report to the target report, passing the appropriate report context through report parameters. However, things can get complicated when implementing drill-through reports from a multi-level grouped matrix data region. This recipe discusses the potential challenges of implementing drill-through from a multi-level matrix, and guidance about how to address these challenges.
Recipe Ingredients
Product Versions (one of the following):
- SQL Server Reporting Services 2008 R2 or newer
- Power BI Report Server
- Paginated Reports in Power BI or Power BI Desktop
What You’ll Need
- Experience creating a SQL Server data source and dataset
- Experience creating grouped and detail reports
Difficulty Level: 200
Contributed by Paul Turley
Drill-down and Drill-through Report Navigation
The examples demonstrated in this recipe require two reports: the drill-through source report, which you navigate from, and the drill-through target report, which you navigate to. Figures 1 and 2 show the source and target reports, Sales Summary and Product Sales Detail, respectively.
The Sales Summary report will have a matrix grouped by product category and product subcategory on the rows axis. The columns axis of the matrix is grouped by year and then by quarter. Both rows and columns will support drill-down navigation, meaning that you can expand a product category to view sales summarized by subcategory, and that you can expand a year to see sales summarized by quarters.
With the Sales Summary matrix expanded to the lowest level of detail (Subcategories on rows and Quarters on columns), clicking a Sales value navigates to the target report, passing four parameters and filtering the Product Sales Detail report on Category, Subcategory, Year and Quarter. In Figure 1, note the four parameter values displayed below the report title.
Figure 1 – Report navigation at the Subcategory and Quarter level
If we were to collapse the matrix to show only higher levels, how does the drill-through action work? With the Subcategory and Quarter levels in the Sales Summary report matrix collapsed in Figure 2, clicking the Sales value cell navigates to the Product Sales Detail report passing only the Category and Year. The Subcategory and Quarter parameters are blank, and the target report is not filtered on these fields.
Figure 2 –Report navigation at the Category and Year level
In the book Seven Habits of Highly Effective People, Stephen R Covey said “Begin with the end in mind” which is a pattern I often apply to solution design. We will begin by designing the target report, determine how parameters need to be passed to achieve filtering for different drill-through scenarios and then design the source report; essentially working backward.
Report parameters are used to transfer the data context from the source report. The target report will display the aggregate Sales for all products grouped by category, subcategory, year, and quarter.
Designing the Drill-Through Target Report
This section shows you how to create a simple drill-through target report. Figure 3 shows the Product Sales Detail report in the designer with the Category and Year parameters set to “Bikes” and “2013”.
Figure 3 – Product Sales Detail target report in Report Builder
Create the Dataset with Parameters
To get started, you will create the report, add a dataset query with parameters and then set the report parameters to allow null values.
Start by creating a new blank report in Report Builder.
Save the new report as Product Sales Detail.
Create a new data source named PaginatedReportData that connects to the PaginatedReportData database on your SQL Server instance.
Create a new dataset named SalesDetail using the PaginatedReportData data source created in the previous step.
Enter the following query as text into the query designer.
SELECT p.ProductCategory AS Category, p.ProductSubcategory AS Subcategory, p.ProductName AS Product, d.Year, d.Quarter, SUM(s.SalesAmount) AS Sales FROM Product p INNER JOIN Sales s ON p.ProductKey = s.ProductKey INNER JOIN Date d ON s.OrderDate = d.TheDate WHERE p.ProductCategory = @ProductCategory AND ((@ProductSubcategory IS NULL) OR (p.ProductSubcategory = @ProductSubcategory)) AND d.Year = @Year AND ((@Quarter IS NULL) OR (d.Quarter = @Quarter)) GROUP BY p.ProductCategory, p.ProductSubcategory, p.ProductName, d.Year, d.Quarter;
Take a close look at the filtering logic in the WHERE clause. The ProductSubcategory and Quarter parameters use a similar expression which cause corresponding records to be filtered only when a value is passed into the query. If nothing (e.g. Null) is passed to the ProductSubcategory parameter then we want the query to ignore that parameter and return all subcategories. The Same is true for the Quarter parameter. A null value indicates the data should not be filtered by the parameter.
Execute the query to ensure there are no errors. Enter the following values when you are prompted for the query parameters:
| Name | Value |
| @ProductCategory | Bikes |
| @ProductSubcategory | Road Bikes |
| @Year | 2013 |
| @Quarter | 1 |
Click OK to return to the report designer and then save your report.
When you run or save the dataset query, the query designer automatically detects these query parameters and creates corresponding report parameters. You need to allow null values for both the Subcategory and Quarter parameters.
From the Report Data pane on the left side of the designer, right-click the ProductSubcategory parameter and choose Parameter Properties. Figure 4 shows the Report Parameter Properties dialog.
On the General page, check the Allow null value box and click OK to save the property setting.
Figure 4 – Report Parameter Properties
On the Default Values page, add a new value and set it to (Null).
Repeat steps 8 through 10 for the Quarter parameter.
Design the Report Body
A completed version of the report is shown in Figure 5. You will design the report layout to display the report parameters and the data source fields in a table with totals shown above the details. The following steps include the essential features for this report to work. Adornments such as the font size and style, background shading, separator lines and page numbers are left for you to add as you see fit.
- If the Properties pane is not displayed to the right of the designer, enable it by checking the Properties box on the
- Drag and drop each of the parameters from the Report Data pane to the top of the report body.
- Arrange the new parameter textboxes and add blank textbox to the left of each with label text like you see in the Figure 5.
- Insert a Table data region to the body of the report, below the parameter textboxes.
- Add each field from the SalesDetail dataset to the table, placing the fields in the following order: Category, Subcategory, Product, Year, Quarter, and Sales.
- With the table selected, using the Row Groups pane below the report designer shown in Figure 5, add a Total for Sales before the detail line, so the total appears between the header and the detail line. You can either click the down arrow or right-click the (Details) item on the Row Groups list.
Figure 5 – Group Groups
Select and delete the summary cells in the Total row for the Year and Quarter fields.
Note: Because the Year and Quarter fields are numeric values, the designer will add total expressions, assuming you want to summarize these fields. You can select and delete those cells in the Total row.
Click the column header to select the Sales column.
Modify the format string of the Sales total and detail item as follows: #,###. This will enhance the readability by rounding to the nearest whole number and providing a thousand separator.
Format the report to your liking. A completed version of the report with optional formatting, in design view is shown in Figure 6.
Run the report to review the results.
Figure 6 – Completed Product Sales Detail report
Enter the following values when you are prompted for the report parameters. Notice the total represents only the values for the first quarter
| Name | Value |
| Product Category | Bikes |
| Product Subcategory | Road Bikes |
| Year | 2013 |
| Quarter | 1 |
Run the report again, using the value null for Quarter. Notice all available quarters within the specified years are now displayed. This is an important behavior for the drill-through report parameters and is explored further in the remaining exercises of this recipe.
Publish the Sales Summary Report
Publish the Sales Summary report to your report server or Power BI tenant
- From the File tab, select Save As.
- Select Recent Sites and Servers.
Select or enter the address of your report server or Power BI workspace (if you are publishing to a report server, navigate to a folder) where you wish to publish the report.
Click the Save button to publish the report.
You now have a complete drill-through target report, Product Detail, to act as a companion to the drill-through source report, Sales Summary. In the exercises that follow, you modify the Sales Summary report to implement drill-through functionality to the Product Detail report.
Designing the Drill-Through Source Report
Although the report design user interface includes data regions named Table and Matrix, the underlying report item that implements these is the Tablix data region. This exercise demonstrates the implementation of drill-through functionality from the Sales Summary to the Product Detail report that you created earlier in this recipe.
Create a Grouped Matrix Report
These steps will build the foundational report:
- Create a new blank report and save it as Sales Summary.
- Add an embedded data source for the PaginatedReportData database.
- Add a dataset named SalesDetail using the following query text:
SELECT p.ProductCategory AS Category, p.ProductSubcategory AS Subcategory, d.Year, d.Quarter, SUM(s.SalesAmount) AS Sales FROM Product p INNER JOIN Sales s ON p.ProductKey = s.ProductKey INNER JOIN Date d ON s.OrderDate = d.TheDate WHERE d.Year IN( @Year ) GROUP BY p.ProductCategory, p.ProductSubcategory, d.Year, d.Quarter;
- Execute the query and enter 2013 for the Year parameter (see Figure 7).
Figure 7 – SalesDetails query
- Click OK to save the query and return to the report designer.
- Create another dataset named SalesYearList using the same data source.
- Enter the following query and then save the SalesYearList dataset:
SELECT DISTINCT Year FROM Date ORDER BY Year
- Edit the Year parameter properties from the Report Data pane.
- On the General page, check the box labelled Allow Multiple Values.
- On the Available Values page, select the radio button option labelled Get values from a query.
- Select the SalesYearList dataset from the Dataset drop-down list.
- From the Value field and Label field drop-down lists, select the Year field as shown in Figure 8.
Figure 8 – Year parameter Available Values
- Click OK to save the parameter properties.
- Add a matrix data region to the report body using the Insert Matrix option from the Insert ribbon, Matrix drop-down button.
- Drag and drop the Category field into the cell titled “Rows”.
- Drag and drop the Subcategory field immediately to the right of the new Category cell so that a secondary row group is created.
- Drag and drop the Year into the “Column” cell to create the first column group.
- Drag the Quarter field just below the Year group cell to create a secondary column group.
- Drag and drop the Sales field to the “Data” cell.
- Select the text in the data cell (it should read [Sum(Sales)]).
- From the Home ribbon, in the Font group, click the “U” button to underline the text.
- Compare your report to Figure 9.
You should have two row groups on the Category and Subcategory fields, respectively. You should also have two columns groups, each using the Year and Quarter fields.
Figure 9 – Sales Summary in the designer
Run the report and verify that sales amounts are grouped correctly, by Category and Subcategory on rows, and by Year and Quarter on columns.
Switch back to design view for the next set of steps.
Add Drill-through Navigation
These steps will add the navigate from the Sales amount textbox in the matrix to the Product Sales Detail target report, passing the appropriate parameters:
- Right-click the Sales cell under the Quarter column heading and then click Text Box Properties from the resulting context menu. The Text Box Properties dialog box is displayed.
On the Action page, click Go to Report.
Click the Browse… button.
Browse to the location where you saved the report in the previous section, select the Product Sales Detail report, and then click the Open button.
Add the following parameter assignments. For each, click the Add button, select the parameter in the Name column and then select the corresponding field in the Value column. These selections are shown in Figure 10.
| Name | Value |
| ProductCategory | [Category] |
| ProductSubcategory | [Subcategory] |
| Year | [Year] |
| Quarter | [Quarter] |
Figure 10 – Report action
Click the OK button on the Text Box Properties dialog.
Run the report.
Hover over one of the underlined sales values. A navigation link “finger” cursor should be displayed in place of the mouse pointer.
Click a value noting the row and column group values for the cell.
The designer should navigate to the target report, displaying values for the Category, Subcategory, Year and Quarter related to the sales value you clicked.
Switch back to the Design view for the next steps.
Add Drill-down to Details in the Matrix
To “drill down” means to hide group values and allow users to expand the group to view details. Do this by hiding the lowest level row and column groups and then set a toggle item.
- In the Row Groups pane below the report designer, click the drop-down arrow on the Subcategory group, and select Group Properties…
- Select the Visibility page.
- Select the radio button to Hide the group when the report is initially run.
- Check the box labelled Display can be toggled by this report item:
- Drop-down the list and select the name of the Category group textbox. This should be the only item on the list containing the word Category and may be proceeded by a number.
- Click OK to close the Group Properties dialog.
- Repeat the same steps for the Quarter column group. Set the group to be initially hidden and to toggle display based on the Year textbox.
- Run the report and verify that sales are now grouped and aggregated by Category and Year. A plus sign icon should be displayed in the group headings which allow you to expand tot eh lower-level group values.
- Switch the Design view for the next steps.
At this point, if you were to click on a sales values on a row or column in the collapsed state, the value of the first item in the hidden group would be passed in the corresponding parameter. This is an undesirable behavior. For example, if the Year group is not expanded and you were to click on a sales value under 2012, the value of the first quarter, “1”, would be passed to the Quarter parameter – so the drill-through target report would not show a summary for all of 2012. To rectify this, we need to take advantage of the conditional query logic that we included in the Product Sales Details report dataset.
Pass Conditional Parameter Values
We need to conditionally pass empty values to the Quarter and Subcategory parameters if those groups are not expanded in the matrix.
- Like you did before, right-click the Sales cell and then click Text Box Properties from the resulting context menu. The Text Box Properties dialog box is displayed.
- Select the Action page.
Next to the ProductSubcategory parameter, click the first button labelled fx to open the Expression Builder. This button is indicated in Figure 11.
Figure 11 – Report action with expressions
Modify the expression using the following code which is shown in Figure 11:
=IIF( INSCOPE( Fields!Subcategory.Value ), Fields!Subcategory.Value, Nothing )
This Visual Basic code, shown in Figure 12, uses the IIF “Immediate If” and the InScope functions to see if the Subcategory field is in the current grouping context. If it is not in scope, the code uses the Nothing keyword to return an empty value. In the target report query, this will equate to a Null value.
Figure 12 – Expression dialog
Click the OK button to close the Expression dialog.
Next to the Quarter parameter, click the first button labelled fx to open the Expression Builder and enter the following code (the expression is similar to the previous example for the Subcategory field).
=IIF( INSCOPE( Fields!Quarter.Value ), Fields!Quarter.Value, Nothing )
Click the OK button to close the Expression dialog.
Run the report again.
Test the drill-through action on sales values using different combinations of expanded and collapsed row and column groups.
Tip: You can also use the Omit option, which is the second set of expression buttons on the Action page. Using an Omit expression will effectively ignore the parameter all together, which will cause the target report to use a default value rather than a value explicitly passed from the source report. In this scenario, the effect would be the same but this option can be useful in advanced solutions.
Final Thoughts
The technique you learned in this recipe allows you to conditionally pass parameter values only when grouped field values are available by expanding drill-down groupings. This pattern can be tailored to work in table and matrix reports with any number of grouping levels. The combination of drill-down to detail and drill-through navigation can be used to create a complete reporting experience, enabling report users to view summary values at a high-level and then focus on details to better understand their business.
Credits and Related References
The Low Down on Drill Down
Paul Turley’s SQL Server BI Blog; March 11, 2010, https://sqlserverbi.blog/2010/03/11/the-low-down-on-drill-down/
Drillthrough Reports (Report Builder and SSRS)
Microsoft Docs; March 7, 2017,