Concatenate strings in group column

PR

unread,

Jan 16, 2008, 9:31:26 AM1/16/08

to

I have following problem:

I want to display a concatenated string in a group row of a report.
The grouping works fine, i can "sum" numerical values etc. as
expected, but i could not find a way to concatenate strings (from the
rows that make up the group). "Join(...)" does not work (gives me an
error message).
I can use "First(...) & Last(...)" to concatenate the string columns
from the first and last row of the group, but couldn't find a way to
concatenate the string columns from all rows...

I am using SQL Server 2005 / Visual Studio 2005.

Jean-Pierre Riehl

unread,

Jan 17, 2008, 2:21:48 AM1/17/08

to

I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
Server 2005.
In reports, I use Replace function to change my separator character.

--
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr

"PR" <pr...@egal.de> wrote in message
news:76e4156a-8805-4e71...@q77g2000hsh.googlegroups.com...

PR

unread,

Jan 18, 2008, 4:42:30 AM1/18/08

to

On Jan 17, 8:21 am, "Jean-Pierre Riehl" <jean-pierre.ri...@bewise.fr>
wrote:

> I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
> Server 2005.
> In reports, I use Replace function to change my separator character.
>
> --

> Jean-Pierre Riehlhttp://blog.djeepy1.nethttp://www.bewise.fr

>
> "PR" <pr...@egal.de> wrote in message
>
> news:76e4156a-8805-4e71...@q77g2000hsh.googlegroups.com...
>

thanks for the idea. I do use SQL Server 2005, however, since this is
a "group" column in a report, i can't use SQL to concatenate the
strings as i deal with a grouped subset of the sql query result...

toolman

unread,

Jan 21, 2008, 10:06:33 AM1/21/08

to

I'm having a hard time visualizing what you want to display. Do you
want your concantenation in each detail row or just in a group header?
Can you mock up an example?

PR

unread,

Jan 22, 2008, 4:23:34 AM1/22/08

to

On Jan 21, 4:06 pm, toolman <t...@infocision.com> wrote:
> I'm having a hard time visualizing what you want to display. Do you
> want your concantenation in each detail row or just in a group header?
> Can you mock up an example?

I want the concatenation in each detail row. I have some group "sum"s
that work fine, and want to do the same for a string. Basically it's
just a normal grouping of columns with group sums etc.. just that i
need to concatenate a string besides building the group sums.

toolman

unread,

Jan 22, 2008, 2:57:28 PM1/22/08

to

Is this what you want?
="String Expression " & SUM(Fields!Name.Value)
It would look something like:
String Expression 1234.56
If you're wanting to concantenate actual field values, say like if
you're grouping on a combination of fields then:
=Fields!GroupField1.Value & ", " & Fields!GroupField2.Value
would give you something like City, State or Company, Division

HTH

PR

unread,

Jan 23, 2008, 3:50:06 AM1/23/08

to

On Jan 22, 8:57 pm, toolman <t...@infocision.com> wrote:
>
> Is this what you want?
> ="String Expression " & SUM(Fields!Name.Value)
> It would look something like:
> String Expression 1234.56
> If you're wanting to concantenate actual field values, say like if
> you're grouping on a combination of fields then:
> =Fields!GroupField1.Value & ", " & Fields!GroupField2.Value
> would give you something like City, State or Company, Division
>
> HTH

Sorry, no, i don't want to concatenate strings with the sum value of
the group.
I have a grouping in the report, where a sum value (i.e. =Sum(Fields!
Name.NumValue)) is written to one textfield of the row.
What i want to do is concatenate a string from the groups result set -
pretty much like the "Sum" sums up the numerical values from the
result set over the rows. If "Join" would work it would look like
=Join(Fields!Name.StringValue) for the other text field.. but
unfortunately that does not work.

Any ideas ?

toolman

unread,

Jan 23, 2008, 11:03:02 AM1/23/08

to

I think if you can get your Fields!Name.StringValue values into an
array, you could then use Join() to get what you want. Unfortunately,
I'm not enough of a VB or .NET guy to guide you through that.
Hopefully someone else can jump at this point. Sorry I can't get you
further.

PR

unread,

Jan 24, 2008, 3:37:36 AM1/24/08

to

On Jan 23, 5:03 pm, toolman <t...@infocision.com> wrote:
>
> I think if you can get your Fields!Name.StringValue values into an
> array, you could then use Join() to get what you want. Unfortunately,
> I'm not enough of a VB or .NET guy to guide you through that.
> Hopefully someone else can jump at this point. Sorry I can't get you
> further.

Thanks for your effort. Yes, Join() requires an array.. and i have no
idea how to convert the string values into an array to do that (and
could not find anything about it in the documentation).

Alexandra Ribeiro

unread,

Feb 22, 2008, 6:13:08 AM2/22/08

to

Hello, PR.

Did you find any solution to your problem? I have the same issue and I can't
find any solution for this...

Thank you.
--
Alexandra

PR

unread,

Feb 24, 2008, 8:16:04 AM2/24/08

to

On Feb 22, 12:13 pm, Alexandra Ribeiro

<AlexandraRibe...@discussions.microsoft.com> wrote:
> Hello, PR.
>
> Did you find any solution to your problem? I have the same issue and I can't
> find any solution for this...
>
> Thank you.
> --
> Alexandra
>

Hi Alexandra,

no, i haven't found a solution for this. Maybe using a sub-report is
an option, but i haven't tried this yet.