Using the JOIN Function in Reporting Services

The JOIN() function in SQL Server Reporting Services is a handy tool that allows you to turn a list into a delimited string value. This function accepts two parameters, a list and a delimiter with which to separate the output, and returns a string with that list separated by the specified delimiter character.

Using the JOIN Function in Reporting Services

One of the most practical uses for using the JOIN function in Reporting Services is to present a list of selected parameter values in an easy-to-display format. When building SSRS reports, it is very common to show the selected parameters on the report itself. For parameters that allow multiple values, the JOIN function is the easiest way to do that.

To demonstrate, I’ve set up a simple SSRS report that allows the selection of one or more categories, and then shows a list of sales for those categories. As shown below, that string-typed parameter allows the report user to select one or more categories.

As shown, the report user must select at least one parameter, and may optionally select many (or all) of the values.

To display a single, scalar parameter value on a report is fairly simple, but for multivalued parameters such as this one, one would need to use the JOIN function to pivot that set-based list into a delimited list of string values. Using the JOIN function is fairly simple: pass in as parameters the list (in this case, the parameter values) and a delimiter, and it returns a string value. For the variable I set up for this example, the syntax looks like this:

JOIN(Parameters!pCategory.Value, “,”)

Using the output from this function, I’m going to modify the report so that the selected parameter values are shown in the report. I’ll add a new text box and use an expression to set the value of that text box as shown below:

=”Categories: ” & JOIN(Parameters!pCategory.Value, “,”)

Setting the new text box to show the value of the expression yields the following.

That’s almost what I want, but not quite. Since I specified the parameter values rather than the descriptions, the ID values are being shown in the list. One quick modification to the expression to replace Value with Label makes the output more useful.

=”Categories: ” & JOIN(Parameters!pCategory.Label, “,”)

Now the report properly shows the labels rather than the ID values:

Summary

The JOIN function in Reporting Services allows for pivoting list data into a concatenated string value, which is particularly valuable when displaying multiple parameter values.