Concatenating Field Values

  • Is it possible to concatenate rows from a field in a dataset? I've done the following before with parameters:

    =Join(Parameters!Location_ID.Label, ", ")

    However, I would like to concatenate with fields like the following SubReport, but am receiving an "#Error":

    =JOIN(Fields!Source.Value, ", ")

    Any ideas? Thanks!

  • Perhaps you can modify the following technique... dunno if it'll actually work in Reporting Services but, rumor has it that you can make queries and functions part of the works...

    http://www.sqlservercentral.com/articles/Test+Data/61572/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • do it and see if you get an error.


    Everything you can imagine is real.

  • I'd like to perform the report formatting using SSRS expressions if possible, but maybe I'll end up doing it in the SQL code if I can't find another way. No one knows of a way to take a dataset and concatenate a series of column/row values into one string?

  • G'day mate,

    I'm not sure if I'm interpreting the question correctly... But I'll throw this out there 🙂 I do some schools work, so here I am representing:

    "Amazing Super School Semester:1 Term:1 Week:4 @ 20/06/2008 7:39:49 AM"

    =First(Fields!campus_name.Value, "Attend_Days") &

    " Semester:" & Parameters!semester.Value &

    " Term:" & Parameters!term.Value & " Week:" & Parameters!week.Value & " @ " & Globals!ExecutionTime

    By using the ambasand? (&) I can string SSRS fields and plain text all together. Hope this helps 😀

    - Damien

  • No, not looking to simply concatenate strings, but the DataSet (array?) values. I am looking for a tool or method to iterate through the DataSet and concatenate all of the field values. I've done so with the JOIN method for parameter lists but this does not seem to work for DataSet field values.

  • Devo - have you actually tried the methods in Jeff's article? There's no reason you couldn't use the function run against DISTINCT values in SSRS.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • I did get Jeff's method to work in SQL Server Management Studio with the following code:

    SELECT(STUFF((SELECT ', '+AS1.Source

    FROM Agent_Source AS1 INNER JOIN

    Agent_Source_Release ASR ON ASR.Agent_Source_ID = AS1.Agent_Source_ID

    WHERE (Release_ID = @Release_ID)

    FOR XML PATH('')),1,2,''))

    However, when I moved the query into the SSRS dataset it wants me to declare the @Release_ID variable. SSRS has already created this variable for me but it continues to say it's not there.

    I think the real problem is with the FOR XML PATH method. With even a simple select query I get the following error as soon as I add "FOR XML PATH('')" to the end of a statement:

    "Unable to parse query text."

  • I'd try to help... but I can't even spell SSRS, yet. Sorry...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Did anyone find a solution to this?

    I would also like to concatenate like:

    =Join(Fields!Total.value,",").

    I also have did similar for parameter selections.

  • Are you looking to concantenate all values or distinct values?

    Also, are you looking to concantenate the values within the entire dataset or are there groupings?

    --pete

  • Hi,

    the syntax is =Fields!Field1.Value & Fields!Field2.Value

    That's really all you need to do. You can do this perhaps most easily by adding a calculated field in the dataset. You can do it elsewhere too, but i recommend doing as early as possible.

    Aki

  • I don't want to concatenate 2 different fields I want to concatenate all of a particular Field.

    So if Column is Total and Rows are days of week. Mon=1,Tue=2,Wed=3,Thur=4,Fri=5. In a matrix this would be shown in 1 column 5 rows.

    I want to know is there a simple way to concatenate all the rows e.g

    "1,2,3,4,5"

    As mentioned by myself and original questioner, there is a simple method to do this for items chosen in a dropdown. We want to know is there a similar way to access dataset fields?

    I know how to re-do SQl to get this but I am curious if Reporting Services gives access to values via an array as it does with parameters.

  • OK,

    sorry for misunderstanding. I don't have an exact answer to your problem, but I think I know which way you should go.

    As far as I know, there is no way to solve the issue with built-in functions. Instead, you must write your own function to create an array and populate it with field values. After that it is quite simple to use the function to get the array you need. I did a bit googling and found a few solutions that deal with rather similar problems.

    Creating an array:

    http://stackoverflow.com/questions/270434/custom-code-in-reporting-services-report

    Getting field values:

    http://www.netobjectives.com/blogs/reporting-services-field-collection-tfs

    Hope these help you on,

    Aki

  • ric (1/20/2009)


    I don't want to concatenate 2 different fields I want to concatenate all of a particular Field.

    So if Column is Total and Rows are days of week. Mon=1,Tue=2,Wed=3,Thur=4,Fri=5. In a matrix this would be shown in 1 column 5 rows.

    I want to know is there a simple way to concatenate all the rows e.g

    "1,2,3,4,5"

    As mentioned by myself and original questioner, there is a simple method to do this for items chosen in a dropdown. We want to know is there a similar way to access dataset fields?

    I know how to re-do SQl to get this but I am curious if Reporting Services gives access to values via an array as it does with parameters.

    Ok... here's some methods and some of the pitfalls to avoid when doing such a thing...

    [font="Arial Black"]Performance Tuning: Concatenation Functions and Some Tuning Myths[/font][/url]

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 35 total)

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