Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Concatenating Field Values Expand / Collapse
Author
Message
Posted Thursday, June 19, 2008 1:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 10:34 AM
Points: 7, Visits: 124
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!
Post #520184
Posted Thursday, June 19, 2008 1:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:25 PM
Points: 35,372, Visits: 31,925
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #520187
Posted Thursday, June 19, 2008 1:35 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 4:24 AM
Points: 685, Visits: 104
do it and see if you get an error.


Everything you can imagine is real.

Post #520188
Posted Thursday, June 19, 2008 3:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 10:34 AM
Points: 7, Visits: 124
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?
Post #520275
Posted Thursday, June 19, 2008 4:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 22, 2012 4:10 AM
Points: 40, Visits: 142
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 :D

- Damien
Post #520279
Posted Monday, June 23, 2008 11:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 10:34 AM
Points: 7, Visits: 124
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.
Post #522061
Posted Monday, June 23, 2008 11:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 7,105, Visits: 15,456
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?
Post #522074
Posted Monday, June 23, 2008 3:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 10:34 AM
Points: 7, Visits: 124
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."
Post #522219
Posted Tuesday, June 24, 2008 5:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:25 PM
Points: 35,372, Visits: 31,925
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #522470
Posted Monday, January 19, 2009 4:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 2, 2013 5:25 AM
Points: 58, Visits: 349
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.
Post #639168
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse