Paginated Reports eBook Introduction, Resource and Sample Data Downloads
Having the ability to navigate from one report to another allows users to see data in both summary and detail views. It helps them explore and understand information from multiple perspectives. Power BI is an ideal tool for exploring aggregated data to interpret trends, progress and insights. Paginated reports (also known as SQL Server Reporting Services) is often best suited for outputting multipage detail records in operational reports. The technique demonstrated in this recipe will allow a Power BI report user to explore data in summary and then navigate to details preserving the filter context of selected report items by passing parameters to the SSRS paginated report.
Note that I recently updated this page to include the links below. The essential technique showcased in this example can be modified to work in different use cases, including:
- Drillthrough from Power BI report to SSRS report on-prem that resides in SQL Server Report Server or Power BI Report Server
- Drillthrough from Power BI report to a Paginated Report in the Power BI Service
Recipe Ingredients
Product Versions (one of the following):
- SQL Server Reporting Services 2016 or newer
- Power BI Report Server
What You’ll Need
- Power BI Desktop (version compatible with Power BI Report Server)
- Paginated Report Builder
- A Power BI Report Server installed on-premises or on an accessible virtual machine
- Moderate level DAX coding skills
Difficulty Level: 200
Contributed by Paul Turley
The Report Navigation Solution
The solution depicted in Figure 1 shows the Sales Summary Power BI report, which is deployed to my local Power BI Report Server along with the Sales Detail, a paginated (SSRS) report also deployed to my local Power BI Report Server. To differentiate the two reports, I refer to the Power BI report that we navigate from as the “source report” and the paginated report that we navigate to as the “target report”.
Figure 1. Click to navigate from summary to details
Note: This recipe involves Power BI report design techniques used to navigate to a Paginated report. Since this book is primarily about designing Paginated reports and not designing Power BI reports, we’re not going to get into the details of fundamental Power BI report design but will cover the basics with completed report. If you are less experienced with Power BI, you can use this as an example for future report projects.
Review and Publish the Target Paginated Report
Let’s start by looking at the target report and then deploy it to the report server. This is a standard Paginated report containing parameters used to filter the results. A completed copy of this is provided for you to configure and deploy to your report server or Premium Power BI service tenant. The sample report is very simple and could easily be enhanced to include more information in greater detail.
Open the Sales Detail (Drillthrough Target).rdl report in Paginated Report Builder.
There are four report parameters in this report, two of which are used to connect to the SQL Server database. The other two parameters are used to pass filter values to the underlying query.
Edit the ServerName parameter and set the Default property to the name of your server or instance.
The default value for this parameter is LocalHost because it was connected to my local default instance of SQL Server when the report was developed. You can leave this setting if your SQL Server instance is the same as mine.
Close and save any changes to the ServerName parameter
Preview the report and make sure it connects and displays data from the sample database
Select different values for the Year and Country parameters using the parameter dropdown lists and click the View Report button to see values in the report change, and to confirm that the parameters are working correctly.
Ensure that you are signed into your local report server by selecting the Sign-in option from the File menu in Paginated Report Builder. If prompted, enter the address for your report server.
Tip: If you don’t know the address for your web portal or report server, you can use Report Server Configuration Manager to lookup the Web Service URL and Web Portal URL. By default, the web portal address is http://SERVERNAME/Reports and the report server address is http://SERVERNAME/ReportServer (where SERVERNAME is the name of the server where Reporting Services is installed) but this is configurable and might be different in your environment.
Use the Save As… option on the File menu to save the report to a folder on your report server.
Note: Depending on the Report Builder menu option and deployment target, the terms “save” and “publish” are used interchangeably, meaning that the report has been deployed to a server or service where it can be ran and viewed by users.
Get the Address for the Published Paginated Report
- Open Notepad or your favorite text editor
In a web browser, enter the address for your report portal or Power BI portal where you published the Sales Detail paginated report
Navigate to the report folder and run the newly published report. Verify that the report runs and returns data
Use Ctrl-C to copy the full address for the report from the browser address bar to the Windows clipboard
Paste the address into the text editor
The next step can seem a little tricky but it’s not complicated. You need to get the path to the report on the report server.
- In your text editor, copy and paste the report address to a new line
In the new address line, locate the text “Reports” and change it to “ReportServer”
Copy the modified address line to the address bar in your browser and press Enter
The browser should navigate to a text menu page similar to Figure 2. Of course, you will see the folders on your server.
Figure 2. Report server content list
The address for the report is not yet in the correct format but it was sufficient to navigate to the Home folder on the report server. The following steps are used to obtain the correctly formatted report address.
Get the report address
- Use the links displayed on the report server menu page to navigate to the folder where you published the Sales Detail (Drillthrough Target) report and click the report name link to run the report.
When the report is displayed, select and copy the complete address from the address bar and paste it to a new line in your text editor.
This is the correct path for this report on the report server. You can new add parameter values to the end of the address.
Copy and paste a new line in your text editor and then append the following two parameters and values to the end of the address. Each parameter is preceded with an ampersand character:
&Year=2013&Country=Australia
Copy and paste this address with the parameters to replace the contents of the address bar in your web browser.
You’ll notice that the address is quite long and that it contains extra characters in-place of things like spaces and forward slashes. These are escape codes that help the report server interpret certain characters. Don’t worry about this because you can easily copy the correctly formatted address from the address bar of the web browser. As an example, this is the address copied from my server:
Here is the complete report address with the two parameters added. This should be similar to the address used for your report:
- With the cursor at the end of the address bar, press Enter and verify that the parameter is changed and data in the report is filtered correctly.
The report should render in the browser, displaying data for 2013 in Australia. Note the Year and Country parameters are displayed correctly in the report heading.
Open the starting report in Power BI Desktop and add drill-through navigation for the paginated report
- Make a copy of the Sales Analysis PBRS (Drillthrough Source)-Starter.pbix file and rename it Sales Summary.pbix
Open Sales Summary.pbix in Power BI Desktop
Note: Make sure that you use the version of Power BI Desktop designed for your server version. If in doubt, you can download and install Power BI Desktop from the download menu in your report web portal. The application optimized for on-premises Power BI Report Server displays a black icon on the program menu and task bar, rather than the gold icon used by the Power BI Service version of Power BI Desktop.
On the Home ribbon, click the Edit Query button to open the Power Query editor
Note: Depending on the software version, the Edit Queries button may be replaced by the Transform Data button.
You will see three query parameters. Two parameters are used to manage database connection information in a similar manner to the Sales detail paginated report.
Tip: Using parameters in the Power Query editor can help manage variable information like connection information, file paths and addresses.
In the starter report file, there are three text type query parameters. Modify these parameters, using the following instructions:
| Parameter Name | Description |
| ServerName | SQL Server address or server name. You can use “LocalHost” if it is a local instance. |
| DatabaseName | PaginatedReportData |
| ReportURL | The full address for the published paginated report on your report server. Example: http://localhost/ReportServer/Paginated Report Recipes/Sales Detail |
Note: Since this book is about paginated report design rather than DAX coding and Power BI report fundamentals, I have provided a completed example rather than step-by-step instructions to build this report from scratch. As we look at the finished report design, I will provide some high-level guidance about the approach you can take when using this technique.
The completed Power BI report contains a measure in the Sales Orders table named Order Details Drillthrough Link which is used in the solution. However, we will start by looking at an earlier version of this measure to understand the design evolution.
Click the chart icon on the left to view the report page.
From the Fields list on the right, expand the Sales Orders table and select the measure named Sales Detail Report Link (version 1).
To see the entire DAX expression, use the drop-down arrow to expand the formula bar. The view should be similar to Figure 3.
Figure 3. Sales Detail Report Link (version 1) measure
The expression is simple but can be a little challenging to read because the web address is long and the text wraps. To create this, I pasted the base address for the report between double quotes. The two parameter names, Year and Country are added as literal strings and then the selected values for the Year field in the Date table and the Country field from the Sales Territory table are concatenated into the text. The resulting DAX code for this measure looks like this:
Sales Detail Report Link (version 1) = “http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fPaginated+Report+Recipes%2fSales+Detail (Drillthrough Target)&rs:Command=Render&Year=”
& FORMAT( SELECTEDVALUE( ‘Date'[Year] ), “0000” )
& “&Country=” & SELECTEDVALUE( ‘Sales Territory'[Country] )
The DAX SELECTEDVALUE() function returns one value only when a single value is in scope for the expression. When this measure is used in a context where there is only one Year, the value for that year is returned but if the report visual isn’t grouped or filtered by the Year field, the expression SELECTEDVALUE( ‘Date'[Year] ) will return a blank value and the concatenated report address will be invalid.
From the Fields list on the right, expand the Sales Orders table and select the measure named Sales Detail Report Link.
Here we are taking the earlier version of the measure to the next level. This code uses three variables to resolve interim values, making the code easier to read and to manage. Here is the code for the measure formatted so it is easy to read:
Sales Detail Report Link =
VAR vReportAddress
= MIN( ‘ReportAddress'[ReportAddress] )
VAR vYear
= FORMAT( SELECTEDVALUE( ‘Date'[Year] ), “0000” )
VAR vCountry
= SELECTEDVALUE( ‘Sales Territory'[Country] )
RETURN
IF( HASONEVALUE( ‘Date'[Year] )
&& HASONEVALUE( ‘Sales Territory'[Country] ),
vReportAddress & “&Year=” & vYear
& “&Country=” & vCountry,
BLANK()
)
One more step is necessary to use the text returned by this measure as a web link. In a Power BI data model, fields and measure can have associated metadata that changes how a report responds to values. The Data Category property of a measure serves this purpose.
With the measure selected in the Field list, switch to the Modeling ribbon (this might be titled “Measure Tools” depending on your version of Power BI Desktop.)
The measure properties in Figure 4 shows the Data Category for the measure. Drop this list down to see the options and then select Web URL.
Figure 4. Sales Detail Report Link measure – completed
Set the ReportAddress Parameter
The ReportAddress parameter is designed to pass this value to a measure so it can easily be used in expressions.
- On the Home ribbon, click Edit Parameters.
If needed, paste the correct address for your published report into the ReportAddress parameter value and then click OK (see Figure 5.)
Figure 5
By default, adding a Web URL categorized measure to a table or matrix visual will display the entire text of the address as a link, which isn’t ideal for this report. Navigation links in a matrix visual can be visualized as a simple link icon by setting the Web icon property.
Select the matrix visual on the report page.
In the Visualizations pane, use the Fields icon (highlighted in Figure 6) to view the fields used in the matrix.
Hover over the “Details” field in the Values field well.
Figure 6. Measure added to matrix values
When designing this report, after adding the Sales Detail Report Link measure, I renamed the field in the visual to “Details” to shorten the column header. This is evident in the tooltip when hovering over the field in the field well.
In the Visualizations pane, view the properties for the matrix (under the paint brush icon.)
Expand the Row headers property group.
Scroll down to the URL icon property and switch this property On (Figure 7.)
Figure 7. URL icon property of the matrix row headers
Test Report Navigation
You can test the report navigation link right here in the designer. It should work just as it would after the Power BI report were published to the report server.
- Expand a year in the matrix visual to see the sales for each country in that year.
Click the link icon corresponding to a year and country to navigate to the Sales Detail (Drillthrough Target) paginated report
Verify that the correct report parameters were set and that the report is displaying the right information.
Publish the Power BI Source Report
The last step is to publish the Power BI report to your report server. Publishing a Power BI report to the Paginated Report Server from Power BI Desktop is similar to publishing the target report from Paginated Report Builder.
- On the File ribbon, choose Save as and then choose Power BI Report Server (see Figure 8).
Figure 8. The “Save as” menu option in Power BI Desktop
The Power BI Report Server Selection dialog is displayed, as you see in Figure 9.
Either select your previously connected server or enter the address for your Paginated Report Server web portal.
Figure 9. Server Selection dialog
Tip: You can use the Report Server Configuration Manager to find the Web Portal address for your Paginated Report Server the Web Portal URL page.
After the Power BI report is published, use your web browser to navigate to web portal for your Paginated Report Server.
Figure 10 shows both reports published to my local report server.
Figure 10. Reports in Power BI Report Server Web Portal
Click to open the Sales Analysis PBRS (Drillthrough Source) Power BI report.
Expand the matrix visual and click a link icon to test the drill-through like you did in Power BI Desktop.
Figure 11 shows the target report after clicking the link for Australia in 2013.
Figure 11. Sales Details target report after drill-through navigation
If the report navigation works as expected, do a little “happy dance” to celebrate the momentous occasion.
Advanced Techniques
The parameterization technique demonstrated in this recipe is a foundation you can use to build more complex solutions. You can add more parameters by applying the same pattern of “&ParameterName=Value” concatenated to the end of the report address URL. Multiple values can be passed to a parameter by separating values with a comma using DAX functions like CONCATENATEX(). See the Microsoft documentation referenced below for more options related to passing report parameters in a URL string.
Final Thoughts
Report navigation is the essence of data exploration and gives users the ability to understand their data in proper context. Dashboard and summary reports help business users see the big picture but having access to details lets them understand the low-level details behind the numbers. Power BI as an ideal tool for summary-level, interactive reporting and paginated reports are great for consuming detail-level data. Using this technique allows you to have both of these capabilities in one solution. Power BI reports can be published to your on-premises Power BI Report Server or to the cloud Power BI service. Paginated reports do not have to be in the same location and can be deployed on-prem in a standard SSRS report server, Power BI Report Server or in you Premium Capacity Power BI Service tenant.
Be mindful that there are practical limits to the amount of information you can pass in a URL string which can limit the number of parameters you can pass and the number of values for multivalued parameters. At this time, this technique is one of few methods for passing context from a Power BI report to a paginated report but sometime in the future, we may see tighter integration between these report types built into the platform. You should avoid passing sensitive information in clear text parameters. When navigating between reports located on different services or services, users may be prompted for their credentials to login to a service.
Credits and Related References
Reporting Services URL access parameter reference (Microsoft documentation):
Pass a Report Parameter Within a URL (Microsoft documentation): https://docs.microsoft.com/en-us/sql/reporting-services/pass-a-report-parameter-within-a-url?view=sql-server-ver15
Drillthrough from Power BI to an SSRS Paginated Report (Paul Turley, SQL Server BI Blog): https://sqlserverbi.blog/2019/07/04/drillthrough-from-power-bi-to-an-ssrs-paginated-report/