Drillthrough from Paginated Report to Power BI

— report recipe
also see: Drillthrough from Power BI to an SSRS Paginated Report

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.

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

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.

Object Type	Accepts Filters	Description
Dashboard	No	Dashboards don’t accept filters
Report	Yes	Shows default report page with report-level filters
Report page	Yes	Applies report-level filters to page
Bookmark	Yes	Bookmark and filter directives can be combined in the URL query string using “&” (rather than “?”) to append directives
Workspace app	Depends	Default page in app navigation must be a report rather than a dashboard
Workspace app report page	Yes	Same as a report. Use path to app or a report within the app
Workspace app bookmark	Yes	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.:

Operator	Description
eq	Equal to
ne	not equal to
ge	greater than or equal to
gt	greater than
le	less than or equal to
lt	less than
in	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:

https://app.powerbi.com/groups/cdae7f3c-b818-493a-af56-55308d41798f/reports/0dcf5a4d-1bf2-438f-b13b-95eb9adadd91/ReportSection

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:

https://app.powerbi.com/groups/cdae7f3c-b818-493a-af56-55308d41798f/reports/0dcf5a4d-1bf2-438f-b13b-95eb9adadd91/ReportSection3b93b5700b15a516ed98

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.

Object Type	Query String Attribute	Object ID
Workspace	groups	cdae7f3c-b818-493a-af56-55308d41798f
Report	reports	0dcf5a4d-1bf2-438f-b13b-95eb9adadd91
Page	ReportSection	3b93b5700b15a516ed98

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…

https://app.powerbi.com/groups/cdae7f3c-b818-493a-af56-55308d41798f/reports/0dcf5a4d-1bf2-438f-b13b-95eb9adadd91/ReportSection?filter=Product/Brand eq ‘Contoso’ and Sales/Quantity lt 5

…the report opens with only data where the Brand is Contoso and Sales records have a quantity less than 5. But, my browser seems to have mangled the URL making it difficult to decipher:

https://app.powerbi.com/groups/cdae7f3c-b818-493a-af56-55308d41798f/reports/0dcf5a4d-1bf2-438f-b13b-95eb9adadd91/ReportSection?filter=Product~2FBrand%20eq%20%27Contoso%27%20and%20Sales~2FQuantity%20lt%205

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:

http://localhost/reportspbrs/powerbi/Report%20Recipes/Sales%20Analysis

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:

Character	HTTP Escape Code	Unicode URL Escape Code
Space	%20	_x0020_
$	%24	_x0024_
&	%26	_x0026_
`	%60	_x0060_
:	%3A	_x003A_
<	%3C	_x003C_
>	%3E	_x003E_
[	%5B	_x005B_
]	%5D	_x005D_
{	%7B	_x007B_
}	%7D	_x007D_
“	%22	_x0022_
+	%2B	_x002B_
#	%23	_x0023_
%	%25	_x0025_
@	%40	_x0040_
/	%2F	_x002F_
;	%3B	_x003B_
=	%3D	_x003D_
?	%3F	_x003F_
\	%5C	_x005C_
^	%5E	_x005E_
|	%7C	_x007C_
~	%7E	_x007E_
‘	%27	_x0027_
,	%2C	_x002C_

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:

?filter=Product~2FProduct_x0020_Category%20in%20(%27Accessories%27,%27Bikes%27)

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).

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.

Figure 2

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.

Figure 3

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_”)

Return sText

End Function

The completed function is shown in Figure X

4. Click OK to accept the code changes and close the Report Properties dialog.

Figure 4

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.

Figure 5

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.

Figure 6

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.

=Parameters!PBIReportURL.Value

& “?filter=” & Code.EncodeObjectNames( “Product/Product Subcategory” )

& ” eq ‘” & Fields!ProductSubcategory.Value & “‘ and Date/Year eq ” & Fields!Year.Value

5. Click OK to accept the new image settings.

6. Save your changes to the Sales Summary report.

7. Switch to Preview and test the drillthrough navigation link by clicking the link icon on any row.

Figure 7

The target report should show only data for the selected Subcategory and Year.

8. Deploy the Sales Summary paginated report to your report server or Premium Power BI tenant.

9. Test the deployed Sales Summary report to verify that it works as it did in the designer.