Grouping & Concatenation?

Hello,

I have a report with a table containing 2 groups (Role and Person, where person is a subgroup of role). I am trying to concatenate the names of a series of persons who are grouped by their role within an organisation so that a report that would usually appear as:
...
Board of Directors
Director1
DIrector2
Director3
Advisory Board
Adv1
Adv2...
...
Will appear as:
...
Board of Directors
Director1, Director2, Director3
Advisory Board
Adv1, Adv2...
...
This can easily be done in crystal reports by concatenating the person names in the header section of group 2 (persons) and printing the result in the footer section of group 1 (role). However, in SSRS it appears that a group's footer is output before the contents (details or subgroup) of the group and this throws out the whole concatenation process so that I end up with:
...
Board of Directors
Advisory Board
Director1, Director2, Director3
...
How can I get around this?
Thank you,
Stephen.

You may want to read this blog article: http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

It describes the custom aggregate approach. In your particular example, the custom code function would just concatenate strings (instead of adding certain values as shown in the blog article).

-- Robert

|||

Thanks for the response. I will give that a try and let you know how I get on.

Thanks again,

Stephen.

|||

I've taken a look at the suggested solution and, correct me if I'm wrong but it appears to be doing much the same as what I am already doing. In the header of the inner group, I am concatenating the list of persons:
...
Shared _memberList As String

