Solved

Crystal Reports, group by question

Posted on 2014-01-10
18
739 Views
Last Modified: 2014-01-21
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
Comment
Question by:JP_TechGroup
  • 7
  • 4
  • 4
  • +1
18 Comments
 
LVL 18

Assisted Solution

by:vasto
vasto earned 250 total points
Comment Utility
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
 

Author Comment

by:JP_TechGroup
Comment Utility
...
0
 

Author Comment

by:JP_TechGroup
Comment Utility
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
 
LVL 18

Expert Comment

by:vasto
Comment Utility
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
 

Author Comment

by:JP_TechGroup
Comment Utility
Yes sir and it still returns a standard 24hour period as above.
0
 

Author Comment

by:JP_TechGroup
Comment Utility
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
 
LVL 18

Accepted Solution

by:
vasto earned 250 total points
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:JP_TechGroup
Comment Utility
Thank you both for your comments, but that formula does not accomplish what I need done.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
What is wrong?

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

mlmcc
0
 

Author Comment

by:JP_TechGroup
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 125 total points
Comment Utility
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
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 125 total points
Comment Utility
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
 

Author Comment

by:JP_TechGroup
Comment Utility
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
 
LVL 18

Expert Comment

by:vasto
Comment Utility
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
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now