Concatenate strings in group column

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.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_gg@.egal.de> wrote in message
news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>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.
>
>|||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-927a-7f2c848f295d@.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...|||On Jan 16, 9:31=A0am, PR <pr...@.egal.de> wrote:
> 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(...)" =A0to 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.
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?|||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.|||On Jan 22, 4:23=A0am, PR <pr...@.egal.de> wrote:
> On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> > I'm having a hard time visualizing what you want to display. =A0Do 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.
Is this what you want?
=3D"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:
=3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
would give you something like City, State or Company, Division
HTH|||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 ?|||On Jan 23, 3:50=A0am, PR <pr...@.egal.de> wrote:
> On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
>
> > Is this what you want?
> > =3D"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:
> > =3DFields!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. =3DSum(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
> =3DJoin(Fields!Name.StringValue) for the other text field.. but
> unfortunately that does not work.
> Any ideas ?
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.|||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).|||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" wrote:
> 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).
>|||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.sqlsql