SQL Server Reporting Services (SSRS) 2016 is the latest release of sql server based reporting solution by Microsoft.

SSRS forms part of Microsoft Business Intelligence suite which includes SSIS (SQL Server Integration Services) and SSAS (SQL Server Analysis Services).

First introduced in SQL Server 2005, Reporting Services has become one of the most important SQL Server subsystems, it enables you to design and run reports using data from SQL Server relational and Analysis Services databases. While it’s been incredibly useful since its inception, it hasn’t had many significant updates over the past few SQL Server releases. That has definitely changed with the SQL Server 2016 release. Reporting Services in SQL Server 2016 boasts some significant, and very welcome, new features.

Reporting Architecture

FIG 1: Reporting Services Architecture

What’s New in SQL Server 2016 Reporting Platform

• Unified reporting platform for all Microsoft Report Types.

FIG 2: Different Report Types

• New Web Portal Interface incorporates KPI’s, Mobile Reports, Paginated Reports, Excel and PowerBI desktop files.

FIG 3: SSRS 2016 New Web Portal layout

• Mobile Reports:SQL Server 2016 Mobile Reports provide one mobile application for Power BI and SSRS mobile reports. It has a native mobile experience that is optimized for touch, as well as the ability to keep mobile data refreshed with real-time queries or periodic scheduled data refreshes.

FIG 4: Mobile Report Layout & Authoring Tool

Users can also work with Mobile Reports, even when they are disconnected. The new SQL Server Mobile Report Publisher enables users to create and publish SQL Server Mobile Reports to the new Reporting Services Web Portal.

FIG 5: Mobile Report Developer App

• KPI’s:
  Single-item measures that are capable of giving an “at-a-glance” view of a single metric, e.g. Total Sales Year to Date.

FIG 6: KPI in SSRS Web Portal

KPIs can be linked to a published shared data source or to static data, updated through the Web Portal or created in the SSRS Mobile Report Publisher (formally known as the Datazen Publisher). The chart types available include static figures, lines, bars and traffic lights to offer a simple summary view of some key metrics.

Power BI

Power BI is a cloud based business analytical service that gives users a single view of the most critical business data. Power BI allow users to create rich visualization and interactive reports using a desktop application, they can access their data on the go with native Power BI mobile applications.

Advantages of Power BI:

• Power BI puts business intelligence creation into the hands of Analysts who can extract source data, create a dataset, transform or manipulate the data, visualize the data and publish the resulting reports and dashboards. For progressive or iterative analysis, Analysts can evolve the BI with new measures or dimensions without requiring IT involvement.
• The Question and Answer (Q&A) function may be the top cited benefit and capability in achieving self-service BI.

FIG 7: Power BI End to End Architecture

• The dashboard visualizations are best in class and continually updated from the community. Interactive geo-map visualizations are empowered by Bing Maps.
• The underlying Power BI technology is an in-memory analytics engine and columnar database that supports tabular data store structures used by Power Pivot. This achieves a balance between performance and ease of use (as compared to three dimensional cubes which require more complex assembly and query languages, such as MDX (multidimensional expressions) for SSAS).
• The DAX (Data Analysis Expressions) scripting language is a relatively simple construct used to create calculated columns and measures. It’s similar to Excel, although while Excel is cell based, DAX is column based.

Key features of SSRS 2016:

• Cross-browser compatibility. Until now, SSRS was only fully compatible with Internet Explorer and even then, only with specific versions. In SSRS 2016, reports render consistently across all modern browsers, including Internet Explorer 9 and later, Chrome, Safari, Firefox and Windows 10’s Edge.
• Report themes and granular control of presentation. Modern report themes and chart types are available, but more importantly, with support for CSS, Developers have more control over the look and feel of reports.
• Integration with Power BI. SSRS reports are accessible via Power BI, Microsoft’s rapidly growing cloud business intelligence platform.
• Better support for mobile devices. With support for iOS, Android and Windows Phone, SSRS reports render on a variety of mobile devices so users can interact with reports from just about anywhere.
• Parameters have major overhaul. Historically support for report parameters was fairly basic – think simple text boxes and drop down lists. In the 2016 release, report users have far more control over the visual layout of parameters.
• New look for Report Builder. Designed for power users as opposed to Developers, Report Builder continues to be part of Microsoft’s strategy to bring business intelligence to business users. This latest version sees a visual refresh in line with Office 2016.
• Subscription improvements in SSRS Including enable / disable / pause subscriptions, changing subscription owners, adding subscription descriptions, etc.
• New chart types – Tree Map and Sunburst.
• Custom parameters panel to add rows and columns to change the panel layout.
• PowerPoint rendering and export.
• Support of SharePoint 2016.

Why SQL Server 2016

Microsoft offers a wide range of tools for Extract, Transform and Load (ETL), Analytics, and Reporting, from enterprise as well as self-service perspectives. Previous versions of SQL Server & Report authoring tools raised questions and confusion in users, and clear use directions weren’t officially provided. With SQL 2016, Microsoft has posted a strong reporting roadmap (read about it in detail here).

Future for SQL Server 2016 and Self-service BI looks brighter as Microsoft continues to heavily invest in product updates & acquisitions of new firms.

Contributed by Arun Kumar, Senior BI Consultant at jEyLaBs