Customise Parameters Pane in SQL Server Reporting Services 2016

I will be writing a series of blogs that specifically focus on SQL Server Reporting Services (SSRS) – illustrating new features, new chart types, enhancements and general improvements. 

In this article, I will be looking at the new and improved Parameter Pane that was made available in CTP 2.4.

What is a Parameter Pane?

In short, this is a ‘drop down’ field in SSRS.  Any attribute that can be sliced, diced or filtered are strong parameter candidates for a dashboard report.  The screenshot below illustrates how the current Parameter Pane looks in in the current version of SSRS. 

Current Limitations

Although still a very powerful feature in SSRS, the current nagging issue is that developers have very limited control of where a parameter can be placed in a report.  This is always at the top of the page and reliant on parameter ordering in Designer.

It looks like Microsoft have finally listened to the SQL Community and finally rectified this.  Although it has taken what feels like forever, it is good news for any budding Reporting Services developer out there. 

New to 2016 CTP 2.4

I will now demonstrate how easy it is to customise your parameters in SSRS 2016.  As this is only available in CTP 2.4 and above, you will need to use Report Builder to make use of this functionality.

1.      Navigate to Report Builder through your configured SSRS web service and choose Blank Report.

2.      On the View tab, select the Parameters checkbox to display the parameters pane.  For simplicity, tick all of the checkboxes available.

3.      You will now notice a pane appears at the top of the design surface and looks like the below.

4.      Assuming you have a data source and data sets configured, to add a parameter to the pane, carry out the following:

a.      Right click an empty cell in the parameters pane, and then click Add Parameter.

b.      There are other ways to add a Parameter – click here to see these.

5.      To move a parameter to a new location in the parameters pane, drag the parameter to a different cell in the pane.  In my example, I have placed Country in the centre of the screen, but State and City left and right respectively.

6.      To access the properties for a parameter, there is now an alternative option.

a.      Right click the parameter in the parameters pane, and select Parameter Properties.

7.      To add new columns and rows to the pane, or delete existing rows and columns, right click anywhere in the parameters pane and then click a command on the menu that displays.  These options are shown in the above image but after adding 3 new columns and rows, the Parameter Pane now looks like the following:

The white grids clearly indicate where the new columns and rows are positioned.

8.      To delete a parameter from the pane and from the report, you can use the traditional methods, as well as:

a.      Right click the parameter in the parameters pane, and then click Delete.

9.      Now Run the report to visualise how the drop downs will show in a report.

10.   That is it.  You can now view the parameters.

Conclusion

Although this is a step in the right direction, the parameters are still limited to the top of an SSRS report.  However, you can develop custom workarounds, such as Excel-style Slicers – suggested by Simon Sabin in his blog. 

It is surprising that Microsoft have not come up with a fully configurable Parameter Pane as the current workarounds are not perfect.  I am hoping further enhancements are added before the full version of SQL Server 2016 is released. 

References/Future Reading

For more information on Report Parameters, I recommend the below resources/blogs:

·        Koen Verbeeck’s SQL Server Blog – https://www.mssqltips.com/sqlservertip/4088/customize-the-sql-server-reporting-services-2016-parameter-pane/

·        MDSN Blog (Customize the Parameters Pane) – https://msdn.microsoft.com/en-GB/library/mt574039.aspx

·        MDSN Blog (Chang Parameter ordering) – https://msdn.microsoft.com/en-GB/library/dd255258.aspx

·        Simulating Slicers in SSRS Reports – http://sqljason.com/2012/07/simulating-slicers-in-ssrs-reports.html

Look out for future blogs on SSRS2016; one particular interest is Report Builder, which Microsoft have apparently greatly improved. I have yet to fully dissect all of the new features so we will see whether the changes are significant enough to improve on the poor adoption seen previously.