By: Scott Murray | Comments (3) | Related: > Reporting Services Parameters
Problem
What are some of the functions in SSRS that allow me to interact with parameters?
Solution
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...
- Working With Multi-Select Parameters for SSRS Reports
- SQL Server Reporting Services Using Multi-value Parameters
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.
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.
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.
As you can see in the below resulting report, we selected 3 values in the parameter, but only 2 had data.
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.
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.
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.
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.
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.
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!
Conclusion
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
- Check out these resources:
About the author
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
