Professional SSRS 2016 Preview: Chapter 2–What’s New in SQL Server 2016 Reporting Services?

This post is part of the series: “Professional SSRS 2016 Book Preview Posts” which are 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.  The goal for this posts is to provide useful and meaningful information you can use.  For more comprehensive details, I refer readers to the rather lengthy book itself.  Note that large portions are copied directly from the book manuscript that may refer to figures and screen capture images that have been removed for brevity.

WHAT’S IN THIS CHAPTER

• Report designer enhancements
• Modern browser rendering
• Parameter layout management
• Introducing Mobile Reports and KPIs
• New printing and rendering options
• The new web portal
• Power BI dashboard pinning and integration

The enhancements to Reporting Services in SQL Server 2016 range from subtle to significant. Several notable enhancements expand the reporting platform and help round out the Microsoft Business Intelligence (BI) product tool belt. Chapter 2 contains no hands-on exercises so there is nothing to download and no exercises for this chapter. We will introduce the hands-on exercises and samples in the Chapter 3. Just sit back and learn about how Reporting Services is improved and enhanced; in some ways, it is the same or similar to the past few versions.

Before you learn about several new features introduced in Reporting Services for SQL Server 2016, take a look at the quick history lesson shown in Figure 2-1 that highlights the origins of the product.

Reporting Services was released as an add-in tool for SQL Server 2000 in early 2004. At the time, the feature set was light when compared to the product today, but the foundational architecture hasn’t changed significantly. Essential features included basic charts, sub-report 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) that was paired with a semantic modeling tool in the designer. The original modeling 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 (BIDS).

Several improved and progressively more powerful features appeared in later product versions. SQL Server 2008 R2 introduced many advanced visual elements such as gauges, sparklines, data bars, key performance indicators (KPI), and maps. After the introduction of so many new features, the only minor improvements in SQL Server 2012 and 2014 were a noticeable change to the feature cadence as product development resources were redirected to emerging products like Power Pivot, Power View, and then Power BI.

In 2015, under new leadership and restructured product teams, Microsoft reaffirmed its commitment to Reporting Services as a core feature of the Microsoft reporting and BI platform. The product now emerges from a period at rest to another wave of aggressive development and improvements. Many of the core features remain the same, and the design experience is relatively unchanged. But as discussed in this chapter, several new improvements are driving new momentum.

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 Microsoft Office 2016 standards. The installation process for Report Builder changes to an “evergreen” application. This means that Microsoft maintains updates for frequent download, rather than the old “ClickOnce” installation from your on-premises server. Similar to prior versions, users can elect to install Report Builder from the web portal menu.

Report Builder has been updated with a modern look-and-feel, simple and sleek, as shown in Figure 2-2. 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 SQL Server Integration Services, SQL Server Analysis Services, and SQL Server Reporting Services projects) are now a combined package, simply called SSDT (which also includes a project template for SQL Server database projects). 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. If Visual Studio is not installed on the computer, the SSDT setup package installs the Visual Studio shell. 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 web portal, used to navigate and manage report content, and the actual report content are rendered to modern HTML5 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, as well as laptop and desktop machines, regardless of the brand or operating system.

In earlier versions of Reporting Services, report output consistency was attempted with multiple version and browser logic in the rendering code to emit different content HTML for different browsers and versions, which quickly resulted in a patchwork of branched code and logic. By contrast, the modern rendering code outputs one lightweight stream of HTML5 that works across all modern devices.

The trade-off is that some backward compatibility is sacrificed, particularly with older versions of Internet Explorer (IE). Potentially, the most adverse effect of this shift to modern web standards is that users on older computers with an outdated operating system will need to upgrade to the latest available version of IE or their preferred web browser.

PARAMETER LAYOUT CONTROL

Have you ever had to explain to a user or project stakeholder that the parameter prompts are inflexible and that you have little control over how and where they are placed?

You will have improved control over parameter formatting and placement. Since the inception of Reporting Services about 12 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. Figure 2-3 shows that the Report Designer has a grid to manage the placement of parameters, in the parameter bar, in any configuration, within definable rows and columns.

The new parameter bar applies SSRS deployments in Native mode, but does not change the way parameters are rendered in SharePoint integrated mode.

UPDATED RDL SPECIFICATION

