grouping similar data

In layout view: how do I group all the company names together who have the same value for a certain field? For instance, 10 companies all have the same booth size, but in Preview mode, it lists each company seprately with the booth size on each line. I need to have the booth size listed once with all the companies who share that common size. I have tried adding a group and using the expression for the company name as well as the booth size and it does not work. Can someone provide some detailed instructions?
Thank you,
Bil
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comIf I am understanding correctly you want to have the booth size and then
show all companies under that that match that description. Have you tried
grouping it by the booth size and then put your companies in the detail?
"Bkana" <nospam@.developmentnow.com> wrote in message
news:97f68ac4-a6e2-429d-a4e5-3b98fed63020@.developmentnow.com...
> In layout view: how do I group all the company names together who have the
> same value for a certain field? For instance, 10 companies all have the
> same booth size, but in Preview mode, it lists each company seprately with
> the booth size on each line. I need to have the booth size listed once
> with all the companies who share that common size. I have tried adding a
> group and using the expression for the company name as well as the booth
> size and it does not work. Can someone provide some detailed instructions?
> Thank you,
> Bill
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

Grouping problem

I am trying to get a table to display where my like rows would sum together,
but now matter how I do it there are 2 rows (in my example) that always show
as separate rows and I want to combine them.
For example:
ProductName Balance 30 60 90
-- -- -- -- --
30-Day Posting 1 1 0 0
(10) 90-Day Posting 10 0 0 10
(10) 90-Day Posting 20 0 20 0
(5) 60-Day Posting 5 0 5 0
Should not show 0 0 0 0
Row 2 and 3 should be together and have 30 as the balance and 20 and 10 in
the 60 and 90 column should be on the same line.
This was done with the following statement:
select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
"30" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
"60" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
"90" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (p1.PurchasedProductID = p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1
If I add the (ProductTypeID = 1) to the last line:
select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
"30" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
"60" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
"90" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1 where (ProductTypeID = 1)
Then I get:
ProductName Balance 30 60 90
-- -- -- -- --
30-Day Posting 1 1 0 0
(10) 90-Day Posting 10 0 0 10
(10) 90-Day Posting 20 0 20 0
(5) 60-Day Posting 5 0 5 0
This gets rid of the last line (which I wanted).
What I would like it to look like is:
ProductName Balance 30 60 90
-- -- -- -- --
30-Day Posting 1 1 0 0
(10) 90-Day Posting 30 0 20 10
(5) 60-Day Posting 5 0 5 0
How can I make it do that?
I assume I have to group it, but I can't seem to make that work with these
subqueries. I get errors, such as you can't use a subquery in a group by
clause.
Here is the table and data (really cut down).
drop table PurchasedProducts
go
CREATE TABLE [dbo].[PurchasedProducts] (
[PurchasedProductID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductTypeID] [int] NULL,
[ProductName] [varchar] (20) NULL ,
[PostingsLeft] [int] NULL ,
[DateExpires] [datetime] NULL
) ON [PRIMARY]
GO
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('30-Day Posting',1,1,'11/25/2005')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('(10) 90-Day Posting',1,10,'1/24/2006')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('(10) 90-Day Posting',1,20,'12/25/2005')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('(5) 60-Day Posting',1,5,'12/25/2005')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('Should not show',2,90,'12/01/2005')
go
Thanks,
TomThe easiest way to solve this is to group your results as illustrated below:
SELECT productname, SUM(balance) AS BALANCE, SUM(days30) AS [30],
SUM(days60) AS [60], SUM(days90) AS [90]
FROM (select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
Days30 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
Days60 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
Days90 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1 where (ProductTypeID = 1)) AS a
GROUP BY productname
- Peter Ward
WARDY IT Solutions
"tshad" wrote:

> I am trying to get a table to display where my like rows would sum togethe
r,
> but now matter how I do it there are 2 rows (in my example) that always sh
ow
> as separate rows and I want to combine them.
> For example:
> ProductName Balance 30 60 90
> -- -- -- -- --
> 30-Day Posting 1 1 0 0
> (10) 90-Day Posting 10 0 0 10
> (10) 90-Day Posting 20 0 20 0
> (5) 60-Day Posting 5 0 5 0
> Should not show 0 0 0 0
> Row 2 and 3 should be together and have 30 as the balance and 20 and 10 in
> the 60 and 90 column should be on the same line.
> This was done with the following statement:
> select ProductName,
> Balance = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID)),
> "30" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
> "60" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
> "90" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (p1.PurchasedProductID = p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
> from purchasedproducts p1
> If I add the (ProductTypeID = 1) to the last line:
> select ProductName,
> Balance = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID)),
> "30" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
> "60" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
> "90" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
> from purchasedproducts p1 where (ProductTypeID = 1)
> Then I get:
> ProductName Balance 30 60 90
> -- -- -- -- --
> 30-Day Posting 1 1 0 0
> (10) 90-Day Posting 10 0 0 10
> (10) 90-Day Posting 20 0 20 0
> (5) 60-Day Posting 5 0 5 0
> This gets rid of the last line (which I wanted).
> What I would like it to look like is:
> ProductName Balance 30 60 90
> -- -- -- -- --
> 30-Day Posting 1 1 0 0
> (10) 90-Day Posting 30 0 20 10
> (5) 60-Day Posting 5 0 5 0
> How can I make it do that?
> I assume I have to group it, but I can't seem to make that work with these
> subqueries. I get errors, such as you can't use a subquery in a group by
> clause.
> Here is the table and data (really cut down).
> drop table PurchasedProducts
> go
> CREATE TABLE [dbo].[PurchasedProducts] (
> [PurchasedProductID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProductTypeID] [int] NULL,
> [ProductName] [varchar] (20) NULL ,
> [PostingsLeft] [int] NULL ,
> [DateExpires] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('30-Day Posting',1,1,'11/25/2005')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('(10) 90-Day Posting',1,10,'1/24/2006')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('(10) 90-Day Posting',1,20,'12/25/2005')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('(5) 60-Day Posting',1,5,'12/25/2005')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('Should not show',2,90,'12/01/2005')
> go
>
> Thanks,
> Tom
>
>|||"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:89A22616-2111-4C1B-9D6D-F9AF452FB238@.microsoft.com...
> The easiest way to solve this is to group your results as illustrated
below:
Does it!!
You just treated my select as another table. I can never seem to come up
with that myself. I always understand it when I see it, but I can't seem to
see it when I need it.
Not really sure of the thought process to come up with it.
I was almost there, but couldn't quite see it.
Thanks,
Tom
> SELECT productname, SUM(balance) AS BALANCE, SUM(days30) AS [30],
> SUM(days60) AS [60], SUM(days90) AS [90]
> FROM (select ProductName,
> Balance = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID)),
> Days30 = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
> Days60 = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
> Days90 = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
> from purchasedproducts p1 where (ProductTypeID = 1)) AS a
> GROUP BY productname
>
> - Peter Ward
> WARDY IT Solutions
> "tshad" wrote:
>
together,
show
in
these
by
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]

