Group by Day

Hi,
This is what I am trying to achieve, but in vain.
I have a report that has to be grouped by "Day" - I have hourly data for a
day which can span for several days based on the report start and end time.
For example if the report duration is 10/01/2004 to 10/05/2004, then I must
see 10/01/2004 day's worth of data (hour 0 thro 23) on a single page with
summary statistics for that day towards the bottom of the page. And then
10/02/2004 on the next page and so on and so forth.
I have 2 matrices - one for detailed data for the day and another one for
the summary (sum, avg, min, max) for the same day. I am trying to group by
"Year", "Month" and "Day" but this is not working. Can anyone please let me
know how to make this work? Any help will be highly appreciated.
ThanksCan anyone please help, I am stuck.
"kmp" <kmp@.discussions.microsoft.com> wrote in message
news:3356ECAC-AB1E-4FB9-A8A2-7A424C687B10@.microsoft.com...
> Hi,
> This is what I am trying to achieve, but in vain.
> I have a report that has to be grouped by "Day" - I have hourly data for a
> day which can span for several days based on the report start and end
time.
> For example if the report duration is 10/01/2004 to 10/05/2004, then I
must
> see 10/01/2004 day's worth of data (hour 0 thro 23) on a single page with
> summary statistics for that day towards the bottom of the page. And then
> 10/02/2004 on the next page and so on and so forth.
> I have 2 matrices - one for detailed data for the day and another one for
> the summary (sum, avg, min, max) for the same day. I am trying to group by
> "Year", "Month" and "Day" but this is not working. Can anyone please let
me
> know how to make this work? Any help will be highly appreciated.
> Thanks|||Create a function in SQL server to strip off the time from the Datetime
field. Do this by coverting to to char(8) then back to datetime. Use this
function to give you the date only. Group by this function and put a page
break after that grouping level.
"kmp" wrote:
> Hi,
> This is what I am trying to achieve, but in vain.
> I have a report that has to be grouped by "Day" - I have hourly data for a
> day which can span for several days based on the report start and end time.
> For example if the report duration is 10/01/2004 to 10/05/2004, then I must
> see 10/01/2004 day's worth of data (hour 0 thro 23) on a single page with
> summary statistics for that day towards the bottom of the page. And then
> 10/02/2004 on the next page and so on and so forth.
> I have 2 matrices - one for detailed data for the day and another one for
> the summary (sum, avg, min, max) for the same day. I am trying to group by
> "Year", "Month" and "Day" but this is not working. Can anyone please let me
> know how to make this work? Any help will be highly appreciated.
> Thanks|||Thanks for the reply - this worked. But I have another problem. When I page
break per day, I would also like to see the summary statistics towards the
bottom of the page per day.
Since I have 2 matrices - one for detailed data (where I page break per day
basis) and another one for the summary statistics (sum, min, max etc.), when
I do a page break on the first matrix, I am not able to get the second matrix
on the same page (because of the page break on the first one). Is there
anyway I can see both the matrices on one page? Any help will be appreciated.
"johnE" wrote:
> Create a function in SQL server to strip off the time from the Datetime
> field. Do this by coverting to to char(8) then back to datetime. Use this
> function to give you the date only. Group by this function and put a page
> break after that grouping level.
> "kmp" wrote:
> > Hi,
> > This is what I am trying to achieve, but in vain.
> >
> > I have a report that has to be grouped by "Day" - I have hourly data for a
> > day which can span for several days based on the report start and end time.
> > For example if the report duration is 10/01/2004 to 10/05/2004, then I must
> > see 10/01/2004 day's worth of data (hour 0 thro 23) on a single page with
> > summary statistics for that day towards the bottom of the page. And then
> > 10/02/2004 on the next page and so on and so forth.
> >
> > I have 2 matrices - one for detailed data for the day and another one for
> > the summary (sum, avg, min, max) for the same day. I am trying to group by
> > "Year", "Month" and "Day" but this is not working. Can anyone please let me
> > know how to make this work? Any help will be highly appreciated.
> >
> > Thanks|||Can someone help me with my problem as posted below? I really appreciate the
help. Thanks.
"kmp" wrote:
> Thanks for the reply - this worked. But I have another problem. When I page
> break per day, I would also like to see the summary statistics towards the
> bottom of the page per day.
> Since I have 2 matrices - one for detailed data (where I page break per day
> basis) and another one for the summary statistics (sum, min, max etc.), when
> I do a page break on the first matrix, I am not able to get the second matrix
> on the same page (because of the page break on the first one). Is there
> anyway I can see both the matrices on one page? Any help will be appreciated.
> "johnE" wrote:
> > Create a function in SQL server to strip off the time from the Datetime
> > field. Do this by coverting to to char(8) then back to datetime. Use this
> > function to give you the date only. Group by this function and put a page
> > break after that grouping level.
> >
> > "kmp" wrote:
> >
> > > Hi,
> > > This is what I am trying to achieve, but in vain.
> > >
> > > I have a report that has to be grouped by "Day" - I have hourly data for a
> > > day which can span for several days based on the report start and end time.
> > > For example if the report duration is 10/01/2004 to 10/05/2004, then I must
> > > see 10/01/2004 day's worth of data (hour 0 thro 23) on a single page with
> > > summary statistics for that day towards the bottom of the page. And then
> > > 10/02/2004 on the next page and so on and so forth.
> > >
> > > I have 2 matrices - one for detailed data for the day and another one for
> > > the summary (sum, avg, min, max) for the same day. I am trying to group by
> > > "Year", "Month" and "Day" but this is not working. Can anyone please let me
> > > know how to make this work? Any help will be highly appreciated.
> > >
> > > Thanks|||Let me see if I understand this.
You have a report with two grouping levels let say by Product then by day so
you get the daily sales for the product but then you get a total for that
product. You want the total for the product to be on the same page as the
last days totals for that product
like this
Product A
Day 1
Order
Order
Total
Page Break
Day2
Order
Order
Total
Product A Total
Page Break
You may be able to achieve the results you are looking for by inserting a
group header for the Group by day level. set that to have a page break
before and set the visibility for the group header to hidden for the first
record of dataset.
"kmp" wrote:
> Thanks for the reply - this worked. But I have another problem. When I page
> break per day, I would also like to see the summary statistics towards the
> bottom of the page per day.
> Since I have 2 matrices - one for detailed data (where I page break per day
> basis) and another one for the summary statistics (sum, min, max etc.), when
> I do a page break on the first matrix, I am not able to get the second matrix
> on the same page (because of the page break on the first one). Is there
> anyway I can see both the matrices on one page? Any help will be appreciated.
> "johnE" wrote:
> > Create a function in SQL server to strip off the time from the Datetime
> > field. Do this by coverting to to char(8) then back to datetime. Use this
> > function to give you the date only. Group by this function and put a page
> > break after that grouping level.
> >
> > "kmp" wrote:
> >
> > > Hi,
> > > This is what I am trying to achieve, but in vain.
> > >
> > > I have a report that has to be grouped by "Day" - I have hourly data for a
> > > day which can span for several days based on the report start and end time.
> > > For example if the report duration is 10/01/2004 to 10/05/2004, then I must
> > > see 10/01/2004 day's worth of data (hour 0 thro 23) on a single page with
> > > summary statistics for that day towards the bottom of the page. And then
> > > 10/02/2004 on the next page and so on and so forth.
> > >
> > > I have 2 matrices - one for detailed data for the day and another one for
> > > the summary (sum, avg, min, max) for the same day. I am trying to group by
> > > "Year", "Month" and "Day" but this is not working. Can anyone please let me
> > > know how to make this work? Any help will be highly appreciated.
> > >
> > > Thanks