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

clip_image002

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.

clip_image004

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.

clip_image006

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.

clip_image008

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.

clip_image010

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.

clip_image012

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.

clip_image014

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.

3 thoughts on “Drillthrough from Paginated Report to Power BI

  1. Hi Paul, your table above shows a similar approach should be possible with bookmarks. I can’t find any details on the syntax though. Are you able to share an example or point to some documentation?
    Thanks,
    Barney

    Like

  2. Pingback: Power BI and SSRS Drillthrough – Curated SQL

  3. Pingback: Drillthrough Navigation Between Power BI and SSRS Paginated Reports | Paul Turley's SQL Server BI Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s