SSRS 2016 – Part 2 New Features for Paginated Reports

This second post of 3 on SSRS 2016 looks at the new features for the SSRS portal and the paginated reports using the Community Technical Preview (CTP) 3.3.

Post 1 covered the basics of creating your first paginated report.

Post 3 will cover the new Mobile Reports and KPI features.

 

Portal Improvements
The portal has had a complete overhaul to make it HTML 5 and CSS3 compatable to support browsers other than IE. No more ActiveX! So far there are only a few new features. One of which is the ability for users to add reports to a Favorites tab for quick access. I guess this is better than using your browser’s bookmark functionality. Another is that it is now possible to export reports to Power Point.

It is now possible to name, enable and disable subscriptions from the portal.

Controlling sunscriptions from the SSRS portal

As of CTP 3.3 this is only possible in the old portal.

Parameter positioning
Anyone who has used SSRS in the past has probably been slightly frustrated with the lack of control for parameter positioning. It was possible to have some control by manipulating the ordering of the parameters, but for 2016 we have a new interface to define the positioning. It’s basically a grid onto which parameters, along with their labels, can be placed.

To see this new feature let’s add a parameter to filter the report by database. Add another data set to the report, created in the last post in this series, but this time use the query below.

SELECT database_id AS DatabaseID,
name AS Name

FROM sys.Databases

ORDER BY database_id;

This data set will populate the drop down of our parameter.

Creating a dataset in SSRS 2016 for a parameter

Right click parameters and select add a new parameter. Give it a name and move on to the Available Values section. We could leave this as it is, which would require the users to type the database they want to see the status of, but a drop down would be much better.

Connecting a data set to a parameter in SSRS 2016

Then edit our Databases data set from the previous post to add the WHERE clause on the parameter value. This will filter the result set of the query by the parameter.

SELECT database_id AS DatabaseID,
name AS Name,
create_date AS CreatedDate,
state_desc AS [State]

FROM sys.Databases

WHERE database_id = @Database

ORDER BY database_id;

To position our new parameter tick Parameters in the View ribbon menu. This will open the Parameter grid on which you can drag the parameter into position.

Positioning parameters in SSRS 2016

Run the report to test the new parameter.

A drop down menu in SSRS 2016

 

Visualisations

There are two new visualisations for paginated reports in SSRS 2016.

To test these new visualisations, I will be creating a new report and using the data I imported in my previous post Importing open data sets in SQL Server as the data source. The query below will produce my data set.

;WITH cte_Top10Agencies
AS
(
  SELECT	TOP 10
      Agency
      ,SUM([Likes Followers Visits Downloads]) AS Likes

  FROM	[OpenDatasets].[dbo].[NYC_Social_Media_Usage] 

  GROUP BY Agency

  ORDER BY [Likes] DESC
)

SELECT	smu.Agency
    ,[Platform]
    ,DATEPART(YEAR, [Date Sampled]) AS YearSampled
    ,DATENAME(MONTH, [Date Sampled]) AS MonthSampled
    ,SUM([Likes Followers Visits Downloads]) AS Likes

FROM	[OpenDatasets].[dbo].[NYC_Social_Media_Usage] smu
JOIN	cte_Top10Agencies t10 ON smu.Agency = t10.Agency

GROUP BY	smu.Agency
      ,[Platform]
      ,DATEPART(YEAR, [Date Sampled])
      ,DATENAME(MONTH, [Date Sampled]);

From the Insert ribbon menu click Chart and select Insert Chart. Drag out an area for the chart in the designer. The next step is to select a chart type. We will be concentrating on the new Sunburst and Treemap visualisations.

Selecting a chart type in SSRS 2016
I want to count the social media ‘Likes’ each agency has grouped by month and then year.

Selecting fields for a chart in SSRS 2016

To make the data the chart is displaying easier to understand, I set the labels to be visible.

Set labels to be visible in SSRS 2016

Tree Map

I couldn’t get the labels to display the year and the month while putting this post together. I think it is possible as I remember building custom label values, containing multiple fields, with expressions in previous versions of SSRS. Give me a shout if you have this working.

The Tree map visualistation in SSRS 2016

Sunburst

Right click the chart, select Change Chart Type and select the Sunburst visualisation. IMO this chart shows this particular data set in a much more useful way.

SSRS 2016 Sunburst visualisation

 

TL;DR

There are some nice additions here but for me there are only 2 reasons to upgrade to SSRS 2016:

  1. Reports render in HTML 5. Ever had your boss ask why the report you spent hours on doesn’t load in Safari?
  2. Datazen dashboards are stored and viewed from SSRS – no longer do you need to install and maintain the Datazen server.

Datazen is no more and these dashboards in SSRS are referred to as Mobile Reports, but more about that in the next post.

There are still some basic features I’d like to see in the paginated report designer like being able to format multiple text boxes at once.

So that covers the changes and additions to what was already in SSRS. In the next post I will be discussing the whole new world of Datazen Mobile Reports.

About the author

James Anderson

I'm a freelance SQL Server DBA in Southampton, UK. Performance tuning code and improving development processes are what I love to do for work. Outside of work, I live to travel to as many places as possible with my wife Sarah.

3 thoughts on “SSRS 2016 – Part 2 New Features for Paginated Reports”

  1. One of the key things I would like to see in SSRS 2016 is PARAMETER VALIDATION. Simple validations, like being able to put a check on your EndDate parameter to insure that it’s actually AFTER the StartDate. “Use a drop down” some mook will say. Well, I HAVE used drop downs for date parameters (MDX datasets running against cubes) and all I can say is “they suck.”

Leave a Reply

Your email address will not be published. Required fields are marked *

Sign Up

James Anderson SQL Server DBA

Hi, I'm James Anderson. When I'm not blogging about SQL Server, I do this.

DBA Stackexchange Profile