Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: [RESOLVED] Group by Shift and Not Date Post Reply Post New Topic
Author Message
KCowden
Newbie
Newbie


Joined: 15 Jun 2016
Location: United States
Online Status: Offline
Posts: 13
Quote KCowden Replybullet Topic: [RESOLVED] Group by Shift and Not Date
    Posted: 08 Aug 2016 at 3:46am
Good morning all,

I have Google fooed until I'm sick to my stomach and I am hoping someone here can help.

I am researching data related to a particular type of incident for my fire department.

The data I am pulling includes a date/time field and I have group #1 which groups the data by month and then group #2 which is by day (daily).

What I am hoping for, is to group data by 24 hour periods 07:00:00 - 06:59:59.

I am using CR 2008 and I would have bet that there was an option for grouping by shift (hour span) but if there is, I missed it.

Please help if you can.

Edited by KCowden - 23 Aug 2016 at 9:41am
There is no situation so terrible that you can't make it worse.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 08 Aug 2016 at 6:17am
You can group by day, but it appears your 'day' would not match the regular 24 hour day.  I am trying to think how you could make formula to do this. If I come up with something, I will let you know.
IP IP Logged
KCowden
Newbie
Newbie


Joined: 15 Jun 2016
Location: United States
Online Status: Offline
Posts: 13
Quote KCowden Replybullet Posted: 09 Aug 2016 at 4:57am
kevlray,

Thank you. It's a head scratcher and I'm glad to know it's not some simple thing I missed as a noob.
There is no situation so terrible that you can't make it worse.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 Aug 2016 at 4:47am
maybe adding time using DATEADD() to your original field and grouping on that result but displaying the original field?
IP IP Logged
KCowden
Newbie
Newbie


Joined: 15 Jun 2016
Location: United States
Online Status: Offline
Posts: 13
Quote KCowden Replybullet Posted: 10 Aug 2016 at 5:35am
Originally posted by DBlank

maybe adding time using DATEADD() to your original field and grouping on that result but displaying the original field?


DBlank,

Thank you for your response. I am hoping you could explain further. I'm okay with SQL and some web languages but a complete noob when it comes to CR or VB syntax.

Would I use the DATEADD function in a selection formula? How would I phrase it to include the next day's entries before a set time (0700)?

It is a date/time field {sytem.response_date}, E.G. yyyy-mm-dd hh:mm:ss.

I am grateful for your response but if you can suffer a little hand holding here, I'd be obliged.

Edited by KCowden - 10 Aug 2016 at 5:37am
There is no situation so terrible that you can't make it worse.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 Aug 2016 at 9:31am
If I understood you correctly you wanted to group by a 'day' but that the 'day' was really 7 am to 6:59:59 am the following day.
If you use a formula field on that datetime field you can 'shift the times back' by 7 hours essentially making your values utilize a regular 'day'. If you group on this result you can group into the 'correct day' but still display (and use) the original values.

example:
DATEADD("h",-7,{table.datetimefield})
IP IP Logged
KCowden
Newbie
Newbie


Joined: 15 Jun 2016
Location: United States
Online Status: Offline
Posts: 13
Quote KCowden Replybullet Posted: 12 Aug 2016 at 8:20am
Okay! Excellent solution. THANK YOU!

A little detail. I thought I needed to add the field to the report but have learned I can group on it without including the field in the report.

There is an anomaly though not important. A few of the date/time incidents are outset to the bottom of that shift's details. But it's not a big deal, just weird.
There is no situation so terrible that you can't make it worse.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.