Post reply

Field Concatenation in SSRS 2008 R2

  • October 14, 2011 at 8:58 am

    #245331

    I searched quite awhile for the technique to do this. If it's already covered in this forum, I apologize.

    Problem

    We have a subreport of name value pairs, but some of the names repeat and we want all the values for those names concatenated into a single name value pair. Unfortunately I am not allowed to modify the stored procedures.

    Here’s our sample data.

    Label -- Value

    Contacts -- Jones, Sheila

    Contacts -- Smith, Mike

    Contacts -- Roberts, Robert

    Here’s how we want to display the sample data

    Label -- Value

    Contacts -- Jones, Sheila;Smith, Mike;Roberts, Robert

    I built a subreport with a tablix connected to the name value dataset called DataSet1. The fields are called Label and Value. I grouped on the Label field. My tablix has two textboxes. The first hosts the generated Label value, created when I created the group on Label, and the second will host the concatenated values. I put the value textbox in the group row to align with the label, but it also works in the details row.

    The work is accomplished in the combination of the Join() and LookupSet() functions. LookupSet() creates an array from the dataset, and Join() does the concatenation. Join() is in previous versions of SSRS but Lookupset() is new to ReportBuilder 3.0. Join() will not work on dataset values; it needs an array input. Below is the value of my Value textbox.

    =Join(LookupSet(Fields!LABEL.Value, Fields!LABEL.Value, Fields!VALUE.Value, "DataSet1"), ";")

    Simple and sweet. It sometimes pays to read the documentation!

    LoopUpSet()

  • October 17, 2011 at 12:48 pm

    #1395535

    Another option, create a hidden multi select parameter with all the contact names, and use join on that parameter. Works like a charm.


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply