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.
I didn’t know this (as you can tell: http://sqlblog.com/blogs/jamie_thomson/archive/2010/08/08/ssrs-helper-code-convert-a-multivalue-parameter-to-a-comma-delimited-string.aspx)
thanks Luke.
Hey Jamie,
Glad I could be of service!
Pingback: SSIS Junkie : SSRS Helper Code : Convert a multivalue parameter to a comma delimited string
Thanks Luke :) great tip! very handy,
Ferruccio Guicciardi,
Luke,
Thanks again for the write up. Got any ideas on how i could add a switch statement into something like this? Got a list comming back but want to change the display value.
Thanks,
Justin
Justin,
What are you trying to achieve?
Excellent! Love finding what i search for. Works perfect!
That’s good to hear!
Can “JOIN” be used to concatenate multiple field values (in the example, parameter values are used) as well. Please give a work around for this case.
Hi Aditya,
The JOIN() function will concatenate an array of values (hence the use of a multi-valued parameter). As Field values are single items you will not be able to use the JOIN() function. i.e.
=JOIN(Fields!MyField.Value, “,”) will not work.
I would suggest you either perform the concatenation of your fields in your SQL query, or use an expression in your report – something to the effect of:
=Fields!MyField.Value + “, ” + Fields!MyOtherField.Value
Personally, I would perform the concatenation in the Query/View/Stored Procedure.
Might I ask what you are trying to achieve?
Is there a size limit on the amount of data that the join function will allow?
Hi Doug,
Not that I know of. What error’s are you getting?
No errors but if size was over 8000 then nothing was being pasted to the database. What we did was to use the max option (nvarchar(max)) and set the field size to max also and that solved it.
Glad you got it sorted!