Grouping with Reporting services

Can anyone help me with grouping in Reporting services. I am more used to crystal reports drill-down method

For example i have a simple table that has timestamp and three other columns. I want to drill down by (after Grouping) for Day-Then- Hour and then show the details for three columns. And also group by one of the columns, if i get above working.

All i could do with Reporting services was stepped down model, but i have same dates repeated more than once. i would like them to be grouped under day and then show time stamps for times of day .

-Thanks all

You can do this by creating a Table and adding a grouping, which is grouped by the date portion of the timestamp field, and then place the detail fields in the detail section, as you are now. The expressions should be something like the following:

Grouping expression:
=Fields!TimeStamp.Value.Date

Grouping header textbox for the date:

=Fields!TimeStamp.Value.ToString("D")

Detail textbox for the time:
=Fields!TimeStamp.Value.ToString("T")

Ian

Grouping with Page Break

I have a report that shows the monthwise details, i have performed this using the table control. the requirement was to have a page break after each month detail. End of each group display the monthwise total. This is absolutely working fine.

My problem is, I am suppose to display the Report total also. I used the Sum in the Table footer. The sum is coming fine the only problem that i have is it is printing on the a New Page instead of the Last Page.

Please suggest.

Hi,

you have to use the Group Footer, not the Table Footer. Edit Group -> include group footer:

Greez Daniel

|||

I have to display the Report Sub Total of the Group that i am already doing in the Group Footer, Where as I am wanting to display the Main Total of the Report, It wont work in the Group Footer as it is the Full Report total.

I tried to add a new Group fo the report just to have a main group and the other one as sub group, but it also did not work.

|||Try setting the RepeatOnNewPage property in the table footer to true. This will show the Main Total on every page.

Grouping with Page Break

I have a report that shows the monthwise details, i have performed this using the table control. the requirement was to have a page break after each month detail. End of each group display the monthwise total. This is absolutely working fine.

My problem is, I am suppose to display the Report total also. I used the Sum in the Table footer. The sum is coming fine the only problem that i have is it is printing on the a New Page instead of the Last Page.

Please suggest.

Hi,

you have to use the Group Footer, not the Table Footer. Edit Group -> include group footer:

Greez Daniel

|||

I have to display the Report Sub Total of the Group that i am already doing in the Group Footer, Where as I am wanting to display the Main Total of the Report, It wont work in the Group Footer as it is the Full Report total.

I tried to add a new Group fo the report just to have a main group and the other one as sub group, but it also did not work.

|||Try setting the RepeatOnNewPage property in the table footer to true. This will show the Main Total on every page.sql

Grouping with non-existent data

