The JOIN() Function in SSRS
BOL’s description of the JOIN() Function: “Returns a string created by joining a number of substrings contained in an array.”. The syntax of the function is as follows:
=JOIN(List [,delimiter])
List – Required. One-dimensional array containing substrings to be joined
Delimiter – Optional. String character used to separate substrings. If omitted, a space “ “ will be used.
So, the JOIN() Function concatenates an array’s values, using either a default space or the specified separator (for example, “, “) to separate the values.
What are the practical uses of this function as it relates to Reporting Services?
A common use of the function in Reporting Services is to display the values being used in a Multi-valued Parameter. You may for example have a report that has a parameter that filters Sales Regions. A requirement of the report is to display, in the header, the Sales Regions that the report is for. In the header of the report you would include the following expression:
=”Monthly Sales Report, for Regions: ” & JOIN(Parameters!SalesRegion.Value, “, “)
Previewing this in the report with the following regions selected from the SalesRegion Multi-Value Parameter:
- North America
- South America
- Southern Africa
Will result in the following Header string:
Monthly Sales Report, for Regions: North America, South America, Southern Africa
I have mentioned this function here as I find it very useful and have used it on many occasions. The above use is only one of many, but for me, is the most common.
