?
Solved

Grouping table by hour skipping Hour if no Data

Posted on 2011-10-20
27
Medium Priority
?
197 Views
Last Modified: 2012-08-13
I am grouping a table by Hour for each previous day, but find that if there was no data within that specific Hour Crystal actually skips that Hour, is there a way that I can show the Hour and just fill the field with a Zero ?
0
Comment
Question by:John-S Pretorius
  • 16
  • 11
27 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 37004260
0
 

Author Comment

by:John-S Pretorius
ID: 37005847
Thank you mlmcc, I would like some help here please.

Does the fact that I'm grouping the {EntriesExit.time} by Hour mean I won't be able to use the work around as pointed out in the document :

NOTE ======
· @date cannot be used for grouping or summaries because it is a print-time formula.

After grouping {EntriesExit.time} for the previous day I'm actually suppressing the Details section and just use the Group Header (Which will show the Hour) I then use totals for Entry's and % formula's to display occupancy and place them next to each Hour to form my report.

unless my approach is wrong...What is your thoughts ?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37007199
You won't be able to group on the formula.

WHat you have is

You want
0800 - SOME DATA

0900 - SOME DATA

1000 - no data

1100 - SOME DATA

mlmcc
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:John-S Pretorius
ID: 37009844
That is what I'm looking to see, but crystal skips the Hours if there is no data

0800    some data
0900    some data
1100    some data
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37010051
Is your time always a full hour or is Crystal grouping the times by hour?

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 37010057
Crystal Groups By Hour
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37010060
Is there a starting hour for the day?
Is there an ending hour?

Is your time a date time and you are filtering on a date?

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 37010069
The day starts at 00:00
It should break each day in 1,2,3,4 ....24Hrs but there's not always transactions between certain hours (no traffic) and its these Hours that are skipped.
The day ends 23:59:59

I'm using record selection to select previous day > {EntryExit.time}=currentdate-1 then I'm creating a group for {EntryExit.time} showing every Hour (all transactions within that hour grouped in details under the header)
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37010118
Something like this

mlmcc
GroupByHour.rpt
GroupByHour.mdb
0
 

Author Comment

by:John-S Pretorius
ID: 37010125
Something more like the image below : Grouping
0
 

Author Comment

by:John-S Pretorius
ID: 37010136
so for the Hours where there was no data : I want it filled in
   Hour                           Entry         Exit
12:00am                           0               2
 1:am - no activity             0               1
 2:00am - no activity         0               0
 3:00am - no activity         0               0
 4:00am - no activity         0               0
 5:00am                            0               1
0
 

Author Comment

by:John-S Pretorius
ID: 37010150
So this is what I did with your data : see image below  Sorted Change
It seems you where sorting a second time.
0
 

Author Comment

by:John-S Pretorius
ID: 37010151
I would then suppress the details to only show the grouped 'by Hour' below each other.

Is my approach maybe what's causing the problem ?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37010167
Your method will work if you have just 1 days data.

I grouped by the date (day) in case there were several days in the group
It also provided a place to reset the current hour to the 00:00

I then grouped by hour to do the missing hours.

Try it my way
Group by date - DAY
Group by date - HOUR
Your data either in detils or in the group header

mlmcc

0
 

Author Comment

by:John-S Pretorius
ID: 37010177
It's just for one days data (record sort selects previous day) but I stil get Hrs missing if there is no data within those Hrs.



 Results
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37010212
Did you put the formulas I provided in the report?

Are you suppressing anything?

I don't see the GH2A and GH2B as my report has.

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 37010249
Yes I did and still get the gaps in between :

 Results
0
 

Author Comment

by:John-S Pretorius
ID: 37010251
I appologise, I was meant to post this image. Results are still the same. result
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37010260
The formula in GH1B should be in GH2A

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 37011385
Made the change but still not getting a line by Hour, this is what i get if there was 4 missing Hour 'timelines'
00:00   data
05:00   data

Your recomendation fills the following for the missing dates :
00:00 data
01:00-04:00 no data
05:00 data

This is what I'm hoping for though :
00:00 data
01:00 no data
02:00 no data
03:00 no data
04:00 no data
05:00 data
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37012034
Check this one

GroupByHour.rpt

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 37014107
Unless I am doing something wrong, and I have double checked my resuls look a little different, see Image below of results and reports :

 Results
0
 

Author Comment

by:John-S Pretorius
ID: 37014165
It seems that I cannot get to show re-occuring 'no data available' hours- it's only showing the first hour if it doesn't have data and then skips to the next field with data - where yours will show all hours even if it doesn't have data


So close - any ideas please.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37014168
Can you upload your report?

mlmcc
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 37014177
Is the formula formatted to grow?

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 37014178
I will save it with Data, give me a short minute, thank you.
0
 

Author Closing Comment

by:John-S Pretorius
ID: 37014195
Thank you - great work.

I just had to change the formula to 'can grow'
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question