Shared Function AddMember(ByVal member As String) As String
If (_memberList Is Nothing) Then _memberList = String.Empty
If (_memberList.IndexOf(member) = -1) Then _memberList &= member
Return String.Empty
End Function
...
Then in the footer of the outer group, I am printing the concatenated list:
...
Shared Function GetMembers() As String
Dim tempList As String = _memberList
_memberList = String.Empty
Return tempList
End Function
...
As I say, this is causing problems because it appears that the outer group's footer is processed before the inner group's header (which just doesn't seem logical to me), so the list of persons are allways printed either in the following group or following record.

|||

OK. I've finally found a solution. Instead of using a table to achieve this, I place a list object inside a table header and manually configured the groups within the list object. This way, I was able to ensure the order of precedence was header->inner group->footer.

Regards,

Stephen.

|||

Can you please be more specific? Did you create a separate dataset for the list and added groupings in that? Did you hide the list display in the header and took the concatenated result from it and displayed in the body of the table?

Thank you,

Vinita

Grouping & Concatenation?

Hello,

I have a report with a table containing 2 groups (Role and Person, where person is a subgroup of role). I am trying to concatenate the names of a series of persons who are grouped by their role within an organisation so that a report that would usually appear as:
...
Board of Directors
Director1
DIrector2
Director3
Advisory Board
Adv1
Adv2...
...
Will appear as:
...
Board of Directors
Director1, Director2, Director3
Advisory Board
Adv1, Adv2...
...
This can easily be done in crystal reports by concatenating the person names in the header section of group 2 (persons) and printing the result in the footer section of group 1 (role). However, in SSRS it appears that a group's footer is output before the contents (details or subgroup) of the group and this throws out the whole concatenation process so that I end up with:
...
Board of Directors
Advisory Board
Director1, Director2, Director3
...
How can I get around this?
Thank you,
Stephen.

You may want to read this blog article: http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

It describes the custom aggregate approach. In your particular example, the custom code function would just concatenate strings (instead of adding certain values as shown in the blog article).

-- Robert

|||

Thanks for the response. I will give that a try and let you know how I get on.

Thanks again,

Stephen.

|||

I've taken a look at the suggested solution and, correct me if I'm wrong but it appears to be doing much the same as what I am already doing. In the header of the inner group, I am concatenating the list of persons:
...
Shared _memberList As String

Shared Function AddMember(ByVal member As String) As String
If (_memberList Is Nothing) Then _memberList = String.Empty
If (_memberList.IndexOf(member) = -1) Then _memberList &= member
Return String.Empty
End Function
...
Then in the footer of the outer group, I am printing the concatenated list:
...
Shared Function GetMembers() As String
Dim tempList As String = _memberList
_memberList = String.Empty
Return tempList
End Function
...
As I say, this is causing problems because it appears that the outer group's footer is processed before the inner group's header (which just doesn't seem logical to me), so the list of persons are allways printed either in the following group or following record.

|||

OK. I've finally found a solution. Instead of using a table to achieve this, I place a list object inside a table header and manually configured the groups within the list object. This way, I was able to ensure the order of precedence was header->inner group->footer.

Regards,

Stephen.

|||

Can you please be more specific? Did you create a separate dataset for the list and added groupings in that? Did you hide the list display in the header and took the concatenated result from it and displayed in the body of the table?

Thank you,

Vinita

group by

I've created a table containing columns date, cost, orders etc. I want to
write a query group by the month part of the date. Is it possible to write
it. if yes how. Any suggestion would be greatly appreciated.
regards
shineSelect col1,col2,col3,MONTH(datecol)
From SomeTable
Group by col1,col2,col3,MONTH(datecol)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"shine" <shine@.discussions.microsoft.com> schrieb im Newsbeitrag
news:3FA1DE3D-DD75-442B-B969-2367E7406E48@.microsoft.com...
> I've created a table containing columns date, cost, orders etc. I want to
> write a query group by the month part of the date. Is it possible to write
> it. if yes how. Any suggestion would be greatly appreciated.
> regards
> shine|||u can get the month as
select datepart(m,getdate()) This_Month
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"shine" wrote:

> I've created a table containing columns date, cost, orders etc. I want to
> write a query group by the month part of the date. Is it possible to write
> it. if yes how. Any suggestion would be greatly appreciated.
> regards
> shine|||Two problems with the code:
(1) all the months get grouped together, regardless of the year, so the
data is meaningless
(2) You are not allowed to have a function in a GROUP BY in Standard
SQL, so this does not port.
I would use a Calendar table to get a year/month value, do a JOIN and
gorup by that.|||On 19 May 2005 08:44:25 -0700, --CELKO-- wrote:

> Two problems with the code:
> (1) all the months get grouped together, regardless of the year, so the
> data is meaningless
> (2) You are not allowed to have a function in a GROUP BY in Standard
> SQL, so this does not port.
> I would use a Calendar table to get a year/month value, do a JOIN and
> gorup by that.
A Calendar table could be real overkill in this case; it didn't sound as
though there are any data attached to specific dates, like there are in the
traditional holiday calendar table.
To get around both the problems identified above, and to show a possible
meaningful use for the grouping, use this construction:
SELECT col1, col2, col3, theYear, theMonth,
COUNT(*) "OrderCount", SUM(cost) "TotalCost"
FROM (
SELECT col1,col2,col3,YEAR(datecol) "theYear", MONTH(datecol) "theMonth"
FROM SomeTable
)
GROUP BY col1, col2, col3, theYear, theMonth|||Comments inline.
http://www.sqlserver2005.de
--
"--CELKO--" <jcelko212@.earthlink.net> schrieb im Newsbeitrag
news:1116517465.190810.201240@.o13g2000cwo.googlegroups.com...
> Two problems with the code:
> (1) all the months get grouped together, regardless of the year, so the
> data is meaningless
Even this was only a example, to show the teamwork of group functions with
the selection of oher columns. Due to the lack of DDL by the original poster
there was no clue what he wants to do. Sytanx exmaples doesnt alway make
sense since they are only syntax examples (due to the lack of DDL).

> (2) You are not allowed to have a function in a GROUP BY in Standard
> SQL, so this does not port.
Actually you are allowed, unless you mention the function also in your Group
by clause, if you are working on TSQL and you dont wanna port your code,
why should you bother about Standard SQL ?

> I would use a Calendar table to get a year/month value, do a JOIN and
> gorup by that.

group by

I've created a table containing columns date, cost, orders etc. I want to
write a query group by the month part of the date. Is it possible to write
it. if yes how. Any suggestion would be greatly appreciated.
regards
shineSELECT AVG(cost), DATEPART(mm, datecol)
FROM tbl
GROUP BY DATEPART(mm, datecol)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"shine" <shine@.discussions.microsoft.com> wrote in message
news:F792B5DB-98C6-48C3-B85C-EE43E2915567@.microsoft.com...
> I've created a table containing columns date, cost, orders etc. I want to
> write a query group by the month part of the date. Is it possible to write
> it. if yes how. Any suggestion would be greatly appreciated.
> regards
> shine|||Example:
use northwind
go
select month(orderdate) as month_number, count(*) as month_cnt
from dbo.orders
group by month(orderdate);
AMB
"shine" wrote:

> I've created a table containing columns date, cost, orders etc. I want to
> write a query group by the month part of the date. Is it possible to write
> it. if yes how. Any suggestion would be greatly appreciated.
> regards
> shine