Drillthrough from Power BI to an SSRS Paginated Report

July 4, 2019 by Paul Turley

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

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.

Solution

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.

Figure 1

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.

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:

&Year=2018&Country=Canada

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:

Parameter Name	Description
ServerName	SQL Server address or server name. You can use “LocalHost” if it is a local instance.
DatabaseName	SSRS2019Recipes
ReportURL	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

Figure 3

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:

Detail Report Link =
VAR SelectedYear = SELECTEDVALUE( 'Sales Summary Subcategory Country'[Year] )
VAR SelectedCountry = SELECTEDVALUE( 'Sales Summary Subcategory Country'[Country] )
VAR RptURL = SELECTEDVALUE( 'Constants'[Report URL] )

RETURN
RptURL & "&Year=" & FORMAT( SelectedYear, "0000") & "&Country=" & SelectedCountry

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)

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)

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

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

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

Figure 8

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

Interview with Guy In A Cube’s Adam Saxton and Patrick Leblanc

December 19, 2017 by Paul Turley

Since starting the Guy In A Cube series over three years ago, Adam Saxton has become the front man for Microsoft Business Intelligence feature announcements and Power BI how-to tutorials. Joined by Patrick Leblanc last year, the Guy In a Cube series features over 200 short video tutorials with at least two new clips added every week. The Guy In A Cube YouTube channel currently has over 13,000 followers.

I always look forward to spending time with both of these guys at community events. I first met Adam several years ago when he was a Senior Support Escalation Engineer for Microsoft who helped with a number of tough BI server setups. Patrick did senior level consulting work for customers in the field before joining Microsoft as Data Platform Solutions Architect. Adam and Patrick are natural born entertainers. With their deep knowledge and expertise with database technology, security and the entire BI, analytics and reporting stack; they offer one of the top online resources for learning and keeping up with the rapidly-expanding Microsoft BI platform.

At conferences and events, I rarely see Adam without a camera in his hand and so was a real treat to get them in front of my camera for this interview. We chatted about how they got started and how they continue to use their series to highlight new and important features, and to provide guidance to support the Microsoft BI community.

In this interview during the 2017 PASS Summit, we continue to explore the topic of this series of blog posts on Microsoft BI solutions for the enterprise. Patrick and Adam share their guidance about managing large-scale solutions, version control and multi-developer projects using Power BI, Analysis Services and SQL Server.

Can’t We Just Get Along? Making SSRS, Power BI and Excel Play Well Together

July 3, 2017 by Paul Turley

Please join me and other 2017 PASS Summit speakers for 24 Hours of PASS: Summit Preview on July 19th and 20th. 24HOP is a series of 60 minute on-line sessions presented back-to-back for 24 hours, from the same professionals who will deliver preconference and main conference sessions during the Summit the first week of November this year. These online sessions are free of charge and normally attended by thousands of individuals to gain insight and knowledge about the topics. 24 Hours of PASS features free educational webinars delivered over 24 hours. Topics covered in this edition include Performance Tuning, SQL Server 2017, Linux, DevOps, Azure, PowerShell, SSRS, Power BI and much more. Browse all sessions. These webinars provide a sneak peek at some of the best practices, expert tips and demos you’ll find at this year’s PASS Summit in Seattle. Continue reading →

Modern Reporting with SQL Server 2016 and 2017 Reporting Services full day pre-conference session

June 15, 2017 by Paul Turley

I am thrilled to be presenting a full-day preconference session before the PASS Summit, on October 31st. Please join me for a deep tour of the new capabilities and BI platform integrations for SQL Server Reporting Services 2016 & 2017. The session will also review the essential skills and tasks to setup and configure the report server and web portal, report design and modern report solution planning. Continue reading →

Professional SSRS 2016 Book Preview Posts

January 22, 2017 by Paul Turley

This page is a table of contents for for several new and forthcoming posts. I’m posting a series of excerpts for my Wrox Press book: Professional SQL Server 2016 Reporting Services and Mobile Reports. Each of the posts is a condensed version of the material covered in a corresponding chapter from the book. Although I would love for you to buy the book to get the full edition of each topic, each post will contain valuable information that I hope will be informative and educational on it’s own; whether you buy the book or not. I’ll update this index with topics and links as I continue to add each post. Until then, some of these will serve as placeholders for future posts.

Please post comments or contact me through my blog if you have questions or feedback, or if you are interested in training and consulting related to this material. – Paul

Continue reading →

Power BI on-Premises Production Targeted for Mid 2017

December 16, 2016 by Paul Turley

NEWS FLASH: Power BI reports can be deployed to SQL Server Reporting Services web portal. The production-ready release is targeted for mid 2017. This is much sooner than most folks in the community were anticipating. An installable technical preview is targeted for January of 2017. This announcement was just made on the SQL Server Reporting Services Team Blog.

From the announcement:

Which Power BI capabilities do you plan to add to SSRS?