Grouping by day of the week?

I am trying to create a report that will help with some trending documentation that I have to put together. What I am trying to do is get a report that would show me for every Monday, Tuesday, etc a count of the number calls taken per day for the entire year. I can get my result set easily enough, it is displaying it that I am having problems with.

The that returns is a typical MSSQL date/time field. I have tried using Weekday, weekdayname etc but it keeps saying that the value returned is out of range. How might I go about setting up my grouping to get a return set approximatly like the following example?

Monday
Jan 2 = 200
Jan 9 = 188

Tuesday
Jan 3 = 203
Jan 10 = 220

Wednesday.....

Something along those lines..

Any help or tips are GREATLY appreciated.

Thank you.What I have always done is created a formula called @.day then created a group on the @.day formula, supressed that formula and created another formula called @.dayofweek and layed it over the top of the day formula to show the name of the day of the week.

@.Day
dayofweek({yourdatabase.POST_DATE})
//create a group on this formula.

@.DayOfWeek
weekdayname({@.Day})
//place this formula on the groupheader of the @.Day group.

the field being used with dayofweek needs to be a date or date time field.

Group By Table Question

Is there a way to force a group in a table to stay together on a single page? I have a group that is showing the group by on one page and the details of the group on the next page and am wondering if there is a way to force them to be together? I don't want my groups to be broken into 2 or more pages and I don't want to necessarily always force a new page break if the group will fit on the page with another group.

