String aggregation in SSRS 2005

Question

Using BIDS 2005 to create rdl reports. I want to have the report aggregate all the strings in a data group. I was looking for something along the lines of Concatenate(Fields!CompanyName.Value, ","), or Join, or equivalent. But it would have to iterate over all the records in the scope given.

I am creating a report of a user's activities in a calendar format (looking like the google's month view calendar) But if a user has multiple activities on a single day I want all of them to show up in the same 'day box'. Is this a problem needing aggregation or is there some other way to get a SSRS report to do this, I have tried to figure a way to get the matrix to do it for me but I am hitting walls.

Chimere

Thank you Joel for the tag corrections, I will be sure to use those next time. – Chimere

Answer 1

The usual way to do aggregate concatenation in SSRS is with custom code. See here for an example:

http://blogs.msdn.com/suryaj/archive/2007/08/11/string-aggregation.aspx

Here's the custom code in basic form:

Private CurrGroupBy As String = String.Empty
Private ConcatVal As String = String.Empty
Public Function AggConcat(GroupBy as String, ElementVal as String) as String
    If CurrGroupBy = GroupBy Then
        ConcatVal = ConcatVal & ", " & ElementVal 
    Else
        CurrGroupBy = GroupBy 
        ConcatVal = ElementVal 
    End If
    Return ConcatVal 
End Function

Followed by this expression at the grouping level you want to display:

=RunningValue(
     Code.AggConcat(
         Fields!YourFieldToGroupBy.Value
       , Fields!YourFieldToConcat.Value
       )
   , Last
   , "YourGroupName" 
   )

"YourGroupName" is typically "table1_Group1", if it is the first table and the first group you have created in the report, and if you didn't specify a different name.

Fourier

That has worked so far thank you! This is an approach I had read of, but I had also read that using the shared variables (CurrGroupBy and ConcatVal) was a bad idea, saying that they are shared among all instances of the report. Bad thing to do on a web server. Do you know if this is true? – Chimere

That may be true, I don't know. If you find out please post something here, and I'll wikify the answer. – Fourier

This is a great help. None of the examples in the docs seem to show calling a custom function for RunningValue. – Jahncke

Thank you! Do you got any ideea how to join only the distinct ones? – Mcglothlin

Answer 2

There is a simpler way to concatenate values together by a grouped value. Use something like this as the expression:

=Join(LookUpSet(Fields!GroupField.Value, Fields!GroupField.Value, Fields!ConcatField.Value, "DataSet1"), ",")

Anderlecht