Starting with SQL Server 2016, a huge number of upgrades have been performed to the freely-included reporting toolset. In the past, when I taught SQL Server Reporting Services (SSRS) and creating reports using either Report Builder or the Report Designer in SSDT, I was often asked by students how they can have greater control over how the parameters were displayed in the report to encourage users to easily enter or select data before viewing a report. Frustratingly, Microsoft has not allowed us to ever change how the SSRS parameters appear at the top of the screen. Even though parameters were introduced eleven years ago with SSRS 2005 (not counting the SQL Server 2000 SSRS add-on), not much has changed with them – until now with SQL Server 2016!

To be clear, we report developers could always arrange the order in which the SSRS parameters appeared at the top of the report. Heck, the correct order is especially required when we use cascading parameters – parameters that show values based on previously selected parameters. However, many times developers have wanted to change the displayed layout, making the report more intuitive. Microsoft listened and has finally responded accordingly!

In all earlier versions of SSRS, all parameters were laid out automatically across the top of the screen, left to right. Only when SSRS parameters were too long or if there were too many of them would it be forced to wrap them to a new row at the top of the screen.

For example, the report below was created in BIDS 2008 for SSRS 2008 R2. It has the SSRS parameters laid out horizontally in three (3) rows.

The order of the SSRS parameters being displayed is determined by their order in the Parameters folder in the Report Data window shown in the BIDS/SSDT Report Designer or Report Builder – typically located just to the left of the report. The order is set by selecting a parameter and then by clicking one of the two arrows at the top of the Report Data window.

In the past, students have often asked for greater control over how the parameters appeared at the top. I would shamefully look down, kick at the dirt embarrassingly, and tell them there was nothing they could do.

As shown below, SSDT/BIDS and Report Builder did not offer parameter layout in the Report Designer. This has changed as you shall soon see.

Starting with SSDT 2015 and Report Builder for SQL Server 2016, when you create a new empty report, you will be greeted by a 4 x 2 grid-style layout for the parameters at the top of the designer. Not only will you be able to drag and drop parameters on the grid, you will also be able to rearrange them. As well, you can also add or remove rows and columns.

As shown, you can right-click on a cell to insert and delete rows and columns. If you need additional empty space, you can even add empty rows or empty columns (without parameters). The layout of the parameters is entirely up to the report developer!

Here is an example I put together for displaying products available in the prehistoric Northwind database. Note that I included a few empty rows to add more space. When I initially opened the report in Visual Studio, scroll bars appeared to the right and bottom of the SSRS parameter design area.

For the scroll bar at the bottom of the SSRS parameter window, I just need to maximize SSDT! For the right scroll bar, I will grab the parameter splitter bar at the top of the report and drag it downward to see all the parameter rows, as shown below.

With it stretched out and maximized, my report now looks like this in the SSDT Report Designer.

I can also view and edit this report with Report Builder. Note that the Parameters pane is not shown by default in Report Builder. That can be changed by simply clicking a checkbox on the View tab.

As you can see, I have five (5) SSRS parameters shown at the top of the Report Designer. Okay – with all those wasteful blank rows and unnecessary extra columns, this looks ridiculous. That’s okay – I wanted to show you what it can do. Here is the report shown on the Preview tab of SSDT. I used an arrow to show that the user will be offered the same parameter window splitter bar to preserve screen real estate. Bummer – as in previous versions, you cannot reposition or alter the View Report button in any way.

Notice how the blank parameter rows remained in case I needed the additional space.

BTW – there were a couple of cool tricks that I used in this report. Let me show them to you.

First off, the Suppliers parameter is a multivalued parameter. The user can select as many suppliers as they want for the report, including all suppliers as shown below.

I was able to determine and display the number of selected suppliers at runtime. As well, I was able to display the text “ALL 29 SUPPLIERS SELECTED” if the selected supplier count matched the total supplier count. There happens to be 29 suppliers in the database. The number ”29” is not “hard-coded” in the report.

Here is another screenshot showing the count of selected suppliers:

The two (2) properties that I compared were:

Parameters!ProductSuppliers.Count = The number of suppliers selected

Count(Fields!SupplierID.Value, “DSTSuppliers”) = The total number of suppliers returned in the DSTSuppliers dataset

Here is the entire expression. I could have shortened this expression by placing the Count() function in a custom function in the Report Properties Code window.

= IIf(Parameters!ProductSuppliers.Count = Count(Fields!SupplierID.Value, “DSTSuppliers”), “ALL ” & Count(Fields!SupplierID.Value, “DSTSuppliers”) & ” SUPPLIERS SELECTED”, Parameters!ProductSuppliers.Count & ” SUPPLIERS SELECTED:” & vbCrLf & Join(Parameters!ProductSuppliers.Label, “, “)) & vbCrLf & “CATEGORY: ” & Parameters!ProductCategories.Label

Secondly, you may have noticed that my example had a couple of rows highlighted in pink. If you study those highlighted rows carefully, you’ll see that the “Units in Stock” values are less than the “Reorder Level”. In the business world, it means that these products need to be reordered from the suppliers as soon as possible.

I only wanted to highlight these rows if the two columns were being displayed. Note that one of the report parameters offers the user the choice of displaying these two columns. The best way to do this is to select each entire column (one at a time) on the Tablix data region, then right-click it, and select Column Visibility…

I selected to show/hide the column based on an expression, as shown below.

I then entered the following expression based on the Boolean parameter’s value.

Warning: Do not select the column and then set the Visibility directly in the Properties window – it doesn’t work very well. It will hide the columns just fine but it will not slide the remaining columns together. It will look like your report is missing teeth!

I highlighted the Detail row and went to the Properties window. I set an expression for the BackgroundColor property to check the parameter and the two values. Here is the full expression:

=IIf(Parameters!DisplayStockAndReorderLevel.Value And Fields!UnitsInStock.Value <= Fields!ReorderLevel.Value, “Pink”, “White”)

By the way, with all these SSRS parameters, including a multi-valued parameter and the two price range parameters, I had to write a SQL query to handle it. I used an ad hoc query with the IN and BETWEEN operators. Here it is:

SELECT
Products.ProductID, Products.ProductName, Products.SupplierID, Products.CategoryID, Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock, Products.UnitsOnOrder, Products.ReorderLevel, Products.Discontinued, Categories.CategoryName, Suppliers.CompanyName AS SupplierName
FROM
Products
INNER JOIN
Categories ON Products.CategoryID = Categories.CategoryID
INNER JOIN
Suppliers ON Products.SupplierID = Suppliers.SupplierID
WHERE
(Products.CategoryID = @CategoryID)
AND
(Products.SupplierID IN (@SupplierID))
AND
(Products.UnitPrice BETWEEN @BeginPriceRange AND @EndPriceRange);

Note that it is more difficult to use a stored procedure with a multivalued parameter. It’s not impossible but more difficult because you must pass in an array of multiple values into a single stored procedure parameter and then parse out your array inside the stored procedure. It’s been done and you can find code examples on the web if you insist on using stored procedures.

There is nothing revolutionary about these last few tricks. I thought they were kind of cool and simply wanted to share them.

I will be “reporting” more new features of SSRS 2016. There is so much more that has been added!

Best wishes and happy reporting!