| Author |
Message |
keeru
Newbie
Joined: 20 May 2012
Location: India
Online Status: Offline
Posts: 3
|
  
Topic: Hourly Report even when there is no data
Posted: 20 May 2012 at 6:53pm |
|
Hi,
I need your help to fix the below issue in my Crystal Development pls.
I need to show workload report in below format
Hours count of events
01:00 to 02:00 am 10
02:00 to 03:00 am 2
03:00 to 04:00 am 0
.
.
23:00 to 24:00 pm
my issue is iam able to group by hourly and put summery field to get the count, but the main prob is i need to display all the hours in above format even when there is no data during a perticular hour for ex:03:00 to 04:00 am there is no data so need to display 00....
Unable to achieve this.found in some blog that keeping 24 sub reports would resolve this,but unable to understand where to do that...
Thank you so much for your help in advance
and one more requirement is that i need to show this data graphically also...is this possible..pls help!
Edited by keeru - 20 May 2012 at 7:53pm
|
 IP Logged |
|
|
|
dbodell
Groupie
Joined: 08 Nov 2011
Location: United States
Online Status: Offline
Posts: 53
|
Posted: 21 May 2012 at 11:42am |
|
You need a table of all dates and hours. For your even data, create a summary view which has the date and hour of the day associated with that record as two separate fields. For example, if the date and time on the record is 03/01/2012 14:25:33 then your new fields would be EventDate (value would be 03/01/2012) and EventTime (14:00:00). Group on the event date and event hour fields so you can count the number of records. Then join your date/hour table to your event data using a left outer join on the date and hour fields. You will have a record for each date and hour and if there is no event, you'll get a null. So to handle that, in your select statement say isnull(event.hour, 0). This may be confusing so if it is, let me know. I can probably do a better explanation with an example.
|
|
Thanks,
D. Bodell
|
| IP Logged |
|
dbodell
Groupie
Joined: 08 Nov 2011
Location: United States
Online Status: Offline
Posts: 53
|
Posted: 21 May 2012 at 11:44am |
|
Once you get your counts by the date and hour, create a line chart in the report that uses the axes as date/hour for X and quantity for Y. That's just one way to graphically represent it.
|
|
Thanks,
D. Bodell
|
| IP Logged |
|
keeru
Newbie
Joined: 20 May 2012
Location: India
Online Status: Offline
Posts: 3
|
Posted: 21 May 2012 at 4:42pm |
|
Hi Bodell,
Thank you so much for your quick reply.
I have a limitation from application end,cannot create another table with even data(all hours).so is the problem :(...
Is there a way to handle this directly in crystal instead of creating additional table in application and populating the datehours with or without data.
Please advice.
|
| IP Logged |
|
Gurbs
Senior Member
Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
|
Posted: 21 May 2012 at 10:30pm |
|
Do you have access to run SQL queries on the database? If so, you could create a view instead of a table. Works the same way
|
| IP Logged |
|
keeru
Newbie
Joined: 20 May 2012
Location: India
Online Status: Offline
Posts: 3
|
Posted: 21 May 2012 at 11:20pm |
|
Hi,
Yes I do have access to Database.
Can you please explain me with an example(how to design in Crystal) i will try and check
sry for miss communication..i wanted to say that..i will create view in DB, but can u help me out to design the fields in CR.like with a example.
Thank You
Edited by keeru - 21 May 2012 at 11:34pm
|
| IP Logged |
|
Gurbs
Senior Member
Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
|
Posted: 21 May 2012 at 11:23pm |
|
A view isn't in Crystal, it is in the database. what dbodell suggested, to create your own table, you could create your own view in the database. Just create your query the way dbodell suggested, and put 'Create or replace view "Viewname" as' in front of it. You could then add the view to Crystal the same way as you add tables, and use them the same way as you use tables
|
| IP Logged |
|
|
|