I need to build a report that groups data by a given time frame increment (15
min, 30 min, 1 hour). But I need the report to show every increment weather
there is data in my result set or not. So if I was to run my query for the
time frame of 12pm - 1pm and got the following data:
12:00, 1, 1
12:10, 1, 1
12:20, 1, 1
12:50 1, 1
And was asked to display my report using the 15 minute increment I should
get this back:
12:00 - 2 records
12:15 - 1 record
12:30 - 0 record
12:45 - 1 records
Now my grouping expression works to group the data (Hours are limited in the
query):
= ((DATEPART("h", Fields!EntryDate.Value))
* (60 / Parameters!Granularity.Value)
+ (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
Parameters!Granularity.Value)))
However if there isn't any data in one of the incremental groups that group
isn't displayed. How can I make the grouping work so that even if there is
no data for my group I still get a row?
Let me know if this doesn't make any sence and I'll try to clarify it.I have to do this and it is not simple. You need to have the data there to
be able to show it. This means you need to have a stored procedure that
fills in the missing rows (or more likely, puts in all the rows and then
joins it with the query so you have all the times shown).
RS can't group on something that isn't there.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"eoghain" <eoghain@.discussions.microsoft.com> wrote in message
news:E5317CC5-E951-43D0-A784-FE9840B80D8B@.microsoft.com...
> I need to build a report that groups data by a given time frame increment
(15
> min, 30 min, 1 hour). But I need the report to show every increment
weather
> there is data in my result set or not. So if I was to run my query for
the
> time frame of 12pm - 1pm and got the following data:
> 12:00, 1, 1
> 12:10, 1, 1
> 12:20, 1, 1
> 12:50 1, 1
> And was asked to display my report using the 15 minute increment I should
> get this back:
> 12:00 - 2 records
> 12:15 - 1 record
> 12:30 - 0 record
> 12:45 - 1 records
> Now my grouping expression works to group the data (Hours are limited in
the
> query):
> = ((DATEPART("h", Fields!EntryDate.Value))
> * (60 / Parameters!Granularity.Value)
> + (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
> Parameters!Granularity.Value)))
> However if there isn't any data in one of the incremental groups that
group
> isn't displayed. How can I make the grouping work so that even if there
is
> no data for my group I still get a row?
> Let me know if this doesn't make any sence and I'll try to clarify it.
>|||I was afraid of that. I guess I'll have to figure out how to build a
query/stored procedure that will return me the proper time sets given a
start/end and increment.
Thanks for your help.
"Bruce L-C [MVP]" wrote:
> I have to do this and it is not simple. You need to have the data there to
> be able to show it. This means you need to have a stored procedure that
> fills in the missing rows (or more likely, puts in all the rows and then
> joins it with the query so you have all the times shown).
> RS can't group on something that isn't there.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "eoghain" <eoghain@.discussions.microsoft.com> wrote in message
> news:E5317CC5-E951-43D0-A784-FE9840B80D8B@.microsoft.com...
> > I need to build a report that groups data by a given time frame increment
> (15
> > min, 30 min, 1 hour). But I need the report to show every increment
> weather
> > there is data in my result set or not. So if I was to run my query for
> the
> > time frame of 12pm - 1pm and got the following data:
> >
> > 12:00, 1, 1
> > 12:10, 1, 1
> > 12:20, 1, 1
> > 12:50 1, 1
> >
> > And was asked to display my report using the 15 minute increment I should
> > get this back:
> >
> > 12:00 - 2 records
> > 12:15 - 1 record
> > 12:30 - 0 record
> > 12:45 - 1 records
> >
> > Now my grouping expression works to group the data (Hours are limited in
> the
> > query):
> > = ((DATEPART("h", Fields!EntryDate.Value))
> > * (60 / Parameters!Granularity.Value)
> > + (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
> > Parameters!Granularity.Value)))
> >
> > However if there isn't any data in one of the incremental groups that
> group
> > isn't displayed. How can I make the grouping work so that even if there
> is
> > no data for my group I still get a row?
> >
> > Let me know if this doesn't make any sence and I'll try to clarify it.
> >
>
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:%23uBQ2EqRFHA.3296@.TK2MSFTNGP15.phx.gbl...
>I have to do this and it is not simple. You need to have the data there to
> be able to show it. This means you need to have a stored procedure that
> fills in the missing rows (or more likely, puts in all the rows and then
> joins it with the query so you have all the times shown).
> RS can't group on something that isn't there.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "eoghain" <eoghain@.discussions.microsoft.com> wrote in message
> news:E5317CC5-E951-43D0-A784-FE9840B80D8B@.microsoft.com...
>> I need to build a report that groups data by a given time frame increment
> (15
>> min, 30 min, 1 hour). But I need the report to show every increment
> weather
>> there is data in my result set or not. So if I was to run my query for
> the
>> time frame of 12pm - 1pm and got the following data:
>> 12:00, 1, 1
>> 12:10, 1, 1
>> 12:20, 1, 1
>> 12:50 1, 1
>> And was asked to display my report using the 15 minute increment I should
>> get this back:
>> 12:00 - 2 records
>> 12:15 - 1 record
>> 12:30 - 0 record
>> 12:45 - 1 records
>> Now my grouping expression works to group the data (Hours are limited in
> the
>> query):
>> = ((DATEPART("h", Fields!EntryDate.Value))
>> * (60 / Parameters!Granularity.Value)
>> + (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
>> Parameters!Granularity.Value)))
>> However if there isn't any data in one of the incremental groups that
> group
>> isn't displayed. How can I make the grouping work so that even if there
> is
>> no data for my group I still get a row?
>> Let me know if this doesn't make any sence and I'll try to clarify it.
>|||But you can do it with a query, i have done that sometimes, if you giove me
some tim i´ll try to find out if i have it right away, just email me to give
a reminder.
Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:%23uBQ2EqRFHA.3296@.TK2MSFTNGP15.phx.gbl...
>I have to do this and it is not simple. You need to have the data there to
> be able to show it. This means you need to have a stored procedure that
> fills in the missing rows (or more likely, puts in all the rows and then
> joins it with the query so you have all the times shown).
> RS can't group on something that isn't there.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "eoghain" <eoghain@.discussions.microsoft.com> wrote in message
> news:E5317CC5-E951-43D0-A784-FE9840B80D8B@.microsoft.com...
>> I need to build a report that groups data by a given time frame increment
> (15
>> min, 30 min, 1 hour). But I need the report to show every increment
> weather
>> there is data in my result set or not. So if I was to run my query for
> the
>> time frame of 12pm - 1pm and got the following data:
>> 12:00, 1, 1
>> 12:10, 1, 1
>> 12:20, 1, 1
>> 12:50 1, 1
>> And was asked to display my report using the 15 minute increment I should
>> get this back:
>> 12:00 - 2 records
>> 12:15 - 1 record
>> 12:30 - 0 record
>> 12:45 - 1 records
>> Now my grouping expression works to group the data (Hours are limited in
> the
>> query):
>> = ((DATEPART("h", Fields!EntryDate.Value))
>> * (60 / Parameters!Granularity.Value)
>> + (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
>> Parameters!Granularity.Value)))
>> However if there isn't any data in one of the incremental groups that
> group
>> isn't displayed. How can I make the grouping work so that even if there
> is
>> no data for my group I still get a row?
>> Let me know if this doesn't make any sence and I'll try to clarify it.
>

