String Aggregation

SSRS Report Designer exposes rich set of functionality to customize look and feel of report data using the expression editor. Along with some of the niceties like IntelliSense, real-time syntax checking it also comes with a suite of Mathematical, Financial, DateTime, Text and Aggregation functions which come very handy.

    Aggregation functions consist of the standard COUNT, AVG, MIN, MAX among others, but something which I find quite useful is the RunningValue (Returns a running aggregate of the specified argument function). What I found the other day is quite an interesting way of using the RunningValue to solve a problem cited in SSRS forums (and in internal DL's) and for which there is no straight forward solution available.

    Simplified Problem Statement: In the AdventureWorks database, your account has access to a view which returns Customer Id, Sales Ids, and Sales Amount. Your task is to prepare a two column report showing Customer Id in one column and Sales Amount in the second column. The report also needs you to group by customer Id and display under the Customer Id column value as Customer Id with Sales Id's concatenated. Sales amount needs to show Sales Amount.

    The way I look at the problem is that upon grouping, report needs to aggregate Sales Amount as well as Sales Ids'. Aggregate of Sales Amount is SUM whereas aggregate of Sales Ids needs to be concatenation of Sales Ids.

    Now onto the solution: Since I have already given a hint that I had solved this using RunningValue, let us revisit the RunningValue function:

    RunningValue(Expression, Function, Scope)

    

    Using Expression, I would like it to keep concatenating the SalesIds. So my expression here could keep concatenating the SalesId's as long as the Scope is my current CustomerId. I used the Aggregate Function
Last to return the concatenated list before it moves onto the next CustomerId. So my Code for expression looked like:

 

Private currCustId As Integer = 0

Private ret As String = String.Empty

Public Function ConcatSalesIds(custId as Integer, salesId as Integer) as String

    If currCustId = custId Then

        ret = ret & ", " & salesId

    Else

        currCustId = custId

        ret = custId & " [" & salesId

    End If

    Return ret & "]"

End Function

 

and the expression for my Customer Id has the value:

= RunningValue(Code.ConcatSalesIds(Fields!CustomerID.Value, Fields!SalesOrderID.Value), Last, "custGroup")

where "custGroup" is the grouping done on Fields!CustomerID.Value