By: Koen Verbeeck | Comments | Related: > Reporting Services Overview
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.
When we query the log, we can observe that there are extra rows logged for the mobile reports executions:
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:
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.
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.
The List View also has checkboxes, so you can easily delete or move multiple items at once.
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.
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.
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
- Read more about service pack 1 in the tip New Features in SQL Server 2016 Service Pack 1.
- For more SQL Server 2016 tips, you can use this overview.
- More SQL Server Reporting Services can be found here.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
