Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

What's New in Reporting Services 2016 Service Pack 1


By:   |   Read Comments   |   Related Tips: > Reporting Services Overview

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

SQL Server 2016 was been released the first of June 2016, but at the recent Connect event Microsoft announced the first service pack! Aside from numerous fixes, this service pack also comes packed with some notable enhancements for Reporting Services. This tip gives you an overview of these enhancements.

Solution

Aside from Reporting Services, there are many reasons why this service pack is very important. First of all, a lot of Enterprise Edition features such as partitioning, columnstore indexes, in-memory OLTP and many other are now available in the other editions of SQL Server. This makes the Standard Edition a lot more powerful and it gives you a compelling reason to upgrade to SQL Server 2016. If you already have SQL Server 2016, you can find the service pack here. Aaron Bertrand also lists various enhancements in the tip New Features in SQL Server 2016 Service Pack 1.

SSRS Execution Log for Mobile Reports

With service pack 1, there is now more information logged in the Execution Log in the SSRS database. Before the upgrade, only information about the shared datasets - if the mobile report used one - was logged.

SSRS Execution Log for Mobile Reports

When we query the log, we can observe that there are extra rows logged for the mobile reports executions:

Execution Log Results in SSRS

Interesting to note is that for the WWIDW_Orders Dashboard, the log contains three rows:

  • One row for the rendering of the mobile report itself. All counters are zero, so this row is just there to log that this specific mobile report has run. There's a specific action called RenderMobileReport.
  • One row for the execution of the shared data set WWIDW_Orders2016, which is used as a data source for the mobile report. Here, we can find  the number of rows and the time it took to fetch the data from the database and process it. The bytecount is zero.
  • One row for the rendering of the JSON of the shared data set. The row count is repeated, but this action has its own data retrieval, processing and rendering time. If we add this up with the execution of the shared data set (the last row in the screenshot), we can see that this mobile report has quite some performance issues.

The TestReport mobile report is a report that only uses sample data. This means there is no execution of a shared data set.

Enhanced SSRS context menu

A frequently asked for feature are better context menus in the new SSRS portal. Before the service pack upgrade, you had to right-click an object and go to the Manage screen. There you could choose for other options, such as setting security or edit the object in its editor (such as Report Builder or the Mobile Report Publisher). Now the most important actions are directly available in the context menu when you right-click on an object. For example, when you right-click a mobile report you get the following context menu:

Mobile Report Context Menu in SSRS

Now you can skip the Manage screen and go directly to the Mobile Report Publisher. For paginated reports, you can go to Report Builder, view the history snapshots or perform other maintenance tasks.

Paginated Report Context Menu in SSRS

By clicking on the three dots in the top right corner you will also pop-up the context menu.

SSRS List View

One of the questions in the What's new for SSRS 2016 webcast was about the missing list view in the new SSRS portal. The service pack finally introduces the list view back into the portal.

List view in SQL Server Reporting Services

The List View also has checkboxes, so you can easily delete or move multiple items at once.

List view details in SQL Server Reporting Services

Direct Navigation for KPIs in SSRS

The last addition is the ability to set a direct navigation property for a KPI. This means that instead of having the big KPI pop-up, you go directly to the related content when you click on a KPI.

Direct Navigation for KPIs in SSRS

Important to notice is that this currently only works with Custom URLs and not with mobile reports as related content. Luckily you can easily work around this limitation by specifying the URL of a report. This means you can link also to paginated reports for example. When direct navigation is configured, it will be shown in the KPI with the a little link icon.

Direct content icon in SSRS

Conclusion

With the first service pack of SQL Server 2016, you get some nice enhancements for Reporting Services which makes it worthwhile to upgrade your SQL Server 2016 instances. There are many more enhancements in SP1, such as the release of many Enterprise Edition features into the other editions of SQL Server.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools