Shall We Join Or Shall We Split Now?

SqlTuesday T SQL Tuesday #37 – Invite to Join me in a Month of Joins

When I saw this month’s T-SQL Tuesday’s topic, hosted by Sebastian Meine, my initial thoughts were indeed:

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:

Joining several values from a multi-value parameter together in a comma-separated string

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:

Displaying number of items selected=

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:

=Parameters!SomeMultivalueParam.Value(1)

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:

=Split("val1,val2,val3", ",").GetValue(1)

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:

Type: System.String

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?

=Split("val1,;val2,;val3", ",;").GetValue(1)

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:

=Split("val1,;val2,;val3", ",;")(1)

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…

Adding an assembly reference to the C# project

Switch to the .NET tab and select the Microsoft.VisualBasic component:

Adding the Microsoft.VisualBasic assembly to the C# project

To continue, I double-clicked the Windows form’s grey background to get to the code behind the form and added the following using:

using Microsoft.VisualBasic;

And then I typed the following code in the Form1_Load method:

MessageBox.Show(Strings.Split("val1,;val2,;val3", ",;").GetValue(1).ToString());

To conclude I hit F5!

What do you think that returned?

Guess…

Output of the Split function using a multi-character delimiter

So the Split function does actually support a multi-character delimiter!

Have fun!

Valentino.

Share

Tags: , , ,

  1. Koen Verbeeck’s avatar

    Nice touch to the #tsql2sday topic :)
    I especially like how this can be used to display the chosen parameter values on the report.

    Reply

© 2008-2014 BI: Beer Intelligence? All Rights Reserved