Thanks...

Were you able to solve this problem? My situation is even a little simpler. I just want a group to go to the next page if it won't fit on the current. I.e. If my report will contain four groups, three of which will fit on the first page the fourth of which would be broken across to the second page, I want a break to occur before the fourth group. Obviously if a group is too large for a single page, a break will occur in the middle of that group.

I know you could do this in the Oracle reporting tool, and I thought I remembered doing it in Crystal... but, I can't find the option here.
|||Hello,

There is the ability to do this at the table level, but not the group level.
However, there are a couple of workarounds possible.

The first workaround that comes to mind is to use nested Lists, where the inner list is set to be kept together on the same page, if possible. This is a checkbox at the bottom of the general tab of the List properties dialog.

Another potential resolution would be enable Repeat group headers and/or footers on each page, so that at least the continuation of the group will contain the header and/or footer on all the pages it spans. This is a checkbox at bottom of the general tab of the Grouping and Sorting property dialog.

Group By Table Question

Is there a way to force a group in a table to stay together on a single page? I have a group that is showing the group by on one page and the details of the group on the next page and am wondering if there is a way to force them to be together? I don't want my groups to be broken into 2 or more pages and I don't want to necessarily always force a new page break if the group will fit on the page with another group.

Thanks...

Were you able to solve this problem? My situation is even a little simpler. I just want a group to go to the next page if it won't fit on the current. I.e. If my report will contain four groups, three of which will fit on the first page the fourth of which would be broken across to the second page, I want a break to occur before the fourth group. Obviously if a group is too large for a single page, a break will occur in the middle of that group.

I know you could do this in the Oracle reporting tool, and I thought I remembered doing it in Crystal... but, I can't find the option here.
|||Hello,

There is the ability to do this at the table level, but not the group level.
However, there are a couple of workarounds possible.

The first workaround that comes to mind is to use nested Lists, where the inner list is set to be kept together on the same page, if possible. This is a checkbox at the bottom of the general tab of the List properties dialog.

Another potential resolution would be enable Repeat group headers and/or footers on each page, so that at least the continuation of the group will contain the header and/or footer on all the pages it spans. This is a checkbox at bottom of the general tab of the Grouping and Sorting property dialog.

Group by Month

Hi All:
I have a report that has a field named CloseDate which has a date format. I
want to be able to group together all records by month. (i.e Sept, October
etc.)
If I elect to group by the CloseDate, my records are grouped by each
individal date rather than the month the date occurs.
How can I achieve this?
Thanks
Brennanin your grouping and sorting properties, edit your group on expression like
this:
=Microsoft.VisualBasic.DatePart("m",Fields!Date_Name.Value)
in the cell values for any totals be sure to use SUM(Fields!Date_Name.Value)
and you also probably want to format how your date column is displayed:
=Format(CDATE(Fields!Date_Name.Value),"MM-yyyy")
"Brennan" wrote:
> Hi All:
> I have a report that has a field named CloseDate which has a date format. I
> want to be able to group together all records by month. (i.e Sept, October
> etc.)
> If I elect to group by the CloseDate, my records are grouped by each
> individal date rather than the month the date occurs.
> How can I achieve this?
> Thanks
> Brennan