As with previous Reporting Services upgrades, the RDL has been revised in 2016. Figure 2-4 shows two code snippets from the Visual Studio XML viewer with the RDL namespace header and the ReportParametersLayout element near the end of the RDL file. Note that the xmn attribute version for the reportdefinition namespace is 2016/01

When SSDT for SQL Server 2016 is used to deploy reports to an earlier version report server, the Report Designer provides backward version compatibility by removing this metadata from the report definition file when the project is built. The versioned RDL file is written to the configuration output sub-folder under the project bin folder (the bin\debug folder by default), and then this file is deployed to the report server.

MOBILE REPORTS

The addition of mobile dashboards to the SSRS platform is based on Microsoft’s Datazen product acquisition from ComponentArt in 2015. Mobile reports are primarily designed to enable data inter- activity 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 operating system platforms. Figures 2-5 and 2-6 demonstrate the same mobile report on two different mobile devices. 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.

The SQL Server Mobile Report Publisher is a separate download that can be obtained by simply choosing the Mobile Report option from the web portal menu. In 2015, I wrote a series of articles for SQL Server Pro Magazine about how to create a mobile dashboard solution with Datazen. Datazen is still available as a standalone product—free to SQL Server Enterprise customers—but any future enhancements are likely to only take place in the new integrated platform. The article series is available here: http://sqlmag.com/business-intelligence/getting-started-datazen-microsoft-s-new-mobile-dashboard-platform. The essential design experience for mobile reports is nearly the same as I described in that series, but a few details change with the new integration. Microsoft Senior Program Manager Chris Finlan provides a complete step-by-step tutorial in his post titled “How to create Mobile Reports and KPI’s in SQL Server Reporting Services 2016 – An end-to-end walkthrough.”

KPIs

New KPIs integrated with the web portal are also based on the Datazen product acquisition. These KPI visuals are created and managed entirely within the portal. In addition to the standard traffic- light style comparison of actual versus target values, KPIs can include a trend line or segment chart.

The KPI shown in Figure 2-7 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 web portal, KPIs are delivered to mobile devices through the Power BI mobile applications currently on every popular device platform.

NATIVE PRINTING CONTROL

The previous printing capability in SSRS relied on an ActiveX control that 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 produce 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 versions of SSRS. 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 WEB PORTAL

A new web 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. Web 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. Figure 2-8 shows the Content menu in the web portal with options to selectively show different types of reports and folders.

Web portal supports in all modern web browsers 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 shown in Figure 2-9 apply multi-level field groups visualized in both color and visual boundaries.

Although the core chart and gauge components are largely unchanged, the default styling proper- ties have been modernized in the new product version. 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, whereby 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 out- dated 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 that 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.

The Reporting Services Configuration Manager (shown in Figure 2-10) 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 web portal, the Power BI icon is displayed on the toolbar (as shown in Figure 2-11).

Figure 2-12 shows that “pinnable” items are highlighted in web 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 (in Figure 2-13) 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, as shown in Figure 2-14. Clicking 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.

SUMMARY

As the components of Microsoft’s reporting platform continue to cross-pollinate, additional integration will be delivered through the web portal for desktop users and on device-specific applications for mobile users.

Report Builder continues to be the SSRS power user’s tool for creating reports the way Office users create and update documents. The Visual Studio–based SQL Server Data Tools (SSDT) is engineered for the developer and serious report designer. It now includes database project and BI add-ins in a single “evergreen” package for multiple versions of Visual Studio that can be maintained and updated independently from SQL Server. The SSDT designer produces RDL reports for SQL Server 2016, and will produce backward-compatible reports through the project build and deployment process.

Mobile reports and KPIs are a new addition to the Reporting Services family. These are simple by design, responsive, interactive, and optimized to run on native mobile applications installed from the device-specific app store. Mobile reports are integrated with the Reporting Services portal, using shared datasets developed in SSDT.

Several enhancements to the SSRS core functionality include native web browser print support, PowerPoint rendering, and new chart visuals. Probably the most significant (although the least obvious) improvement is that the underlying rendering of all reports and the web portal interface is fully compatible with all modern web browsers, applying HTML5 standards and responsive design. This means that the entire report experience can be had on a user’s device and browser of choice.

Chapter 3 addresses the requirements and steps to install and configure SQL Server Reporting Services and the dependent components. We discuss building a basic development environment and enterprise server deployments. You’ll learn about the report server architecture, which will help you gain a comprehensive understanding of the features and capabilities of Reporting Services.