We’re focusing our efforts on adding Power BI reports to SSRS and on supporting the features Power BI Desktop offers for use within these reports, including a variety of data connectors and visualizations. Beyond the current Technical Preview, we plan to add support for

Custom visuals
Additional data connectors (besides Analysis Services), cached data, and scheduled data refresh
Power BI mobile apps (viewing Power BI reports stored in SSRS)

Given our focus on Power BI reports, we have no current plans to add other Power BI features (such as “dashboards,” Q&A, Quick Insights, and others) to SSRS.

What can we expect in the next Technical Preview of Power BI reports in SSRS?

With the current Technical Preview, we used a pre-configured Azure VM to offer you a preview that’s quick and easy to try. Our focus for the next Technical Preview is on a version you can download and install on your own VM or server, a necessary next step toward a production-ready version. Aside from this aspect, the functionality will be similar to the current Technical Preview’s.

When will we have this next Technical Preview?

We’re targeting January 2017 to release this next Technical Preview.

What’s the release vehicle for a production-ready version?

We plan to release the production-ready version in the next SQL Server release wave. We won’t be releasing it in a Service Pack, Cumulative Update, or other form of update for SSRS 2016.

When will we have a production-ready version?

We’re targeting availability in mid-2017.

Can I deploy SSRS 2016 today and migrate to SSRS with Power BI reports when it’s available?

Yes, we aim to make it easy to migrate to SSRS with Power BI reports from SSRS 2016 and previous versions.

The complete blog post can be found here: https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/12/16/power-bi-reports-in-sql-server-reporting-services-feedback-on-the-technical-preview/

Reporting Services 2016, The Force Awakens session at Summit 2016

October 27, 2016 by Paul Turley

THANK YOU…

everyone who attended my session today titled “Reporting Services 2016, The Force Awakens” We had a packed house with attendees standing along all of the walls in two joining session rooms. This is indeed a very popular topic! The session was recorded and will be available to watch with all the Summit 2016 session recordings. You can download the slides deck here. Samples and demo material are available from these resources (which are on the References & Sample slide):

Professional Microsoft SQL Server 2016 Reporting Services and Mobile Reports
Amazon (samples)

Self-paced online SSRS 2016 training
EdX course DAT214x: “Analyzing and Visualizing Data with SQL Server Reporting Services”
EdX.org (samples)

The Essential Guide to SQL Server 2016 Reporting Services
SQL Server Pro Magazine / SQLMag.com
Link (samples)

My Blog
SqlServerBiBlog.com

Reporting Services 2016, Power BI and PASS Summit 2016

October 25, 2016 by Paul Turley

What am I most excited about as I prepare for the PASS Summit here in Seattle this week? A lot of things. Preparing for my session, which will be on Thursday at 1:30, by far the most popular and interesting topics are about integration and tool choice. Today’s public announcement on the SSRS product team blog about on-premises Power BI integration with Reporting Services is really big news. It’s great to see two of the technologies I love working together. Whether in the cloud or on-premises, Power BI and Reporting Services can be used together.

Thursday’s session is titled “Reporting Services 2016, the Force Awakens”. The consensus among industry users is that Reporting Services was stalled and left alone for far too long. Technology and industry trends have moved forward over the past five years or so but Microsoft have invested in other tools and products, aside from Reporting Services. The SSRS server architecture is capable but the rendering and delivery components needed to be modernized.

The good news is that the modernization effort has been underway for quite some time. Resources that were directed to other investments have been recommitted, not only to the Reporting Services product, but to integration across the product stack. It’s really hard to build a reporting platform that addresses every need. I think Microsoft have a history of creating false expectations by putting experimental and incomplete features out in the industry that weren’t on the committed roadmap. I know better than to believe that the pattern won’t ever be repeated but I firmly believe that there is a well-defined reporting and BI roadmap and product teams are making rapid progress against it. It is once again an exciting time to invest in the Microsoft analytics, reporting and BI tool set.

I’ll publish the entire deck after the 2016 Summit. Until then, here are some selected slides…

Comparing the progress of new features added to SSRS over the release cycles, the product really slept from 2010 until 2016.

Microsoft’s reporting & analytic platform has shifted significant as they moved from products to services, and from servers to the cloud. The dust is settling on this major refocusing effort. Capabilities that were introduced in Azure, Cortana Analytics and Power BI are now becoming available to “the box”, on-premises or in hybrid cloud scenarios. We can expect more announcements on this as the roadmap is played-out and gaps are filled-in.

Reporting Services is a central player in the on-prem delivery of the BI, analytics and reporting tools offered by Microsoft. These include Excel, Paginated reports created with SSDT/report designer or Report Builder, Mobile Reports and Power BI Desktop.

…more to come!

check back for updates.

SSRS 2016 Book Nearly Done… My Brain is now Jell-O

July 19, 2016 by Paul Turley

(first excerpt from the upcoming book)

