Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 777
  • Last Modified:

Crystal Reports, group by question

Ok, I thought i was pretty savvy but I am beating my head against a wall on this one.
I have raw data in a table that lists values by station, by date/hour as below:

id             name                      timestring              varvalue
81740      Log_Hours_BLAST1      12/20/13 7:59 AM      27.6
81741      Log_Hours_BLAST1      12/20/13 8:59 AM      23.66667
81742      Log_Hours_BLAST1      12/20/13 9:59 AM      49.23333
81743      Log_Hours_BLAST1      12/20/13 10:59 AM      15.21667
81744      Log_Hours_BLAST1      12/20/13 11:59 AM      8.633333
81745      Log_Hours_BLAST1      12/20/13 12:59 PM      48.71667

I need to create a group based on this data which incorporates a 24 hour period beginning at 8am on day 1 and ending at 7:59:59am on day 2... in other words a group should run from:
12/20/13 8:00:00AM - 12/21/13 7:59:59AM
I then apply various formulas and running totals to the varvalue field in the group footer.
Can anyone help with this?
Thank you.
0
JP_TechGroup
Asked:
JP_TechGroup
  • 7
  • 4
  • 4
  • +1
4 Solutions
 
vastoCommented:
Create a formula

Datetimevar dt;
dt :=DateAdd("h",-8,DatetimeValue(timestring));
DateValue(Year(dt), month(dt), day(dt))

and us it to group the data
0
 
JP_TechGroupAuthor Commented:
...
0
 
JP_TechGroupAuthor Commented:
This does not work. When grouping by this formula, it returns a standard 24 hour period beginning at 1:00am and ending at 12:59:59pm.  Thanks.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
vastoCommented:
the formula will return a date which is 8 hours before the timerstring

12/20/13 7:59 AM will be 12/19/2013
12/20/13 8:00 AM will be 12/20/2013

Initially I posted a formula which was not dealing with the time offset (-8 hours), but I changed it later. Please try the current formula
0
 
JP_TechGroupAuthor Commented:
Yes sir and it still returns a standard 24hour period as above.
0
 
JP_TechGroupAuthor Commented:
To put it another way, I need to essentially re-define what Crystal considers a "day".
My day needs to run from 8am to 7:59am.
0
 
vastoCommented:
I probably do not understand correctly , but let me explain the logic of the formula:

Datetimevar dt;
dt :=DateAdd("h",-8,DatetimeValue(timestring));
DateValue(Year(dt), month(dt), day(dt))



DatetimeValue(timestring) will convert string "12/20/13 7:59 AM" to a date 12/20/13 7:59 AM

DateAdd("h",-8,DatetimeValue(12/20/13 7:59 AM));
will deduct 8 hours and will return

12/19/13 11:59 PM

DateValue(Year(dt), month(dt), day(dt))

will cut the time part so the returned date will be  12/19/13

You can use shorter formula:
DateValue(DateAdd("h",-8,DatetimeValue(timestring)))
0
 
mlmccCommented:
YOu need to group on vasto's formula and use it whenever the summaries are based on the group.  However you will want to display the real field on the report.

mlmcc
0
 
JP_TechGroupAuthor Commented:
Thank you both for your comments, but that formula does not accomplish what I need done.
0
 
mlmccCommented:
What is wrong?

Can you show us the report output and why it is wrong?

mlmcc
0
 
JP_TechGroupAuthor Commented:
Ok, let me try this from another angle. Consider, there are 24 hours in a calendar day.
So, the first of January begins at 1:00:00AM and ends at 12:59:59AM.
If I have 20 records for the 1st of January with different time references, they will all appear in a group created on this field, i.e. they will appear under the 1st of January Group.

What I need is to handle the way a group handles the time and date for a 24 hour period.
I require my 24 hour range to begin at 8:00:00am on January 1st and end on 7:59:59am January 2nd. The group heading will be January 1 but it will in fact encompass two calendar days. Does that clarify my question?
0
 
mlmccCommented:
Yes, and I believe the formula given does that.  It apparently doesn't so we need to see what is the problem.

The group name/column title, the groups should be based on the  formula but when you need to show the time of the service then the field is used.

