Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Parameters Functions


By:   |   Last Updated: 2015-12-23   |   Comments   |   Related Tips: > 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...

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.

Filter

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.

Territorycompare

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".

ArrayIndex

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

warning message

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


Last Updated: 2015-12-23


get scripts

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools