Concatenating Field Values

Question

Concatenating Field Values

Viewing 15 posts - 16 through 30 (of 35 total)

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

mark_92708 SSC Enthusiast Points: 145 More actions · Answer 1

Hi all,

I was reading this thread because I was looking for a solution to this identical problem - trying to concatenate the result strings of one field from a dataset into a single display cell on a report. Well, I did find a solution close what I wanted, and may be suitable for some of you too.

I simply cut the inner most cell (likely a textbox) from the table where I wanted this concatenated string of values, and replaced it with a list object (from the toolbox). Then selected that list and pasted the textbox with my field reference back into the list. Now that cell shows all the items from the dataset for the field of interest, not concatenated in a csv string (which I would prefer) but as a list in that one cell of the outer table. good enough for my needs.

I hope it's useful to some of you too.

Mark

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 2

mark_92708 (2/26/2009)
Hi all,
I was reading this thread because I was looking for a solution to this identical problem - trying to concatenate the result strings of one field from a dataset into a single display cell on a report. Well, I did find a solution close what I wanted, and may be suitable for some of you too.
I simply cut the inner most cell (likely a textbox) from the table where I wanted this concatenated string of values, and replaced it with a list object (from the toolbox). Then selected that list and pasted the textbox with my field reference back into the list. Now that cell shows all the items from the dataset for the field of interest, not concatenated in a csv string (which I would prefer) but as a list in that one cell of the outer table. good enough for my needs.
I hope it's useful to some of you too.
Mark

I think the way you described is a much better and faster way... but, if you really, really want the CSV, see the article I previously prescribed. (click on the following)...

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

Again, I don't know if this will will in reporting services or not.

--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)

mark_92708 SSC Enthusiast Points: 145 More actions · Answer 3

Thanks Jeff for the feedback. I did look over your article prior to coming up with the solution I used. My only challenge with the approach in the article, is that it is in SQL Script, and Reporting Services requires custom embedded code be in VB.NET, and I did'nt know how to apply that SELECT logic in VB.NET to make it work.

Thanks again though.

-Mark

Randy DeMasters SSC Enthusiast Points: 105 More actions · Answer 4

mark_92708 (2/27/2009)
Thanks Jeff for the feedback. I did look over your article prior to coming up with the solution I used. My only challenge with the approach in the article, is that it is in SQL Script, and Reporting Services requires custom embedded code be in VB.NET, and I did'nt know how to apply that SELECT logic in VB.NET to make it work.
Thanks again though.
-Mark

So what solution did you end up using? I can't seem to find one anywhere.

mark_92708 SSC Enthusiast Points: 145 More actions · Answer 5

Randy,

Like I replied to Jeff earlier, the only approach I could get to work did not have a concatenated string of values (CSV) as I would have preferred, but rather I used a ListBox (Tablix) in the outer matrix's cell where I wanted the values to be (in place of the TextBox normally inserted there), and then just populated the list with a standard dataset field reference. This shows the list of values in a list format, but they all appear in the one cell, which works for me.

Does that make sense?

Mark

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 6

mark_92708 (2/27/2009)
Thanks Jeff for the feedback. I did look over your article prior to coming up with the solution I used. My only challenge with the approach in the article, is that it is in SQL Script, and Reporting Services requires custom embedded code be in VB.NET, and I did'nt know how to apply that SELECT logic in VB.NET to make it work.
Thanks again though.
-Mark

I don't know much about Reporting Sevices, but one of the guys I work with is using all stored procs to drive such processes on Reporting Services.

--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)

Randy DeMasters SSC Enthusiast Points: 105 More actions · Answer 7

That is the route I have ended up attempting and it is still frustrating me. It returns the stored procedure values properly in the dataset when I am in VS2008 in the .xsd preview data section, however when I try to drop the field into a textbox no value is returned. It feels as if the datasource is not being wired up with the parameter properly. Thanks in advance for any help anyone might have on that.

Randy DeMasters SSC Enthusiast Points: 105 More actions · Answer 8

I worked it out, if you choose add query on the binding source after setting up your dataset for your SP and then change the query to existing and choose the default fill query it wires the SP up with a FillToolStrip and creates the code to fill your datasource and more importantly I suspect creates a TableAdapterManager. I deleted the tool strip and moved the code into my load event as well as replacing the toolstrip textbox.text value with the parameter I was passing in and it works.

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 9

