I am happy to join several world-class speakers on July 23 & 24 for the Power Platform Virtual Conference. I will be speaking about how to create “no-code” Power BI custom visuals along with Mike Carlo from PowerBI.tips.
Join me on Tuesday, July 23 at 5 p.m. EDT for my session on
Creating Power BI Custom Visuals with No Code at the Power Platform Virtual
Conference. This 2-day online conference dedicated to the #PowerPlatform is
only $49. Register today! #PowerPlatformCon #PowerBI #PowerApps #MicrosoftFlow http://powerplatformconference.com/
Looking to enhance your Power BI, PowerApps and Flow skills but can’t attend an in-person conference? Check out this virtual (on-line, live) conference featuring several headlining, international conference presenters. Join Pragmatic Works for the only 100% virtual conference dedicated to the #PowerPlatform on July 23rd and 24th! http://powerplatformconference.com/ #powerplatformcon #PowerBI #PowerApps #MicrosoftFlow
Report navigation is, in my opinion, the essence of true data exploration and discovery. It lets business users see and understand important information in different forms; in summary and in detail, within context. SSRS and Power BI are truly a dynamic duo for reporting and data discovery. I wanted to post a follow up to these two articles because they are apparently very popular topics. Posted two days apart on my blog, they received 2,744 and 3,356 views. Woo!
Nine years ago we released the Reporting Services Recipes Book from Wrox Press, which was a very popular collection of design patterns and techniques, often referenced frequently by Microsoft leaders at industry conferences. In the book, we showcased different ways to move between SSRS reports by passing parameters and dynamic URLs to create interactive reporting solutions. I am working with my former co-author and a team of talented BI and report design experts on the second edition of the the Recipe book that will include not only updated examples of SSRS patterns for on-prem report solutions but we are including new patterns for Power BI Report Server and Paginated reports in the Power BI service.
Today we have Power BI in addition to SSRS (now called “Paginated Reports”). These tools are different and often serve distinctly different purposes for creating reporting solutions. But, using Power BI and SSRS/Paginated Reports together opens doors to create incredibly rich integrated report and data discovery solutions.
I recently published two blog posts to share some of my work-in-progress for the recipe book: Drillthrough from Power BI to an SSRS Paginated Report, and: Drillthrough from Paginated Report to Power BI. Both of these posts demonstrate navigation from one of the report tools to the other, essentially allowing users to work with these two capable tools in a full circle. As the newer and more modern data analysis tool, Power BI fills and important gap but it is not a replacement for the type of multi-page grouped and repeated style of reporting that SSRS is optimized for. Likewise, Power BI excels as an interactive tool for data discovery and self-service analysis. SSRS and Power BI were borne from the same platform and and have common architectures but also many differences. Used together, the realm of possibilities is expansive.
It is important to understand that the techniques used to navigate and pass filter context between these report tools is limited to the capabilities of web query string parameterization. In my experience, this is rarely a show-stopper but I do occasionally encounter folks pushing the limits – both practically and technically.
Both SSRS and Power BI have their own drillthrough capabilities using internal mechanisms that do not rely on the web browser and report server to handle web URLs. However, moving between different report types relies and web URLS. Although powerful and flexible, there are some limitations. Each web browser has it’s own maximum URL length limit, ranging from 2048 to 2083 or so characters. The server-hosted web services also have their own limits. For simplicity, let’s call the limit about 2000 characters, which will include the base domain and folder path URL, and the query string directives, parameters and values. Keep all this within the ~2000 character limit and you’re good. Also be mindful that all this is visible in the browser’s address bar. Requests and responses sent to the Power BI service are encrypted so there is little risk of exposure outside the session. Your on-prem report server connections can also be protected using certificate-based encryption. Just be mindful about what you pass in sight of the user and web browser after the stream is decrypted on the client.
The report navigation recipes posted here on my blog are early drafts. I welcome your feedback, questions and ideas for more report recipes. Sample files and other downloadable content that are referenced in the recipes will be available when the book is published – and no, that date has not been set, but I will make an announcement when we know. Thank you in advanced for your questions and comments.
In the recipe titled “Drillthrough from Power BI to Paginated Report” I demonstrate how to use report parameters and a dynamic URL address to navigate between reports and pass parameter values in the URL from a Power BI report to detailed data in an SSRS paginated report. Using a similar technique, we can navigate from a paginated report to a Power BI report.
Power BI is very flexible and does not require you to define parameters like you would in a paginated report. You can literally filter a report using any field in the dataset.
SSRS/Paginated Reports (one of the following):
· SQL Server Reporting Services 2016 or newer
· Power BI Report Server
· Paginated Reports in the Power BI service
Power BI Desktop:
· Power BI Desktop May 2019 or newer
What You’ll Need
· A Power BI report connected to a data source using import, DirectQuery or SSAS direct connect mode.
· Paginated Report server: SQL Server Reporting Services (Standard or Enterprise Edition), Power BI Report Server or a Power BI subscription with Premium capacity
· Moderate level Visual Basic.NET coding skills
Difficulty level: 300
The sample database and files will be available in the forthcoming book: SQL Server Reporting Services Paginated Report Recipes, 2nd Edition (working title). These instructions are provided as an example but refer to files that will be available when the book is published. Please contact me in the comments with questions and feedback.
Preparing the target Power BI Report
You don’t have to do anything special in the Power BI report design to enable parameterized filtering; you simply need to know the tables and field names you will need to use for filtering. Most of the effort is building the dynamic URL in the paginated report.
The first order of business is to make note of each field or measure to use for filtering. I suggest jotting them down in a text editor using the notation: TableName/FieldName.
Note: Filtering is applied at the report level, so navigating to a report using URL filters will affect all the pages in that report.
In the following examples, I will demonstrate my experience with a report deployed to my Power BI tenant. In the exercises a little later, you will deploy a sample report and work through these steps in your own Power BI environment.
Addressing Power BI Objects
There are several URL-addressable objects in the Power BI service to which you can navigate using a URL. Each of these objects is assigned a globally unique identifier that can be addressed in a URL. Reports can be filtered, and dashboards cannot, but other objects that are related to reports can be filtered and will respond to a filter directive in the URL. For each object type listed in the following table, navigate to the object in your web browser and copy the URL from the address bar.
Dashboards don’t accept filters
Shows default report page with report-level filters
Applies report-level filters to page
Bookmark and filter directives can be combined in the URL query string using “&” (rather than “?”) to append directives
Default page in app navigation must be a report rather than a dashboard
Workspace app report page
Same as a report. Use path to app or a report within the app
Workspace app bookmark
Same as report bookmarks. Filter directive may be added to the query string using “&” rather than “?”.
Filter Directives & Syntax
The URL query string filtering syntax for Power BI is borrowed from the OData standard which offers a great deal of flexibility. For example, depending on data types; you can specify that a field is equal to, less then, greater than, contains, in a range, or includes a list of parameter values. The filter directive is simply tacked onto the end of the URL specifying an object path in the form of:
?filter=Table/Field eq value
Text and date type values are passed in single quotes:
?filter=Table/Field eq ‘value‘
Multiple filters may be appended together using the “and” operator. This example uses real table and field names:
?filter=Product/Brand eq ‘Contoso’ and Sales/Quantity lt 5
This will filter all pages on the report to include only records where the Brand field in the Product table is “Contoso” and the Quantity field in the Sales table is less than 5.
All of the available filter operators are listed in the following table.:
not equal to
greater than or equal to
less than or equal to
in list (item1, item2, item3)
Before I can add the filter directive, I need to get the report URL. If I go my Power BI service, navigate to a workspace and then click to open a report, I can capture the URL from the address bar. As an example, here’s the address I captured for a report published in my Power BI tenant:
You can see that each object type (“groups”, “reports”, etc.) is proceeded by a globally unique identifier; which is a long string of characters generated by the service. These identifiers don’t change after the object is deployed or created in the service.
Notice that the ReportSection element at the end of the address has no value which means that I have not specified a report page. If I were to use this address to navigate to the report, Power BI will show me the default page, which is the page that was visible when the report was last saved. If I navigate to another page and then capture the URL, the object ID of that page is appended to the “ReportSection” text at the end of the address:
Note: The syntax for the page or ReportSection is a little different than other objects and does not use a forward slash before the object ID.
The common terms we use for some of these objects are a little different that the attribute names used in the URL query string, as noted here. I will show you some additional object names after this first example.
Query String Attribute
Now, to add the filter directive to the report URL… When I append the filter directive from the earlier example to the report URL like this…
This is a necessary practice when passing certain information in a URL called “escaping”. There are certain characters that either have special meaning to a web server or simply can’t be passed in a web URL without special encoding. Spaces and forward slashes are the most common examples that must be converted to a special string of characters so that a web server (in this case the Power BI service) understands what a web client (in this case, my web browser) is asking for in the web address and query string directives. Technicalities aside, we must substitute these characters with special “escape character” strings. Cryptic as it might seem, this is simply a requirement.
Report URL on Power BI Report Server
If you are using Power BI Report Server rather than the Power BI service, you can get the report URL in the same way. Simply navigate to the report and capture the URL in the address bar. You will not see object IDs like those generated by the Power BI service but just the named folder and report. For example, here is the path to a report named Sales Analysis in a folder named Report Recipes on my local report server:
The pattern for adding filters to a report published to an on-premises report server is the same as the Power BI service. Append the filter directive query string parameter to the URL using the same syntax (beginning with “?filter=”).
Handling Special Characters
If any object names that contain literal spaces or other special-purpose characters (like % or @ for example), you must explicitly replace these with properly escaped notation. In the previous example, you saw that the web browser replaces forward slashes with “~2F” and spaces with “%20” within the URL.
This is where things might get a little confusing but it’s really not complicated. The HTTP escape character encoding that your web browser does automatically uses a different syntax than the Power BI service or Power BI Report Server. For object names (tables or fields) in the filter directive, Power BI employs a different syntax called Unicode URL escape codes, which is a seven-character string beginning with “_x” and ending with “_” and a four-character hexadecimal number in the middle. To represent a space, the code is “_x0020_”. Use the following table for reference:
HTTP Escape Code
Unicode URL Escape Code
Let’s say that I need to specify a filter for a field named Product Category in a table named Sales Orders,and I only want to see data for two specific categories, Accessories and Bikes. Before adding escape codes, the filter string would look like this:
?filter=Product/Product Category in (‘Accessories’,’Bikes’)
If I replace the necessary characters (within object names) with Unicode URL escape codes, the filter string becomes:
?filter=Product~2FProduct_x0020_Category in (‘Accessories’,’Bikes’)
Additionally, my web browser automatically adds HTTP escape codes for spaces the quotation characters outside of object names, which produces this string:
In summary, you do not need to apply the HTTP escape codes because your browser will do that for you. If you capture the URL with the escaped characters, that’s fine. However, you must replace spaces and special characters with the filter directive object names with the appropriate Unicode escape codes.
Now that you understand the syntax of the filter directive and the need for escape codes to replace certain characters and spaces, you are equipped to build a fully-qualified URL for the report and folder.
Build the Report Navigation Solution
In this solution, you will make enhancements to a fairly standard SSRS paginated report. You will use a custom Visual Basic function and expression to encode a dynamic report URL with parameters to filter a Power BI report.
Design the Source Paginated Report
A starting report is provided for you. This is a simple SSRS paginated report connected the SSRS2019Recipes sample database.
1. Make a copy of the Sales Summary-Starter.rdl file and rename it Sales Summary.rdl.
2. Open Sales Summary.rdl in Report Builder (shown in Figure 1).
3. Open the data source and make sure you can connect to the SSRS2019Recipes database on you SQL Server. Change the server name in the connection of necessary and test the connection.
4. Leave Report Builder open to resumereport design work after the following steps.
Open and Publish the Power BI Target Report
1. Open Power BI Desktop and then open the Sales Analysis (Drillthrough Target).pbix report file.
2. Publish the Sales Analysis (Drillthrough Target) report to a workspace in your Power BI tenant or to a folder in you Power BI Report Server.
Note: If you are using Power BI Report Server rather than the Power BI Service, you must use the version of Power BI Desktop that installs with Power BI Report Server. To publish a report in Power BI Desktop for report server, choose Save As… from the File menu. To publish a report to the Power BI service, choose Publish from the File menu.
3. Navigate to the published Sales Analysis (Drillthrough Target) report in your web browser, shown in Figure 2.
4. Select the entire URL in the address bar and copy it to the Windows clipboard.
Continue Designing the Source Paginated Report
Now that you have the complete URL for the published Sales Analysis Power BI report in the clipboard, you can return to Report Builder where you were previously editing the Sales Summary SSRS paginated report.
1. In Report Builder, select the report parameter named PBIReportURL.
2. Right-click the selected parameter and choose Edit Parameter from the menu.
1. Select the Default Values page.
2. Click to select the Specify values radio button.
3. Click the Add button.
4. Paste the URL for the Power BI report into the Value box as shown in Figure 3.
5. Click OK to accept the parameter changes.
Create a Custom Function to Encode Object Name Text
1. In Report Builder, right-click the report designer background outside of the report body (dark gray area) and select Report Properties from the menu.
2. In the Report Properties dialog. Select the Code page.
3. In the Code box, enter the following Visual Basic.NET code.
Note: The custom code editor has no syntax validation or debugging features, so it can be challenging to debug and correct errors in your code. I recommend that you challenge yourself by hand-entering this code but if you have any trouble, a copy of this code is provided in the Code.txt file for your convenience.
Function EncodeObjectNames ( ObjectNames as String ) As String
Dim sText As String = ObjectNames
‘– Replace Spaces —
sText = REPLACE( sText, ” “, “_x0020_”)
‘– Replace $ —
sText = REPLACE( sText, “$”, “_x0024_”)
‘– Replace & —
sText = REPLACE( sText, “&”, “_x0026_”)
‘– Replace % —
sText = REPLACE( sText, “%”, “_x0025_”)
‘– Replace ‘ —
sText = REPLACE( sText, “‘”, “_x0027_”)
The completed function is shown in Figure X
4. Click OK to accept the code changes and close the Report Properties dialog.
Add Link Icon
1. Select the Insert tab in the main Report Builder application.
2. From the Insert ribbon, click the Image button.
The mouse pointer changes to an Image icon.
3. In the table, click the right-most cell in the Detail column to drop the new image.
The Image Properties dialog opens.
4. On the Image Properties dialog, click the Import button.
5. Drop down the file extension filter list which is currently set to JPEG files (*.jpg).
6. Select the option to show all file types: All files (*.*).
7. Browse to the correct folder and select the Link Icon.png file.
Add Navigation Action
1. Select the Action page in the Image Properties dialog.
2. Under Enable as an action:, click to select the radio button labelled Go to URL.
3. Click the “fx” button to the right of the Select URL: drop-down list.
4. In the Expression dialog, enter the following code.
Note: Be particularly mindful of the literal single quotes surrounding the ProductSubcatory field reference at the end of the expression.
Navigating from a Power BI report to an SSRS Paginated report with filters and parameters.
Product Versions SSRS/Paginated Reports (one of the following): – SQL Server Reporting Services 2016 or newer – Power BI Report Server – Paginated Reports in the Power BI service Power BI Desktop: – Power BI Desktop May 2019 or newer All versions of Reporting Services accept parameters in a similar fashion, but subtle changes were made to URL parameter formats in SSRS 2016. Some adjustments may be required if you want to apply these techniques to SSRS versions prior to 2016.
What You’ll Need – A Power BI report connected to a data source using import, DirectQuery or SSAS direct connect mode. Report server: – Power BI Report Server or a Power BI subscription with Premium capacity (minimum: P1 or A4) – Moderate level DAX coding skills
Difficulty level: 200
Designing the Power BI Report
This recipe primarily involves Power BI report design techniques. I’m not going to get into the details of Power BI report design but will cover the basics with a partially-completed report to get you started. If you are less-experienced with Power BI you can use this as an example for future report projects.
The sample database and files will be available in the forthcoming book: SQL Server Reporting Services Paginated Report Recipes, 2nd Edition (working title).
These instructions are provided as an example but refer to files that will be available when the book is published. Please contact me in the comments with questions and feedback.
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.
We will start by looking at the target report. This is just a standard SSRS paginated report. 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.
1. Open the Sales Detail.rdl report in 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.
2. Edit the ServerName parameter and set the Default property to the name of your server or instance
The default value for this parameter LocalHost which connected to my local default instance of SQL Server. You can leave this setting if your SQL Server instance is the same as mine.
3. Close and save any changes the ServerName parameter
4. Preview the report and make sure it connects and displays data from the sample database
5. 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.
6. Use the Save As… option on the File menu to save the report to a folder on your report server or a workspace in your Premium Power BI tenant
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
1. Open Notepad or your favorite text editor
2. In a web browser, enter the address for your report portal or Power BI portal where you published the Sales Detail paginated report
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.
3. Navigate to the folder or workspace and run the newly published report, verify that the report runs and returns data
4. Use Ctrl-C to copy the full address for the report from the browser address bar to the Windows clipboard
5. Paste the address into the text editor
The next step can seem a little tricky but it’s not really complicated. You need to get the path to the report on the report server rather than the report portal.
If you published the paginated report to your on-premises report server, do the following:
1. In your text editor, copy and paste the report address to a new line
2. In the new address line, locate the text “Reports” and change it to “ReportServer”
3. 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.
The address for the report is actually 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.
1. Use the links displayed on the report server menu page to navigate to the folder to which you published the Sales Detail report and click the report name link to run the report
2. When the report is displayed, 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.
3. Append parameters and values to the address in the address bar like this. Each parameter is preceded with an ampersand character:
4. Press Enter and verify that the parameter is changed in the report
Open the starting report in Power BI Desktop and add drillthrough navigation for the paginated report
1. Make a copy of the Sales Summary-Starter.pbix file and rename it Sales Summary.pbix
2. Open Sales Summary.pbix in Power BI Desktop
If you will be using Power BI Report Server to deploy report on premises, make sure that you use the version of Power BI Desktop designed for your server version. If in doubt, you can download and install Desktop from the download menu in your report web portal.
3. On the Home ribbon, click the Edit Query button to open the Power Query editor
4. 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:
SQL Server address or server name. You can use “LocalHost” if it is a local instance.
Path to the deployed SSRS/paginated report
5. If necessary, change the ServerName parameter current value to connect to your SQL Server
6. Copy the report server address for the deployed Sales Detail report from your text editor and past it into the current value ReportURL parameter.
Build the dynamic target report address link as a measure in Power BI
The ReportURL Parameter value is exposed as a column in a table named Constants. You will use this to build a dynamic link in the report.
1. On the Home ribbon in the Power Query editor, click Close & Apply to process queries and return to the report designer.
2. Figure 3 shows the report designer in Power BI Desktop. The objective is to add links to each item displayed in the table visual, allowing a user to see a paginated report with details related to a selected year and country.
3. Expand the Sales Summary Subcategory Country table in the field list on the right
4. Click the ellipsis (…) to the right of table name to see the context menu and select New Measure
5. The formula bar is displayed above the report canvas with placeholder text: Measure =
Note: You can use the down arrow on the right side of the formula bar to expand the formula editor window, and the up arrow in the same location to collapse the formula editor
6. Replace the placeholder text with the following new measure:
The purpose of this measure is to build the address text used for our link to the detail paginated report. The SelectedYear and SelectedCountry variables get those respective values. The RptURL variable gets the report path the parameter. This DAX script assembles the URL in the appropriate format.
As you enter the text, use the Intellisense and autocompletion features offered by the editor to build the DAX code. These features can help with proper code syntax and save you from typing errors.
7. Press Enter to validate the code and save the measure
8. With the measure selected in the field list on the right, switch to the Modeling ribbon
9. Use the Data Category drop-down to select Web URL (see Figure 4)
Add report links to the report
With the Detail Report Link measure added to the data model and categorized as a web URL, you can add a link to the table that will dynamically generate a context-specific link for report navigation.
1. Before you go any further, click the floppy disk icon on the toolbar to save your work
2. Select the table on the right side of the report canvas that shows sales by product category grouped by years
3. Drag the new Detail Report Link measure from the field list to the Values field well below the SalesAmount field. This adds a new column to the table visual showing a long URL in blue “link” text (see Figure 5)
Tip: It is easy to deselect a visual so before you begin changing properties, make sure that you still have the table visual in the report canvas
4. In the left-most blade in the panel to the right of the report canvas, click the paint roller icon to see the properties for the table visual, shown in Figure 6
5. Expand the Values group
6. Locate the URL icon property and switch the state of the property to On
7. Verify that the web addresses displayed in the last column of the table are replaced with a small blue link icon, which you can see in Figure 7
Test and publish the completed report solution
A this point, navigation should work from the report in Power BI Desktop, just as it should after the report is deployed.
1. Hover over the link icon on a few different rows to see the parameters at the end of the address in the tooltip
2. Test the report navigation by clicking a link after noting the corresponding year and country.
3. Publish or save the paginated report to your report server or Premium Power BI tenant
4. Test drillthrough navigation in the deployed reports
5. Navigate to the deployed copy of the Power BI report
6. Locate a row with values for a specific year and country and click the corresponding link
The paginated report opens in the browser
7. Verify that the paginated report parameters reflect the selected year and country
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 let’s 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. 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.
A couple of things to keep in-mind when using this technique are that there are string size limits when passing parameters in a web URL. 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.