Solved

Crystal Reports, group by question

Posted on 2014-01-10
18
760 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
  • +1
18 Comments
 
LVL 18

Assisted Solution

by:vasto
vasto earned 250 total points
ID: 39771309
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
ID: 39771408
...
0
 

Author Comment

by:JP_TechGroup
ID: 39771435
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!

 
LVL 18

Expert Comment

by:vasto
ID: 39771445
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
ID: 39771469
Yes sir and it still returns a standard 24hour period as above.
0
 

Author Comment

by:JP_TechGroup
ID: 39771475
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
ID: 39771514
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
ID: 39771675
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
 

Author Comment

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

Expert Comment

by:mlmcc
ID: 39772065
What is wrong?

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

mlmcc
0
 

Author Comment

by:JP_TechGroup
ID: 39772094
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
ID: 39772269
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
ID: 39772294
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 35

Assisted Solution

by:James0628
James0628 earned 125 total points
ID: 39773202
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
ID: 39774954
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
ID: 39777318
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 35

Expert Comment

by:James0628
ID: 39778061
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In this post we will learn different types of Android Layout and some basics of an Android App.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

762 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