Post reply

Concatenating Field Values

  • June 19, 2008 at 1:32 pm

    #183680

    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!

  • June 19, 2008 at 1:35 pm

    #831371

    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)

  • June 19, 2008 at 1:35 pm

    #831372

    do it and see if you get an error.

    Everything you can imagine is real.

  • June 19, 2008 at 3:58 pm

    #831444

    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?

  • June 19, 2008 at 4:14 pm

    #831446

    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

  • June 23, 2008 at 11:14 am

    #832920

    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.

  • June 23, 2008 at 11:26 am

    #832931

    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?

  • June 23, 2008 at 3:39 pm

    #833049

    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."

  • June 24, 2008 at 5:59 am

    #833254

    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)

  • January 19, 2009 at 4:18 am

    #929086

    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.

  • January 20, 2009 at 7:37 am

    #929731

    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

  • January 20, 2009 at 12:25 pm

    #929901

    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

  • January 20, 2009 at 12:42 pm

    #929910

    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.

  • January 22, 2009 at 1:15 pm

    #931425

    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

  • January 22, 2009 at 6:51 pm

    #931570

    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