Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Customize the SQL Server Reporting Services 2016 Parameter Pane


By:   |   Read Comments (1)   |   Related Tips: More > SQL Server 2016

Attend a SQL Server Conference for FREE >> click to learn more


Problem

At the time of writing, SQL Server 2016 preview CTP 2.4 has been released. A new feature of SQL Server Reporting Services (SSRS) 2016 introduced in this preview is the ability to customize the parameter prompt pane of a report. This tip will give an introduction to this new feature.

Solution

SQL Server 2016 preview

As mentioned earlier, at the moment of writing SQL Server 2016 is still in preview (currently CTP 2.4 has been released). This means that the look and feel of Reporting Services and its features can still change and that functionality might change, disappear or added.

Another issue is that the current release of SSDT-BI (the Visual Studio shell for developing business intelligence solutions) for SQL Server 2016 only supports Integration Services projects. You can develop BI solutions for SQL Server 2016 preview using SSDT-BI 2013 (which is actually for SQL Server 2014), but it has only SQL Server 2014 features. So how are we going to test new SSRS functionality? Luckily Report Builder has already been updated and included in the SQL Server 2016 CTP 2.4 release, so in this tip we’ll use Report Builder to test the new parameter pane.

In order to use Report Builder, you need to install SSRS 2016 CTP 2.4 (or later), preferably not on your production machine. Then you navigate to Report Manager - typically the URL has the format http://myserver//Reports - where you can launch Report Builder.

Launch Report Builder

The New Parameter Pane

In previous versions of Reporting Services, you had no control over how the parameter pane looked like. Parameters were shown in the order in which the parameter were defined and that's the only influence you could have. The old set-up looked like this:

old parameter pane

With the new parameter pane, you get a grid where you can add and place parameters where you want. But first you have to enable this grid. You can do this by selecting Parameters in the View tab of the ribbon.

enable parameters

The parameter grid will be added to the top of the report canvas.

enable parameters

Adding Parameters

You now have multiple options to add a parameter to a report. You can do it as in previous versions, by right-clicking the parameters folder in the Report Data pane.

old-school add parameter

The new parameter will automatically be added to the grid.

New parameter to the grid.

You can also add a new parameter by right-clicking any cell in the parameter grid.

Add a parameter to the grid

The new parameter will be added to the parameters folder as well. The last option is to add a parameter in a data set query.

Add a parameter to a query

The parameter will automatically be added to the parameters folder, but not to the grid.

Parameter MIA

When you run the report, you receive an error explaining there is a mismatch between the parameter panel and the number of parameters.

Parameter Error

This might be an issue in the current release and be fixed in another release. For the moment, you can solve the error by dragging the missing parameter from the folder to the grid.

Dragging to the grid

When parameters are automatically added to the grid, they are added to the top left corner of the grid when it is empty. If not, the new parameter is added to the right of the parameter on the most bottom right position. An example:

Dragging to the grid

If there is no place left to the right, the parameter will be added to a new row.

Customizing the Parameter Pane

As in any table-like structure, you can add new columns and rows or remove them.

Add columns or rows

At the moment, there seems to be a maximum of 8 columns, but the number of rows appears not to have a limit. In the grid, you can move parameters to any position by dragging them to the desired cell. If there is already a parameter in that cell, the parameters swap positions. Keep in mind that changing the position of a parameter might impact the order of the parameter list.

Let's illustrate with an example.

Before...

When we switch the parameters on the grid, we can see the ordering has also changed.

... and after.

Some other observations:

  • In the grid, the cells with a grey background are the ones that will be displayed when the report is displayed.
  • Removing a row or column will also delete all associated parameters on that specific row or column.
  • For the moment it's not possible to insert other objects in the grid such as text or images. It's not clear if this will change.

Conclusion

The new parameter pane in Reporting Services 2016 is a step in the right direction for customization of how parameters prompts are displayed in reports. However, the options are quite limited: you can only change location in the grid.

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
Related Resources





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     



Tuesday, November 17, 2015 - 8:10:07 AM - Jon Back To Top

Amazing Amazing!


Learn more about SQL Server tools