Drill from a mobile report to a paginated report in SSRS (SQL Server)

The release of SQL Server 2016 included many new and innovative features. One that intrigued me the most was the integration of Mobile Reports into SQL Server Reporting Services (SSRS). Recently, I was planning a full-day hackathon focused on the Microsoft Data Platform and I was asked to show drill from an SSRS Mobile report to a SSRS Paginated report. That’s easy enough I thought, but of course there was a catch. The requirement was to drill from the Mobile Report to:

  1. A paginated report that includes a parameter populated from a SQL Server
  2. A paginated report that includes parameters populated from a SQL Server Analysis Services (SSAS)

The drill would populate parameters in the Paginated report with values passed from the Mobile report. Still, didn’t think that wouldn’t take much time. So, without apprehension, I agreed to build the demo.

Like I said earlier this is a two-part blog post. In this post, I am going to demonstrate how to drill to a Paginated report, passing values to a parameter populated from SQL Server. In the second post, I will explain how to drill to a Paginated report, passing values to a parameter populated from SSAS.

To get started I created two Paginated reports meeting the include SQL Server parameters and SSAS parameters. Then I followed these steps:

  1. Open the Mobile Report Publisher and create a mobile report, similar to what is seen in the following image:

    mobile-report

  2. Then identify and configure a visual that supports drillthrough.
  3. Select the visual and notice in the bottom properties section there is a button labeled Drillthrough.

    mobile-report-drillthrough

  4. Select that button and select Custom URL from the list of available choices.

    mobile-report-drillthrough-target

  5. The Set Drillthrough URL window will appear.
  6. Before you proceed, you need to understand how to pass a parameter via a URL to SSRS. If you are not familiar with this, go here: Pass a Report Parameter Within a URL, to get more details.
  7. The fastest way to start building the URL navigate to http://<servername>/ReportServer and browse to the location of your report, replace <server name> with your server name. Right-click the link for the report and select Copy shortcut. This is the starting point for the URL.

    report-server

  8. Paste it into the Enter a URL to go to when this visualization is clicked text box on the Set drillthrough URL screen. Remove rs:Command=Render from the URL.
  9. Next you will need to add parameters. Go to the report in SQL Server Data Tools (SSDT) and find the name of the parameter(s).
  10. Return to the URL and add &<name of parameter>=, replacing <name of parameter> with your report parameter names, to the end of the existing URL.
  11. Select {{SelectionList.SelectedItem }} from the Available parameters list. You will get a preview of the URL in the textbox below.
  12. Finally select the button labeled Apply.

    mobile-report-set-drillthrough-url

Before you publish the report, let’s test it out. In the top right-corner of the Mobile Report Publisher select the button labeled Preview. Select the drill configured item and the report should open.

Well, that was pretty simple. The biggest challenge was constructing the URL. I hope the above steps help to de-mystify that and make configuring a drill smooth process. In the next post I will explain how to configure drill to a Paginated report that leverages SSAS as a source for the parameters. Most of the steps are similar, but configuring the URL is a little tricky. Also, Chris Finlan, from Microsoft, provides additional information about Drill and URL parameters for Mobile reports in this blog post.

2017-03-16T09:01:09+00:00