How to sort values in a Join function with LookUpSet in SSRS?

Question

How can I sort the values returned from a LookUpSet function inside a Join function?

Example data:

TransNo     MasterTran    Item    Category    ModifierLevel
1001000     1001000       ItemA   CategoryB   0
1002000     1001000       ItemB   CategoryC   1
1003000     1001000       ItemC   CategoryC   1

End result I'd like to get is "CategoryB ItemB ItemC". When I use the following combination of Join and LookUpSet, I end up getting "ItemB CategoryB ItemC".

=Join(LookUpSet(Fields!MasterTransNo.Value, Fields!MasterTransNo.Value, Iif(Fields!ModifierLevel.Value > 0, Trim(Fields!ItemDescription.Value), Trim(Fields!CategoryDescription.Value)), "LineItemDetails"), " ")

This is an expression on a cell in a table. The Row Group is set to Group on TransNo, sort by TransNo. I've tried a variety of different approach to sorting for the group, but always get the same result.

Any ideas on how I can force the order of data from LookUpSet so that it's joined in the order I want?

score 0 · Accepted Answer · edited Apr 21 '20 at 08:14

I ended up figuring this out by seeing other questions looking to pull distinct values only from the Join(LookUpSet()) functions and modifying it. This code is based off the useful answers from this other SO question.

Go to Report Properties

Enter the Code editor and past the following function into the Custom Code box:

Public Function JoinSortAlpha(arr As Object(), delimiter As String) As String
System.Array.Sort(arr)
Dim result As String = String.Empty
For i As Integer = 0 To arr.Length - 1
    If Not arr(i) Is Nothing And arr(i) <> String.Empty Then
        If result = String.Empty Then
            result = arr(i)
        Else
            result = result + delimiter + arr(i)
        End If
End If
Next
Return result End Function

Go to your expression and replace the Join() function with your new function by calling JoinSortAlpha(). My new expression looks like this:

=JoinSortAlpha(LookUpSet(Fields!MasterTransNo.Value, Fields!MasterTransNo.Value, Iif(Fields!ModifierLevel.Value > 0, Trim(Fields!ItemDescription.Value), Trim(Fields!CategoryDescription.Value)), "LineItemDetails"), " ")

Here's a breakdown of what the function is doing:

Create a new function called JoinSortAlpha which will have values passed into it from the expression. In this case, the values from the LookUpSet() function.
Sort the array passed from the function's argument. It's this sort that will make it alphabetical by default.
Create a String object called result to pass the final values to.
Evaluate the array arr and write a value to the result string for each value contained in the arr array. Values in an array are given a numeric value starting at 0 and increasing by 1. Here, we're telling the array to continue populating the result string from the first value in the array (at 0) until the last value in the array which is determined by the length of the array minus 1 (because the array starts at 0 rather than 1).
If your LookUpSet() function doesn't return any values, SSRS will show an error if we don't account for that in this JoinSortAlpha function. To deal with any potential blanks being returned, we're using an If statement to determine if the string is empty in which case it just returns nothing. Otherwise, it will return the value plus the delimiter from the end of the function (a space " " in my case).

How to sort values in a Join function with LookUpSet in SSRS?

1 Answers1