Randy DeMasters (3/30/2009)
I worked it out, if you choose add query on the binding source after setting up your dataset for your SP and then change the query to existing and choose the default fill query it wires the SP up with a FillToolStrip and creates the code to fill your datasource and more importantly I suspect creates a TableAdapterManager. I deleted the tool strip and moved the code into my load event as well as replacing the toolstrip textbox.text value with the parameter I was passing in and it works.

Very cool... thanks for the feeback, Randy.

--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)

mwpage SSC Enthusiast Points: 113 More actions · Answer 10

You can try this if you want, I am really new to SSRS, and even newer to VB custom code, but I created two functions to accomplish this, and you can polish it up as much as you want.

(this is all using SSRS 2005 and I'm sure there must be a cleaner way to do it, so please reply if you can fix code up)

I create two separate functions. The first function I call from the details row of a hidden table. It's sole purpose is to add values to the hashtable. The second Function obviously retrieves those values.

[font="Courier New"]DIM MyValues AS System.Collections.Hashtable

Function ConcatFields (theKey AS String, theValue AS String, theDelimeter AS String) As String

If (MyValues IS Nothing) Then

MyValues = New System.Collections.Hashtable

End If

If (MyValues.ContainsKey(theKey))

MyValues(theKey) = MyValues(theKey) & theDelimeter & theValue

Else

MyValues.Add(theKey, theValue)

End If

ConcatFields = MyValues(theKey)

End Function

Function RetrieveMyValue (theKey) AS String

If (MyValues.ContainsKey(theKey))

RetrieveMyValue = MyValues(theKey)

Else

RetrieveMyValue = ""

End If

End Function[/font]

For theKey I use whatever my Group Expression is in the table. So if I have CustomerName as a Field

and a Customer has multiple Locations in multiple countries. In the Table where I show data the group expression is

[font="Courier New"]Fields!CustomerName.Value

Fields!CountryID.Value[/font]

In the hidden table I would call

[font="Courier New"]=Code.ConcatFields(Fields!CustomerName.Value & Fields!CountryID.Value, Fields!Location.Value, ", ")[/font]

Then in the table where I am really showing data, in the Locations column just use this expression

[font="Courier New"]=Code.RetrieveMyValue(Fields!CustomerName.Value & Fields!CountryID.Value)

[/font]

Hope that helps!

(Update 2010-10-28) I have had a few people contact me on this, the exact approach on hiding the first table needs to be modified for SSRS 2008 and beyond. There is a good post on the matter here.

Also for SSRS 2008 R2, it works better to get rid of all this custom code, and just use the LookupSet function. That function is designed to lookup stuff in a differenct dataset, but can be used against the same dataset as in (using same field names as above)

=JOIN

( LookupSet

( Fields!CustomerName.Value & Fields!CountryID.Value

,Fields!CustomerName.Value & Fields!CountryID.Value

,Fields!Location.Value

,"DataSet1"

)

, ", "

)

phabybaby Grasshopper Points: 15 More actions · Answer 11

Good thread... thought I'd give a little back.

After working on this a while it occurred to me that if the builtin function wants a parameter... I'd just make a query to return a internal parameter that has nothing to do with user selections.

I created a dataset in SSRS that returns a single column with the values I want to string together. Then created a hidden, internal report parameter that uses 'from query'. I pointed to that query for the available values, used the single column for the value field, did not use a label field, and then set the 'Default Values' options to the same query and single column.

Now I'm using the join(parameters!columnname,", ") just fine!

SSRS 2005

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

That does the trick!

http://stackoverflow.com/questions/972616/string-aggregation-in-ssrs-2005

l543123 SSC Eights! Points: 944 More actions · Answer 13

if the field values to be concatenated are coming from dataset, create a hidden parameter and assign this field (the one which you want to concatenate) as default values to that hidden parameter and the use JOIN(hiddenparameter,",") on the report.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

That won't work, at least in my case, as I needed to concatenate for each group. Myabe it'll help somebody else.

ejoell 66477 Right there with Babe Points: 758 More actions · Answer 15

I thought the JOIN(hiddenParameter, ",") would be just what I needed. Unfortunately all I get is a list of "#ERROR". And it still returned one row for each row in the dataset. Anyone have any ideas why this did not work? Point of Information; I need to be able to show a user how to do this in Report Builder 2.0. So code answers and T-SQL answers will not be applicable in my situation.