Sdílet prostřednictvím

String Aggregation

  • Článek

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

Comments

  • Anonymous
    August 11, 2007
    PingBack from http://msdnrss.thecoderblogs.com/2007/08/11/string-aggregation/

  • Anonymous
    July 01, 2010
    Thank you so much, this clever technics really works. Too bad we have to use such mind-twisting workarounds as substitute for something that has been available in Crystal Reports for ages.

  • Anonymous
    August 15, 2011
    this is great and it can be used to get the last non empty value.

  • Anonymous
    March 12, 2013
    This code doesn't work, I keep on getting on error.  Please check before you post next time.

  • Anonymous
    September 23, 2014
    That's not a nice thing to say John. There are others commenting and saying thanks which means it works for them. You just don't trash talk someone's work.

  • Anonymous
    December 26, 2014
    Thank you for posting this.  No one in my office knows Report Builder well enough to know how to do this (concat string data in a group) and it took two days of searching to finally find an answer I could implement.  Thank you very much!  (and your code worked great for me)

  • Anonymous
    February 19, 2015
    can this be used in a matrix so that the concatenation is limited also on a column grouping?