“Professional SQL Server 2016 Reporting Services and Mobile Reports” from Wrox Press is content complete! The book is well into the editing stage– still, another grueling phase of the publishing process, but there is light at the end of the proverbial tunnel. What is it like to write an 800+ page technical book? I’ve been trying to come up with the best metaphor to describe a year of persistent weekend-and-evening content development – building labs, demonstrations and exercises; performing research and and hounding my friends on the product team. A big thanks thanks to Chris Finlan, Riccardo Muti and Robert Bruckner on the SSRS product team for their continued help and support.

I’ve admittedly been a bit quiet on the community front for a while I’ve been heads-down on this book project but I hope to start redirecting more energy to community content.

Intense book writing can be a life-sucker:

This is Paul’s brain…
…this is Paul’s brain after writing a technical book:

Don’t get me wrong – I enjoy book writing. I’ve spent a lot of my career writing technical books and courseware because I enjoy the challenge. Writing helps me see things from different perspectives and at different levels of depth and difficulty for different audiences.

Give my brain a couple of weeks to heal and I’ll channel some of that energy into blogging and producing more community-facing training content.

So, what’s in the book? This is the Fifth Edition of the Wrox Professional SQL Server Reporting Services series which I’ve been leading and co-authoring since 2003. Amazon still has a working title listed but this is normal. As a next-edition, some of the 2012 edition evolved forward but most of the book is all new material. Four chapters are dedicated to the new Mobile Report features introduced in SSRS 2016. Before someone asks… when will the book be out? I don’t know exactly; there’s still work to be done. Soon. How’s that?

Over the next few weeks, I’ll post excerpts from the book to give you a taste of what’s coming and provide some valuable information about SSRS 2016.

Here’s a short except from Chapter 2:
What’s New in SQL Server 2016 Reporting Services?

Reporting Services was released as an add-in tool for SQL Server 2000 back in early 2004. At the time, the feature set was light by comparison to the product today but the foundational architecture was hasn’t changed significantly. Essential features included basic charts, sub-reports data regions and single-value textboxes.

The second release in 2005 added a self-service report authoring tool called Report Builder (later named Report Builder 1.0) paired with a semantic modelling tool in the designer. The original modelling and ad hoc report tool has since been deprecated but it inspired more capable replacement technologies like Power Pivot and the later generations of Report Builder. Not to be confused with the original Report Builder tool, Report Builder 2.0 and 3.0 produce report definition files compatible with the report project tools that are integrated with Visual Studio; originally called Business Intelligence Development Studio (or “BIDS”).

Report Builder and Designer Enhancements

The report design experience for standard “paginated” reports hasn’t really changed much over the past few product versions but there have been incremental improvements. Report Builder is restyled to conform to Office 2016 standards. The installation process for Report Builder changes to an “Evergreen” app. This means that Microsoft maintains updates for frequent download, rather than the old “ClickOnce” installation from your on-prem server. Similar to prior versions, users can elect to install Report Builder from the Report Portal menu. Report Builder is updated with a modern look-and-feel, simple and sleek, Changes are mostly cosmetic while the fundamental features are the same.

The Visual Studio integrated Report Designer is now part of SQL Server Data Tools (SSDT), a downloadable add-in for Visual Studio. Although the tool set hasn’t changed significantly, there are some subtle changes to the way SSDT is installed and the way updates are delivered. First of all, confusion about the name “SSDT” is dispelled because the former “SSDT” (the previous version add-in for database projects) and “SSDT for BI” (the previous version add-in for SSIS, SSAS and SSRS projects) are now a combined package, simply called SSDT. Secondly, you can simply download and install a version of SSDT that will work with the current version of Visual Studio or a few versions back. The SSDT add-in will be updated frequently and you will have the option to install updates on-demand from within Visual Studio.

Modern Browser Rendering

One of the most significant product improvements in 2016 may be one of the least apparent under casual observation. The entire HTML rendering engine has been completely overhauled across the platform. The report portal, used to navigate and manage report content, and the actual report content are rendered to modern HTML 5 standards, which are supported by all modern web browsers. The shift to modern HTML output means that web content produced by Reporting Services is consistently consumable on any device, regardless of the operating system or web browser, so long as it supports modern standards. The benefits are readily apparent when reports simply work on smartphones of any type, on tablets and laptop and desktop machines; regardless of the brand or operating system.

Parameter Layout Control

You will have improved control over parameter formatting and placement. Since the inception of Reporting Services about twelve years ago, parameters have always been arbitrarily arranged in a narrow bar at the top of the browser window, from left-to-right, and then top-down. The report designer has a grid to manage the placement of parameters, in the parameter bar, in any configuration, within definable rows and columns.

Mobile Reports

The addition of mobile dashboards to the SSRS platform is based on the Datazen product acquisition from ComponentArt in 2015. Mobile reports are primarily designed to enable data interactivity in dashboard-style reports created by a mobile report developer. Managing this expectation is important because this tool is significantly different than conventional Reporting Services.

