SQL Server Reporting Services Series – Part III: Parameters

This is the third post in a series about SQL Server Reporting Services (SSRS). I will be picking up where we left off in the project that was created in Part 2.

One of the other important topics of SSRS is adding parameters to a report which lets you filter the data down to a smaller group. As with many things, parameters can be simple selections or can be more complicated with cascading selections that depend on other parameters. We’ll keep things relatively simple here.

Reviewing what the report looks like now, we have the Sales YTD and Last year by rep grouped by their country.

What we want to do now is to add the ability to filter the report. We want the ability to see all countries or just a single country’s data. The place we need to start is the query in our Shared Dataset (SalesPersonData.rsd).

Query Updates

Right now the only filter is to now pull any of the sales reps that are not part of a territory. We need to add a couple more expressions in the where clause to allow us to filter the data more.

SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix, JobTitle, PhoneNumber, PhoneNumberType, EmailAddress, EmailPromotion, AddressLine1, AddressLine2, City, StateProvinceName,PostalCode, CountryRegionName, TerritoryName, TerritoryGroup, SalesQuota, SalesYTD, SalesLastYear FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL

The CountryRegionName will be what is filtered on and it will be set to the name of the country we want to view. In the actual query we won’t know what that name will be so we need to set a placeholder. The format for placeholders is the @ symbol and a meaningful name. I’ll go ahead and use @CountryRegionName

CountryRegionName = @CountryRegionName

The other check we will need to make is, if the value that gets passed to the query is empty then it means we should display all countries. The TerritoryName check should be applied regardless of the country selected so the full WHERE clause will end up like this:

WHERE TerritoryName IS NOT NULL AND (@CountryRegionName = 'All' OR CountryRegionName = @CountryRegionName)

When you update the Dataset query select the Refresh Fields button and then in the side navigation for the Dataset select Parameters.

SSRS recognized that we have a placeholder in the query and want a parameter option for the report. Here we can set various options including the data type and if it can include multiple values. Using multiple values, our query would have to use IN instead of the equality sign.

Set the data type for the parameter as Text.

Now we need to update our report in a similar manner. In the Report Data window open the SalesPersonData properties. On the main window select Refresh Fields and select OK.

In the Report Data window you should now see a CountryRegionName parameter under the Parameters folder. Opening the properties for it you can see that it kept the data type of Text that we set in the Dataset.

Closing this window, opening back up the Dataset properties and going to the Properties tab in the navigation you will see that the parameter that was written in the query is now set to match up with the new Parameter Value. In more advanced scenarios you can set this to a specific value or, by using the function (fx) button, an expression.

If you preview the report now, you will notice a new area at the top with the label Country Region Name and a textbox. This is where we can enter in the information we want to filter on. Enter in the text United States and select the View Report button on the top right (my screenshot has some bad text coloring) to see the report filtered to only this region.

While it is great that we have the filtering working, it would be very cumbersome to have to remember each region in order to filter the report. Which is why there is the ability to set selectable options for the parameter and a default option.

Available and Default Parameters

Back in Design view, in the Report Data window, open the properties for the CountryRegionName parameter. A couple of the navigation options on the side are Available Values and Default Values. Selecting Available Values you can see the options for having no values, which is the default; you can specify your own values, including the Label and Values for the options; and you have the option to get the values from an existing dataset query.

While we know now what the available options for the Country Region will be, it is unknown whether these will change in the future so the query option will be a better choice for the report.

The first step is to create a new Dataset that just pulls back the country regions. (This would be a good candidate for a Shared Dataset because it would have a high probability of being used for a parameter in multiple reports)

Checking the Person.CountryRegion table, there are many more options there than what is actually in the report so we will build our list off of the same View as the main Dataset. The first select statement also UNIONS a row with the value of ‘All’ that will be used to display all of the regions.

Select 'All' AS CountryRegionName UNION Select DISTINCT CountryRegionName FROM Sales.vSalesPerson

After adding the new Shared Dataset (I named mine SalesPersonCountryRegionList) to the report we can go back and update the parameter’s available values.

We can also set the default to ‘All’.

Now the report will default to all regions displayed…

and when selecting a specific region and clicking the View Report button it will filter the data shown.

Conditional Visibility

One last touch up to the report is the Grand Total data on the filtered views has become redundant so we just want to hide it if the report is filtered.

In Design View, right-click on the thick border next to the Grand Total row; one of the menu items is Row Visibility. Select that item and we will want to Show or hide based on an expression. Select the fx button to open the Expression window.

We need to write an expression that when true will hide the row. The parameter will be our point of reference for the expression and we can add it to the expression by using the GUI at the bottom of the screen or by writing the appropriate syntax. (If you type it out, do not delete the equal sign ‘=’ that has been set).

Parameters!CountryRegionName.Value

We want the row to be hidden when the report is filtered and we know that all the countries will be displayed when the ‘All’ option is selected so we can use:

Parameters!CountryRegionName.Value <> "All"

Now the Grand Total does not show when filtered by a specific region.

Wrap up

Over these past few posts we have gone through some of the basic, but important, concepts for building SSRS reports. There are of course many more topics that could be covered so let me know if there is anything else you would like covered in the comments.