Grouping with a full join

Hi,
I would like to know how to group the Amount of both tables while maintaing
all Ids.
-- Correct results for Table1
select
Table1.id
,sum(Table1.Amount) as AmountTable1
from Table1
group by Table1.id
order by 1
-- Correct results for Table2
select
Table2.id
,sum(Table2.Amount) as AmountTable2
from Table2
group by Table2.id
order by 1
-- How do I combine both results?
select
Table1.id
,sum(Table1.Amount) + sum(Table2.Amount) as AmountBoth
from Table1
left join Table2 on Table1.id = Table2.id
group by Table1.id
order by 1
/*
create table Table1 (Id int, Amount int)
create table Table2 (Id int, Amount int)
insert Table1 select 1, 100
insert Table1 select 2, 200
insert Table1 select 3, 300
insert Table1 select 4, 400
insert Table2 select 5, 500
insert Table2 select 2, 100
insert Table2 select 4, 400
insert Table2 select 6, 600
--drop table Table1
--drop table Table2
*/
---
select
coalesce(Table1.id,Table2.id) as id
,sum(coalesce(Table1.Amount,0)) + sum(coalesce(Table2.Amount,0)) as
AmountBoth
from Table1
full outer join Table2 on Table1.id = Table2.id
group by coalesce(Table1.id,Table2.id)
order by 1|||Great, thank you!
<markc600@.hotmail.com> wrote in message
news:1146119352.959456.161230@.t31g2000cwb.googlegroups.com...
>
> select
> coalesce(Table1.id,Table2.id) as id
> ,sum(coalesce(Table1.Amount,0)) + sum(coalesce(Table2.Amount,0)) as
> AmountBoth
> from Table1
> full outer join Table2 on Table1.id = Table2.id
> group by coalesce(Table1.id,Table2.id)
> order by 1
>|||You should be aware that this solution works when there is a one to
one relation ship between the two tables, but not if there is a one to
many (or many to many) relationship.
Here are two alternatives that avoid that problem.
SELECT id, sum(Amount) as Amount
FROM (select id, sum(Amount) as Amount
from Table1
group by id
UNION ALL
select id, sum(Amount) as Amount
from Table1
group by id) as Combo
GROUP BY id
ORDER BY 1
SELECT COALESCE(T1.id,T2.id),
T1.Amount + T2.Amount as Amount
FROM (select id, sum(Amount) as Amount
from Table1
group by id) as T1
FULL OUTER
JOIN (select id, sum(Amount) as Amount
from Table1
group by id) as T2
ON T1.id = T2.id
ORDER BY 1
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 09:53:11 +0300, "Yan" <yanive@.rediffmail.com>
wrote:

>Great, thank you!
>
><markc600@.hotmail.com> wrote in message
>news:1146119352.959456.161230@.t31g2000cwb.googlegroups.com...
>

Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
Rothery Telephone call out 26/09/05 13:00:00
Ginnelly Auto Order 26/09/05 12:55:03
Aldcroft Customer Services 26/09/05 12:55:00
Aldcroft Auto Order 26/09/05 12:54:20
Broadbent Auto Reminder 26/09/05 12:54:07
Broadbent Auto Reminder 26/09/05 12:51:23
Pickles Telephone call out 26/09/05 12:50:00
Broadbent Auto Reminder 26/09/05 12:49:50
Pickles Telephone call in 26/09/05 12:43:00
Broadbent Auto Reminder 26/09/05 12:41:22
Race Auto Appointment 26/09/05 12:41:13
Race Auto Appointment 26/09/05 12:36:27
Broadbent Auto Reminder 26/09/05 12:36:04
Broadbent Auto Reminder 26/09/05 12:33:38
Broadbent Auto Reminder 26/09/05 12:30:01
Booth Telephone call out 26/09/05 12:25:32
Booth Telephone call out 26/09/05 12:23:00
Prior Auto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>

Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.
Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.
|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
RotheryTelephone call out26/09/05 13:00:00
GinnellyAuto Order 26/09/05 12:55:03
AldcroftCustomer Services26/09/05 12:55:00
AldcroftAuto Order 26/09/05 12:54:20
BroadbentAuto Reminder26/09/05 12:54:07
BroadbentAuto Reminder26/09/05 12:51:23
PicklesTelephone call out26/09/05 12:50:00
BroadbentAuto Reminder26/09/05 12:49:50
PicklesTelephone call in26/09/05 12:43:00
BroadbentAuto Reminder26/09/05 12:41:22
RaceAuto Appointment26/09/05 12:41:13
RaceAuto Appointment26/09/05 12:36:27
BroadbentAuto Reminder26/09/05 12:36:04
BroadbentAuto Reminder26/09/05 12:33:38
BroadbentAuto Reminder26/09/05 12:30:01
BoothTelephone call out26/09/05 12:25:32
BoothTelephone call out26/09/05 12:23:00
PriorAuto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.
|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>