Mobile reports can be viewed in the browser but are optimized for phone and tablet devices through native, installed applications running on all the major mobile OS platforms. They are not a replacement for high-fidelity paginated reports created with Reporting Services, or self-service analytics in Power BI; they serve an entirely different purpose.

At first, the mobile dashboard experience may seem to be a simple drop-in of the Datazen product; but, it is apparent that some integration with the SSRS architecture has already taken place, and more adaptations are likely on the horizon. The first notable difference is the Datazen server is entirely replaced by the SQL Server report server, and queries are now managed as SSRS shared datasets.

KPIs

New Key Performance Indicators (KPIs) integrated with the new Report Portal are also based on the Datazen product acquisition. These KPI visuals are created and managed entirely within the portal. In additional to the standard traffic light style comparison of actual vs target values, KPIs can include a trend line or segment chart. The KPI is driven by data from one or more shared datasets that were created in the SSDT report designer. For ease and simplicity, any value of the KPI can be entered manually through the design page.

Although they are visualized in the Report Portal, KPIs are delivered to mobile devices through the Power BI mobile applications on every popular device platform.

Native Printing Control

The previous printing capability in SSRS replied on an ActiveX control which was only supported on Windows desktops and in certain web browsers. Even in tightly controlled Windows server environments, system administrators would rescind ActiveX support and disallow report printing from the server. The modern printing solution uses the PDF renderer to printable output and then the Adobe document viewer to perform the actual printing

PowerPoint Rendering

Users have had the option to export and render report content to Excel for several SSRS versions. Output to Word was added in SQL Server 2008 and then both of these rendering options were improved and updated in the 2008 R2 version. Now a third Office application format will be supported with the introduction of PowerPoint document rendering.

Most report items and data regions are converted to individual image objects in the resulting PowerPoint sides with one side generated per report page. Additional slides are created based on the report content size and layout. Textboxes are created for titles and report text, which support some report actions and textbox properties.

Integrated and Improved Report Portal

A new Report Portal web interface is introduced to replace the Report Manager. Like Report Manager, the portal is an ASP.NET web application used to access, run and manage reports in the web browser. The new portal has a look-and-feel we are accustomed to seeing in other modern apps from Microsoft these days; with responsive design for constancy on different device form factors. Report Portal will be the home for mobile reports, KPIs and paginated reports – the new name for RDL reports authored with Reporting Services. In the future, we may see support for additional content types.

Report Portal supports in all modern web browser by emitting responsive HTML5 with adaptations for mobile devices and screen orientations.

New Charts and Visual Enhancements

With the addition of two new chart types, visualization improvements are inched forward in Reporting Services. The new Sunburst and Treemap charts apply multi-level field groups visualized in both color and visual boundaries.

New and updated report visuals are likely to be an area of focus for future Reporting Services enhancements, given the success of self-service BI tools like Power BI. The design interface is identical to existing chart types and the only real difference is that groups of rows are visualized in these unique formats. The Sunburst chart is also capable of consuming unbalanced hierarchies with slices generated for different levels only where data points exist.

Standardized, Modern Browser Rendering

At first, you may not notice significant changes from previous versions, but the HTML renderer has been completely overhauled and updated. Now, reports are rendered to HTML 5 standards whereas, they should consistently maintain the same appearance and behavior in all modern browsers that support the HTML 5 standard such as Microsoft Edge, IE 11, and newer versions of Google Chrome, Safari and Firefox. This change is a welcome improvement which should clear up many problems with inconsistent and quirky report layouts while using different web browsers and devices. By the same token, the change means there is no specific backward-compatibility for outdated browsers; consequently, reports that may have worked (or partially worked) in an old version of Internet Explorer may no longer work until the user upgrades.

Power BI Dashboard Pinning

For organizations who have invested in the Power BI cloud service, the Power BI integration feature allows users to pin graphical SSRS report visuals to their online dashboards. In order to use this feature, an administrator must register the report server with an existing Power BI subscription, and a report user must have access to the Power BI subscription.

Reporting Services Configuration Manager includes a new page to manage Power BI Integration. This is where you register the report server instance with the Power BI subscription. When a report with “pinnable” items (such as images, charts and gauges) is viewed in Report Portal, the Power BI icon is displayed on the toolbar.

“pinnable” items are highlighted in Report Portal. When a visual is selected, you are prompted to select a Power BI dashboard and the refresh frequency. This schedules an Agent job on the report server to push updated visuals to the dashboard at the selected frequency.

The Select frequency of updates option utilizes the SSRS subscription architecture by scheduling a SQL Server Agent job on the database server with the report server catalog. The Agent job re-queries the report data and then a report server component refreshes the Power BI dashboard tile with an updated report visual.

Pinned report visuals appear on the dashboard alongside the Power BI report and Excel visuals. Clicking on one of these visuals will drill-through to the report back on the on-premises report server. This gives users a seamless navigation experience between cloud-hosted Power BI content and selected report visual elements on your own report server.

The integrated Power BI experience is a big step forward in providing a completely integrated IT-hosted and self-service reporting, BI and analytics solution.

