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.
>