Reporting Service 2016 Unplugged
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.
Aside from a handful of new features, generally, the report design experience is unchanged. Things got a bit confusing in SQL Server 2014 when the database project template and the Business Intelligence project templates were both given the name “SQL Server Data Tools”. At the same time they came in separate download packages for different versions of Visual Studio. This confusion gets sorted out in the new product version; currently, the SSDT package now includes the whole kit and kaboodle: database and all BI project templates. In the near future, SSDT will be managed outside the SQL Server installation so you should be able to use any version of Visual Studio without worrying about which SSDT package to download and install.
Report Builder is updated with a modern look-and-feel, simple and sleek. Changes are mostly cosmetic while the fundamental features are the same.
Integrated and Improved Report Portal
In addition to Report Manager (the ASP.NET web application used to access, run and manage reports in the web browser), a new report portal web interface is introduced . 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. Here is a look at the initial page and display options in my test environment:
Report Portal works in the CTP 3.2 build including some menu items as placeholder for future completion. My experience has been a bit rough so far. After system restarts, I have had to disable then re-enable the portal. This is accomplished by removing and re-adding the port 80 assignment for Report Manager in the Reporting Services Configuration Manager.
The image from the official Microsoft SQL Server Blog shows the Report Portal hosting KPIs, mobile dashboards, SSRS paginated reports.
The addition of mobile dashboards to the SSRS platform is based on the Datazen product acquisition from ComponentArt last year. To promote a good experience with mobile dashboards, it is important to manage expectations about the strengths and limits of this compelling feature. Mobile dashboards are primarily designed to enable data interactivity in dashboards created by a dashboard developer. Dashboards 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.
The SQL Server Mobile Report Publisher public preview is currently a separate download which can be obtained from here. Last year 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 stand-alone product - free to SQL Server Enterprise customers - but any future enhancements are likely to only take place in the new integrated platform. The essential design experience is pretty much 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.
New KPI delivery is also based on the Datazen acquisition. These KPI visuals are created and managed entirely within the new Report Portal. In additional to the standard traffic light style comparison of actual vs target values, KPIs can include a trend line or segment chart.
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.
These are cool and useful in present form but, in future iterations, I am hoping for a few more knobs and levers controlling features like text size by level, and labeling by group intervals. This is a good start.
Standardized, Modern Browser Rendering
At first, you may not notice significant changes from previous versions, but the a 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.
Now, 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. Now 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.
Power BI Dashboard Pinning
For organizations who have invested in the Power BI cloud service, this nifty feature allows users to pin graphical SSRS report visuals to their online dashboards.
Requirements to enable this feature:
- Register the report server with a Power BI subscription
- SQL Server Agent must be running on the database server with the report server catalog (this is always been an SSRS requirement.)
- 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.
Items are highlighted and 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.
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.
SQL Server Mobile Report Publisher, Microsoft
How to create Mobile Reports and KPI’s in SQL Server Reporting Services 2016, Chris Finlan
How to create a mobile dashboard solution with Datazen, SQL Server Pro
Microsoft Business Intelligence Roadmap, Microsoft SQL Server Blog