What do you want to learn about SSRS 2016 at PASS Global Summit?

June 23, 2016 by Paul Turley

I’m thrilled to have been selected to deliver a session at the 2016 PASS Global Summit titled “Reporting Services 2016: The Force Awakens”. I’ve been speaking at SQL Saturdays and have done some training on SSRS 2016 this year but this new session has yet to be finished SO…. please let me know what you want to learn about Reporting Services in October. Please leave a comment in this post and let me know what’s most important and appealing to you. I’ll do my best to work the most popular topics into my session.

Here are some ideas:

What’s new in the web portal and how is it different then Report Manager
How is HTML 5 rendering different from previous versions
What browsers and devices are supported by SSRS 2016
What can I do (and can’t do) with mobile reports
How to use and create KPIs
How to create navigation paths with KPIs, mobile reports and paginated reports
How does SSRS integrate with Power BI
How does mobile reporting work and look with different types of reports and visuals
How have paginated report visuals changed and improved
Is there anything new or different about SSRS setup or configuration
Is programming with SSRS any different than before

Row-level Security Just Got Better: Passing Report Credentials to Analysis Services in SQL Server 2016

June 13, 2016 by Paul Turley

A new ground-breaking feature in SQL Server 2016 allows report user context to be passed through shared data sources, enabling role-based, row-level security. This feature doesn’t require complex administration configurations, service principal delegation, Kerberos, Claims/Windows Token conversion or impersonation. You may not be as excited as I am about this unless you’ve also lived through the tedious configuration process we’ve had to endure for many years until recently.

A little over a week ago I heard from my friend and past co-author, Robert Bruckner – a Senior Architect and Development Lead on the Reporting Services product team. He told me I needed to check out a new capability they had just enabled in SQL Server 2016 that would make it much easier then before to implement row-level security for SQL Server Analysis Services. I had to check it out for myself and was please to discover that this technique works with Reporting Services mobile and paginated reports. It also works in a variety of server architectures. I tested it in a domain environment and on a simple workgroup server. It will work in scaled-out scenarios, and with or without SharePoint in the mix.

Until now, there were two major blocking points to authenticate report users on the SSAS server so data could be restricted or filtered for each user. Kay Unkroth talks about the prior authentication landscape in his post to the SQL Server Reporting Services Team Blog titled Passing user names to Analysis Services with personalized connection strings in SQL Server 2016 Reporting Services. He describes the challenges in more detail and I’ll summarize from my experience.

1. The first challenge is that user context gets lost as database connections are passed from one service to another. Before we get into the weeds with this story, let’s just categorize this as complicated and confusing. It raises high-visibility questions and concerns that necessitate handing-off to support and administrative folks to take care of settings and components outside the environments you manage – namely the database and report servers.

