What on earth can I still write about JOIN if Sebastian is going to write 31 posts on them?
So then I thought, let’s not write about the T-SQL JOIN, but about the Join() function in Reporting Services!
I hope you enjoy this little read as we delve into a couple of the SSRS functions!
Joining Some Stuff
You may or may not already know, but the functions in Reporting Services are actually using VB.NET functions. These functions are located in the Microsoft.VisualBasic namespace.
And the Join() is actually a method of the Strings class.
So what does the definition of the Join() look like?
public static string Join( Object SourceArray, string Delimiter )
(Yes, I prefer C# style.)
It takes two parameters: an array of objects which contains the strings to be joined and a string that will be used as delimiter for the output.
So where in SSRS do we have an object array? Well, in the case of a multi-valued parameter! I’ve actually already mentioned this briefly in my article on combining multiple-value parameters with stored procedures about three years ago.
As per the definition, the delimiter is a string, not just one character. Which means the following would be an interesting expression to display the selected values on the report:
=Join(Parameters!SomeMultivalueParam.Value, ", ")
That’s right, we can use more than one character as delimiter. Assuming the report has got a multi-value parameter called SomeMultivalueParam, this could result in the following:
Even better, because our parameter is an array of strings, we can also get a count out of it using the following expression:
=Parameters!SomeMultivalueParam.Count & " items selected: " & Join(Parameters!SomeMultivalueParam.Value, ", ")
That generates the following output:
Unjoining Some Stuff?
Okay, that’s enough about Join, what if we need the opposite? To just get a particular selected value out of a multi-valued parameter, you can simply use the following array syntax:
Beware that the array indexes are zero-based, so the above expression gives us the second selected value from the object array. In our example it would thus return “second val”.
But what if we’ve got a character-separated list of values coming out of the database? In that case we don’t have an object array, right? But that doesn’t stop us from creating one!
Which brings us to the opposite of the Join(): the Split()!
Here’s what the Split function looks like:
public static string Split( string Expression, string Delimiter, int Limit, CompareMethod Compare )
Now let’s look at its usage by trying the following expression:
What do you think it returns?
That’s right: “val2”!
Would this also work for longer delimiters? Well, the MSDN page contains some contradictory information in that perspective. It mentions the following as description for the Delimiter parameter:
Optional. Any single character used to identify substring limits. If Delimiter is omitted, the space character (" ") is assumed to be the delimiter.
Hang on, single character?? But some of the examples on that same page are using longer delimiters!
Hmm, let’s try that out! What do you think the following returns?
Yep, you guessed right, once more “val2”!
BTW: the following syntax works as well but the SSRS editor complains about it so better not use it:
To be really sure that we’re actually looking at the right definition, let’s do one more test.
Here’s what I did. I opened Visual Studio 2010 and created a new C# Windows Forms Application project. Then I added a reference to the Microsoft.VisualBasic assembly.
Yes, you can do that! Just right-click the References node in the Solution Explorer and select Add Reference…
Switch to the .NET tab and select the Microsoft.VisualBasic component:
To continue, I double-clicked the Windows form’s grey background to get to the code behind the form and added the following using:
And then I typed the following code in the Form1_Load method:
To conclude I hit F5!
What do you think that returned?
So the Split function does actually support a multi-character delimiter!