Customize the SQL Server Reporting Services 2016 Parameter Pane

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


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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Koen Verbeeck 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



Comments For This Article




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

Amazing Amazing!















get free sql tips
agree to terms