Not long ago when we tried to use SharePoint as the platform for all BI and reporting systems; getting connection and user information to pass through through all the layers in the server architecture was more like running a relay race… a user logged into SharePoint and navigated to a report. At the point, the credential object for the user was exchanged for a Claims Token. The user who chose to run the report and the Claims Token had to be delegated to a Windows service principal name (SPN), which, in-turn was delegated to the service account running a specific service on the Windows server. It was then up to the service to interface with your database in Analysis Services. Without getting into more details; the service account was not the user who had access to certain important data. For the service account to connect to the database at all, it needed to have either admin privileges – a potential security risk – which is now being handled by your system or database administrators (who probably know very little about your reporting solution, your users’ need or business objectives. They just want to make sure it has no security holes and will lock-down anything suspicious or unfamiliar.

2. The second challenge is that Reporting Services requires shared data sources with stored user credentials, in order to use a lot of it’s advanced features like shared datasets (under some conditions), data-driven subscriptions, caching, history or snapshots. In every case, the credentials of the user actually running the report get exchanged for the service proxy account and the original user context is lost. Analysis Services sees the same user connect for every report request. A security genius might conclude that these are all issues that can be handled through some security configuration mechanism – and they would be right. The problem is that they often don’t get handled because of the complexity or the simple fact that management falls within another team’s realm of responsibility.

Over the many years that Reporting Services has been a leading reporting tool in the industry, practitioners have asked if they could somehow add an expression to a shared data source definition that would inject the username into the connection string so it could be deciphered in the server. Interesting idea with a lot of reasons that it wouldn’t happen – – sort of.

Using conventional approaches, where principal delegation was configured and used correctly – which may or may not involved the Kerberos protocol and NTLM Authentication.

The New And Improved Technique: Using the CUSTOMDATA Property

Drumroll, please… Here’s the big change that allows new, cool things to happen.

In every data source connection string, you can add a simple expression that maps the current Windows username to the CUSTOMDATA property of the data source provider. This works in SSRS embedded data sources, shared data sources, in a SharePoint Office Data Connecter (ODC) file and in a SharePoint BISM connection file. In each case, the syntax should be the similar. Here is my shared data source on the SSRS 2016 report server:

The connection string modification is simple. Just add this text to the end, following a semicolon:

;CUSTOMDATA = {{ USERID }}

This value travels through the connection stack when ever a connection is open on the database server. Since the CUSTOMDATA connection property value is assessable and exposed by query and expression languages and APIs on the server, code can be written to respond to the user. In the case, we will filter queries with records and data that only the current user should see.

Windows and Report Users

Windows users, whether added to Active Directory, whether on domain-joined computers or on a simple workgroup server, are standard Windows users and need not be added to the Local or Domain Administrators group. Any SSAS users with restricted data access should also not be added to any Analysis Services administrator role.

As you can see, Paul and Fred are standard Windows users with limited access to elevated resources.

Analysis Service Roles and Membership

I’ve tested this technique with both SSAS multidimensional and Tabular, with the same results. I’ll show you the set-up on a named instanced on my server, SVR201R21, named SSAS_Tabular. The SSAS Security properties page for the instance show that only the Administrator local admin user and the SSAS service accounts. This is important to check because if you accidentally add a user as an administrator, row filtering won’t work. Testing and debugging solutions on a single development machine requires a lot of logging out and logging back in to test under different user conditions.

My SSAS Tabular database is named Adventure Works Tabular 2016. Under the database Roles, properties for the Users role, you see that there are three users; Fred, Paul and ReportUser. The ReportUser is our proxy users. When we need to setup shared a data source with saved credentials, I supply the user name and password for the ReporrUser account.

It’s important to note that the ReportUser must have Read access to the database in order to connect and execute queries. But, since that same account may be used to connect on behalf of others users, the ReportUser account will have restricted access to data through the security bridge tables that are implemented by the role’s row-level filtering expressions.

Row-level Filtering Bridge Table

The core approach for row-level, user-specific filtering is the same as always but the programmatic implementation is only slightly different. This is the essential foundation of that feature. The following table image demonstrates tow things. One is that the table itself can be completely generated using DAX. This is a calculated table (one of many new cool features added in SQL Server 2016). I’ve just used inline ROW functions to added each row to the UNION function that defines the entire table.

In production, you may want to store this lookup data in a SQL Server table for easy maintenance, and then process it into the model as you would all you tables.

For each row, the UserName is an exact match for the value passed in from the UsersName token in the connection string, which will be presented by the CUSTOMDATA DAX function. Do you see how this will work? For testing and debugging, you can add a couple of measures useful for know who the current user is and what the CUSTOMDATA function value returns.

User Name:=USERNAME()

Connected User:=CUSTOMDATA()

In a Reporting Services report, you can add these measures to your query and then add the dataset fields to textboxes in your report.

Implementing a Role-based Row-level Filter

Start by opening the Roles window…

Checking the user membership of the Users role; ReportUser must be a member if the report server or client tool is connected as ReportUser. Even though no records are mapped to this user in the security bridge table, it has served it’s purpose by getting us connected to the database.

Paul and Fred are in the role in case they connect on their own, like from Excel or from SSRS using a data source without stored credentials.

This demonstration scenario calls for filtering all the data related to the Sales Territories for user who have permission to see it, according to the Country/User mapping in the bridge table. If the data related to other business entities should have user-specific filtering, you would follow the same pattern and multiple filters will be in effect.

The filter on the Sales Territory table is a DAX expression. You can use other functions to provide additional logic but this is a good example that demonstrates the essential pattern. As each row is scanned during a query, the expression will determine if there is a match. If so, the row is returned. If there is no match, the row is ignored and not returned in the query results.

Here’s the filter expression with e brief explanation:

=’Sales Territory'[Sales Territory Country]
= LOOKUPVALUE(‘Security Bridge Country'[Country],
‘Security Bridge Country'[Country], ‘Sales Territory'[Sales Territory Country],
‘Security Bridge Country'[UserName], CUSTOMDATA()
)

(again, each row in the table is considered in turn)…

Does the current row’s [Sales Territory Country] field value match the value of…

the result of the LOOKUPVALUE function which looks in the Security Bridge Country table for a Country matching the current Sales Territory[Country] AND where the User filed value on the same row matches the value returned by the CUSTOMDATA function.

Let’s try it out…

My Reporting Services report uses a data source where I have added the text ;CUSTOMDATA = {{ USERID }} to the end of the connection string.

The dataset is a simple MDX query that returns the Sales Territory Country, the Connected User test measure and the Reseller Total Sales measure. Notice that when the query runs locally in the designer, the CUSTOMDATA function returned the literal text in the parameter token. This is expected.

Here’s the report design. I’ve added the Connected User measure so you can see what the CUSTOMDATA function returns, along with the filtered results.

Run as the Administrator user, only the Country row for the corresponding row in the Security Bridge table is returned.

Let’s test the report as a real user.

I’ll sign-out as Administrator and then sign-in as Paul…

Running the deployed report in SSRS web portal this time, only the Country records are returned for this records that were mapped to my user name in the Security Bridge Country table. Furthermore, if I were to run a query that didn’t explicitly use the Sales Territory Country, only the records related to the countries in my filter scope would contribute to any totals due to the relationships in the model.

As I mentioned earlier, I also tested this in a domain environment and it also worked exactly as expected.

This means that the next time I’m called on to implement a row-level security solution, I won’t have to keep the system administrator up all night setting up complicated service principal names and configuring authentication delegation. If I have rights to setup the SSAS server and the data sources on the report server, I can now do all the configuration work myself – and that is pretty exciting.

Thanks again to Robert Bruckner for bringing this to my attention, and to Kay Unkroth for laying out the basics in his post. And a big big thanks to the Reporting Services product team for making making this a reality and saving the next generation of BI solution practitioners from reliving the challenges of the past.

Reporting Service 2016 Unplugged

February 4, 2016 by Paul Turley

This month’s SQL Server Pro Magazine article:

In SQL Server 2016, my favorite report tools get some love with a few cool new features including, a little polish added to a few existing features, and a complete Report Portal overhaul.

Reporting Services reached a certain maturity as a rock-solid reporting platform somewhere just after 2008 – or at least it got past puberty. Most report developers would agree, the versions that followed SQL Server 2008 R2 were more like a midlife crisis for the product, because the feature set stalled for about five years. The latest version is not a complete product rebirth by any means, but it is a good indication Reporting Services is not headed for retirement any time soon; it now plays an important role in the larger Microsoft reporting ecosystem.

Rather than rehashing information posted about new and upcoming reporting features available elsewhere, I’ll share my experience thus far with the latest community technical preview (CTP 3.2) and some insights from the product developers. As with any preliminary software build, your experience may differ.

Design Tools…

The Resurrection of Reporting Services & The Maturing of Power BI

November 11, 2015 by Paul Turley

Spending the past two weeks at the annual PASS Global Summit and the Microsoft MVP Summit, I’ve consumed a literal firehouse of information about the Microsoft BI platform. I’ve participated in the PASS Summit for twelve years and the MVP Summit for seven years thus far and in that time, I don’t recall as much innovative change and product momentum as we have seen lately. The pace of significant additions to the business intelligence offering is truly astounding. Attending both of these events, I often learn about product feature investments on different levels which include those that are publically announced and those that are part of the roadmap and things that the product teams are working on or seriously considering in the near and longer-term future.

At both of these events, we saw a lot of very exciting functionality and heard some very bold statements about what the BI product teams are working on. Citing the product team official blog: “Our goal is simple – we want to put the power of data in the hands of every business and person on the planet. It is our objective to serve over a billion users with the Microsoft business intelligence (BI) platform.” To reach that goal, there are several very specific efforts in motion. The first objective is to harmonize all of the disparate report types that are currently surfaced in different tools like Reporting Services, Excel, Datazen and Power BI. This objective is to standardize reporting content types across Microsoft on-premises, cloud and hybrid systems. These include paginated reports, interactive reports, mobile reports and analytical reports & charts. There will be two different places for users to get to all their report content: Power BI dashboards and the new Reporting Services Portal. Both of these “portals” will serve up a variety of reports and visual content whether hosted on-premises or in the cloud. Additionally, users will have access to visual reports on mobile devices for all the platforms. Some details are still in flux but the direction and roadmap are quite clear.

Power BI: Past, Present & Future

Power BI was front and center in many sessions and discussions and the pace of new feature delivery is dizzying. The online Power BI service is updated weekly and the Power BI Desktop application is updated every month. What was crystal clear is that Power BI is getting tremendous attention from the product teams and leadership as the go-to visualization, modeling and data mash-up tool for many important scenarios. To-date, Power BI has been primarily marketed as a self-service analysis tool in the same way that Power Query, Power Pivot and Power View work in Excel. However, it is quickly becoming an enterprise product with the ability to integrate, automate and use it in a variety of managed business scenarios.

Consider each of these possible options:

Power BI Desktop running on a user’s computer with data connected from many sources; across the Internet and on-premises.
Power BI datasets, reports and dashboards published to the secure cloud subscription services, shared with different permissions for different groups of organization users.
Power BI dashboards containing PBI report visuals, on-prem SSRS report visuals, mobile reports & KPIs, and Excel visuals.
On prem Reporting Services Portal containing Power BI reports & dashboards, SSRS reports, mobile dashboards & KPIs, and Excel visuals.
Power BI reports and dashboards in the cloud connected to live, on-prem SSAS tabular, SSAS multidimensional, or relational data sources using DirectQuery.

Although we can’t say exactly how or when, we will have the ability to publish Power BI Desktop Reports on-premises. This is very welcome news. We will also have the ability to pin on-premises reports and other content to a Power BI dashboard in the cloud. Some of that capability was just added to SQL Server 2016 CTP 3.0.

Custom Visuals – This is one of several examples of how Microsoft is embracing open source code and community development. In addition to the dozens of D3-based visuals that community developers have contributed, the product team is releasing one new visual per week. Any custom visual in the gallery can be downloaded, added to a Power BI Desktop report and published to the service. This is quickly making most common and special-purpose BI visuals available to everyone in one tool.

DirectQuery – connections to live data sources use query folding to optimize performance and real-time results from SQL Server, Azure SQL Database and Azure SQL Data Warehouse. This also includes connectivity to MDX/multidimensional SSAS in SQL Server 2016!

Real-time dashboards & reports – Options for real-time data interaction include DirectQuery and direct connect through the Analysis Service connecter. On-prem data can also be managed with the Personal Gateway. According to team leadership at PASS, gateway enhancements for personal and enterprise applications should be announced soon.

Pinning SSRS Reports – A Reporting Services 2016 server can be registered to integrate with a Power BI subscription using the Reporting Services Configuration Manager. This allows SSRS report visuals to be pinned to a Power BI dashboard. I just tried this in the 2016 CTP 3.0 and it works quite well. When viewing a report in Report Manager, a toolbar icon appears. This prompts the report user to select a visual on the SSRS report and then for a dashboard in their Power BI subscription.

There is SO MUCH more coming!! The product team members are so excited to share what they’re working on and lines between what they “eluded” to in their PASS session presentations and what they told us and demonstrated at the MVP Summit under strict NDA were very fine. Just keep watching and you will see an astounding release velocity of new and impressive features in the next few months.

New Reporting Services

In SQL Server 2016, Reporting Services is getting a significant face lift on several fronts. The HTML renderer has been completely rewritten to emit pure HTML 5 to produce consistent output in every modern browser on every device. This capability is in the current CTP today.

Report Manager is replaced with a modern report portal that will host content from SSRS, mobile dashboards (formerly known as Datazen), Excel and eventually Power BI on premises reports. This raises questions about the availability of Power BI on-prem and the only comment I can make based on public information is that it is planned for some time in the future.

Report parameters can be moved around and organized with greater precision in the parameter bar at the top of the report.

The printing capability in SSRS will use a “plug-in free” PDF renderer rather than the old ActiveX control that will no longer require a plug-in download or special permissions to run.

Two new chart types are added (treemap and sunburst) and changes are planned to modernize the default look of report visuals.

How to Get SSRS Reports in Excel Without Exporting from Reporting Services

May 4, 2015 by Paul Turley

I’ve recently seen a wave of questions from clients and peers about difficulties exporting reports to Excel lately. Every few weeks I get a call or question about this. This topic has been a recurring theme for a very long time and one that I have encountered many times over the past – oh, eleven years or more – using SSRS. Business users like Excel because it’s what they know and they can reformat and manipulate data in a workbook. People like Reporting Services because all the hard work of connecting to data sources, writing queries, totaling, grouping and formatting the results gets done once and then all they need to do is run the report. Users want the best of both worlds and they expect that when they export a report to Excel that they should have their cake and eat it. In other words; they should be able to get a report, with all the goodness of headers, scrolling regions, pagination, interactive sorting – you name it – to work exactly the same way in Excel. Many improvements have been made to all of the report rendering extensions over the years and the Excel export story is better than ever but here’s the deal – bottom line – the more rich formatting and advanced functionality that gets designed into an SSRS report, the less likely it is to export all that functionality to Excel or any other rendering format. Continue reading →

SQL Server 2014 Upgrade Sessions on the Microsoft Virtual Academy

April 12, 2015 by Paul Turley

It was a great honor to be asked to join my associates from SolidQ at the Microsoft Virtual Academy Studios in Redmond and talk about how to upgrade to SQL Server 2012 and 2014. These recordings, which are also on my YouTube Channel, include the material I covered in these sessions. The entire series of studio presentations are hosted on Channel 9 and here at the MVA with accompanying assessment surveys and resources.

In these studio sessions, I am joined by my fellow authors of the 429 page SQL Server 2014 Upgrade Technical Guide; Richard Waymire, Ron Talmage and Jim Miller from SolidQ. Jim and I were responsible for the Business Intelligence content. In our sessions Jim covered SSIS and SSAS Multidimensional, and I covered SSAS Tabular, BI tools and SSRS. In this edited portion of the SSAS session, Jim begins with a brief summary of multidimensional upgrade options and I continue to discuss opportunities to implement SSAS Tabular solutions. BI topics apply equally to SQL Server 2012 and 2014 upgrades. Continue reading →

Designing the Power BI Report

Solution

Build the dynamic target report address link as a measure in Power BI

Add report links to the report

Test and publish the completed report solution

Final Thoughts

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

The New And Improved Technique: Using the CUSTOMDATA Property

Windows and Report Users

Analysis Service Roles and Membership

Row-level Filtering Bridge Table

Implementing a Role-based Row-level Filter

Share this:

Like this:

Share this:

Like this:

Power BI: Past, Present & Future

New Reporting Services

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: