SQL Server Reporting Parameters Functions

By:   |   Comments (3)   |   Related: > Reporting Services Parameters


What are some of the functions in SSRS that allow me to interact with parameters?


How often do you interact with parameters in SSRS and need to complete some special work on those parameters. This process goes way beyond the normal issue that many report developers face dealing with multi-value parameters that has been covered in these tips...

In this tip, we will explore a few methods for examining and interacting with the parameters in your report server reports.

We will use the AdventureWorks databases as the basis for our SSRS report development. The 2014 version of the regular databases are available on Codeplex: https://msftdbprodsamples.codeplex.com/releases/view/125550. Once you download and install the SQL Server databases, we will subsequently use SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio 2013 to develop reports. You can download SSDT-BI from: http://www.microsoft.com/en-us/download/details.aspx?id=42313.

Parameter Interaction

When you first start dealing with parameters, you are almost always concerned with filtering your datasets or setting a sort order. However you can do so much more. One of the first items you can use is the count function which displays the total number of values selected in the parameter list. I find this functionality to be very handy in two instances: 1) for checking if all the values were selected when dealing with a multi-value parameter set and 2) if you want to parse out the list of values being, the total count is helpful to know.

For our example report, we are going to use a Sales by Region report sourced from the AdventureWorksDW. Our first step is to create a dataset based on the DimSalesTerritory table; this step is shown below. You may be curious as to the need for the TotalSalesTerritories CTE; I will discuss the need for this CTE and related fields later in the tip. Otherwise, we are just querying the table to get the territory list.

Param Query

Then we create a parameter using this data set, pSalesTerritory in the below example. Specifically the SalesTerritoryKey is the value field and the SalesTerritoryRegion is the label. We also checked the allow multiple values option.

paramter 1

param 2

Subsequently, we create a simple tablix with Sales Territory Region on the rows, CalendarYear on the column, SalesAmount in the data field, and a filter on the tablix by the SalesTerritory value. Notice we use In as the Operator, since the pSalesTerritory parameter is multi-value. Next we add a footer row for us to keep track of the parameter count. The Syntax is: =Parameters!ParameterName.Count or Parameters!pSalesTerritory.Count in our example.


Simple count

As you can see in the below resulting report, we selected 3 values in the parameter, but only 2 had data.

parameter count 1 result

The count value could be useful in situations where a particular value selected in the parameter does not contain data. As illustrated next, we compare the distinct row count from the Sales Territories in the row group versus the count of selected parameters.

row count vs data avail

So in the below results we see that of the 4 selected values in the Sales Territory parameter, only 3 of the Sales Territories have data for 2010 and 2011.

selected count

Another common use I have for the count function is determining how to display the parameter list in the report heading. If your parameter list has just a few items, this situation may not surface, but as soon as you have 5 or 10 (or more) items in your selected parameter list, the list can get a little unwieldy, especially if report users normally "Select All" values in the list. This normally results in a very long title similar to what is shown below.

select all

We can get around that long title by comparing the parameter count against the total number of territories in the parameters list. As shown in the first illustration in this tip (repeated below), you may remember seeing the CTE which precedes the main part of the parameter. We use the CTE to get the total number of regions that are possible to be selected.

parameter query

Now we can use an IIF statement, shown below, which compares the parameter selected count versus the total number of territories in the parameter pick list. If the numbers are equal, then instead of using the join function to concatenate all the selected values, we just display "All Regions". Otherwise, the join function is used to string the values together, each separated by a comma.


The above statement results in the following report, when all parameter values are selected.

All values selected

One other method of interacting with parameters is to actually look for specific values that are selected in the parameter list. This type of review could be useful, for instance, if we need to display a special warning if a certain parameter is selected. In order to interact in this manner, we need to use the Array.IndexOf function. In actuality, the parameter list is setup as two arrays, one for the parameter labels and one for the parameter values, so we can use the Array.IndexOf function to iterate through the array list looking for a value. The basic syntax is: Array.IndexOf(ArrayToReview,Value to look for); the parameter array is 0 based, so the first position in the array is position 0.

For our example, shown below, we use the Array.IndexOf function to look for "Germany" in the label parameter array. We wrap that function within an IIF statement which says if the result of the Array.IndexOf function is greater than -1 (remember it is 0 based), then we have found Germany, and thus we need to display the warning message about Germany sales. Otherwise we display "None".


This function results in the below warning message for the Germany sales figure. Pretty neat trick!

warning message


In this tip, we covered some basic ways to interact with a SSRS parameter list. Most folks experience the issue of passing multi-value parameters to stored procedure; however, we can interact in even more ways with a parameter list, which is just an array of values or labels. We reviewed using the parameter count feature to determine the actual number of parameters selected by the user running the report. The parameter count can then be compared against the actual number of rows within a tablix or we can also evaluate the count versus the total number of available parameter values. Finally, we used the Array.IndexOf visual basic function to check to see if a particular parameter label (or value) was selected during report execution.

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 Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

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

Thursday, February 3, 2022 - 12:30:53 PM - Michael Satterwhite Back To Top (89733)
Thanks Scott. I've reported off of the ReportServer tables before. But, I'm not sure how to get the actual name of the subscription that the report is executing. There are 3000+ subscriptions that are all using a Shared Schedule. Do you know how I can capture that from within the report?

Thursday, February 3, 2022 - 9:37:28 AM - Scott Murray Back To Top (89731)
I would take a look at this tip to create a query that looks at the subscription table noted in:

Wednesday, February 2, 2022 - 4:10:18 PM - Michael Satterwhite Back To Top (89725)
I'm seeing so many great tips on this site that I have no doubt someone on MSSQLTips can answer my question.

Is it possible to include the name of a user's subscription in a report? I frequently get questions from users and all they send me is the PDF output from a subscription. It would be great if I could include the name of the subscription in the report. In this case, there are over 3000 subscriptions for a single report, so having the name of the subscription would be extremely helpful.

get free sql tips
agree to terms