mlmcc
0
 
mlmccCommented:
Given the data you have above

id             name                      timestring              varvalue
81740      Log_Hours_BLAST1      12/20/13 7:59 AM      27.6                           Dec 19 group

81741      Log_Hours_BLAST1      12/20/13 8:59 AM      23.66667                   Dec 20 group
81742      Log_Hours_BLAST1      12/20/13 9:59 AM      49.23333                   Dec 20 group
81743      Log_Hours_BLAST1      12/20/13 10:59 AM      15.21667                 Dec 20 group
81744      Log_Hours_BLAST1      12/20/13 11:59 AM      8.633333                 Dec 20 group
81745      Log_Hours_BLAST1      12/20/13 12:59 PM      48.71667                 Dec 20 group
New data
81746      Log_Hours_BLAST1      12/20/13 4:59 PM      48.71667                   Dec 20 group
81747      Log_Hours_BLAST1      12/20/13 6:59 PM      48.71667                   Dec 20 group
81748      Log_Hours_BLAST1      12/20/13 8:59 PM      48.71667                   Dec 20 group
81749      Log_Hours_BLAST1      12/20/13 10:59 PM      48.71667                 Dec 20 group
81750      Log_Hours_BLAST1      12/20/13 11:59 PM      48.71667                 Dec 20 group
81751      Log_Hours_BLAST1      12/21/13  0:59 AM      48.71667                  Dec 20 group
81752      Log_Hours_BLAST1      12/21/13  1:59 AM      48.71667                  Dec 20 group
81753      Log_Hours_BLAST1      12/21/13  2:59 AM      48.71667                  Dec 20 group
81754      Log_Hours_BLAST1      12/21/13  5:59 AM      48.71667                  Dec 20 group
81755      Log_Hours_BLAST1      12/21/13  7:59 AM      48.71667                  Dec 20 group
81756      Log_Hours_BLAST1      12/21/13  7:59 AM      48.71667                  Dec 20 group

81757      Log_Hours_BLAST1      12/21/13  8:01 AM      48.71667                  Dec 21 group

Is that correct for the date associated with each record?

mlmcc
0
 
James0628Commented:
First of all, just to make sure that we're not missing something, you said:

 > So, the first of January begins at 1:00:00AM and ends at 12:59:59AM.

 The day in CR actually begins at 00:00:00 AM (midnight), and ends at 11:59:59 PM.  If you're expecting something else, that may be part of the problem.


 That issue aside, as has already been said, the formula that vasto posted is trying to do just what you described.  If it's not clear after mlmcc's examples, maybe a different example will help.

 Let's say that you wanted to group a report by the week and you wanted the week to be from Monday to Sunday.  The week in CR is normally from Sunday to Saturday.  One way that you could handle that would be to create a formula that subtracted 1 day from the date field, and group on that formula instead.  Then, for example, dates from 01/06 - 01/12 (Mon - Sun) would, for grouping purposes, be seen as 01/05 - 01/11 (Sun - Sat, ie. the CR week).  If you use the formula on the report, you'll see the altered dates, so you'd use the field instead, or add 1 day to the result from the formula, for display purposes, so that it reflects the actual date.

 vasto's formula is doing the same kind of thing, but it's shifting the time by 8 hours, instead of shifting the date by 1 day.

 James
0
 
JP_TechGroupAuthor Commented:
Wow, do I need to take a vacation! The grouping includes the prior day for some reason. By supressing dates prior to the specified start date in my parameters, it works perfectly. Thank you!
0
 
vastoCommented:
JP_TechGroup, I am sorry I was not around to explain the formula. However I can see that mlmcc and James0628 handled it. It is fair to share the points between the experts who helped you to resolve the issue. My answer happened to be the first correct one , but  mlmcc and James0628 spent time helping you after that. I will appreciate if you spent few minutes to reopen the question and share the points.
Thanks!
0
 
James0628Commented:
FWIW, while I certainly have no objection to the points being split, as far as I'm concerned, it's not necessary.  vasto's formula was the solution.  I was just trying to explain why.  :-)

 James
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now