| Author |
Message |
martinz
Newbie
Joined: 27 Feb 2014
Online Status: Offline
Posts: 12
|
   Topic: Group by EITHER of two date fields
Posted: 27 Feb 2014 at 10:18am |
|
I have a report of incident tickets. Each record/ticket has an Open date and a Close date. I am currently using the native grouping in CR to select the Open date and to group by month. That works fine but does not list tickets that are closed in that same month (unless they were opened and closed in the same month). Is there a way to group based on either the open date or close date being in the same month. So the report would list all tickets that were either opened or closed (and of course opened and closed) by month?
TIA - I'm having trouble with this one...
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 27 Feb 2014 at 11:04am |
Not sure you can accomplish exaclty what you want with grouping .
If you want to count one row more than once then grouping will not exacly do what you want as the row will always only exist in one group.
If you can, you might consider using a stored proc (or a crystal command with a union) to build a table with your ticket ID and one date field and a date type identifier (open or close). The you can group on the single date field and have the ticket appear in more than one month's group. basically split an open and closed ticket into 2 rows.
Edited by DBlank - 27 Feb 2014 at 11:05am
|
|
IP Logged |
|
martinz
Newbie
Joined: 27 Feb 2014
Online Status: Offline
Posts: 12
|
Posted: 27 Feb 2014 at 1:14pm |
|
Thank you for the reply. I get your thought process - thinking about it quickly I'd also have to adjust my total counts as I would be splitting tickets in two. Is there a way to conditionally group... the logic is pretty simple, I'm just not sure how or where it might be done. For example:
Jan:
If Open or Close is Jan add record
Feb:
If Open or Close is Feb add record
I was trying to see what the basic grouping looks like when done in the UI, for example how it groups all the running months. Because it would be that same code with an added Boolean OR to go from (Open) to (Open or Close). I wasn't able to find what that native grouping UI code looks like.
|
|
IP Logged |
|
martinz
Newbie
Joined: 27 Feb 2014
Online Status: Offline
Posts: 12
|
Posted: 28 Feb 2014 at 3:59am |
|
I think I figured it out. Has a bit of unnecessary redundancy (just in case) but seems to work. I just made the following formula and grouped on it by month:
IF (IsNull({Work_Orders.Close Date & Time})) then {Work_Orders.Open Date & Time} ELSE
IF (IsNull({Work_Orders.Open Date & Time})) then {Work_Orders.Close Date & Time} ELSE
IF ({Work_Orders.Open Date & Time} >= {Work_Orders.Close Date & Time}) then {Work_Orders.Open Date & Time} ELSE
IF ({Work_Orders.Close Date & Time} >= {Work_Orders.Open Date & Time}) then {Work_Orders.Close Date & Time} ELSE
{Work_Orders.Open Date & Time}
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Feb 2014 at 5:23am |
just so you understand,
that formula is making a record appear in one month only, it will not make a row appear in two groups (e.g. 2 months)
example Open date = 1/15/2014 and close date =2/15/2014
this record will not be apart of the Jan group at all and only appear in the Feb group.
I thought you wanted them to appear in both months if the dates crossed over months.
Edited by DBlank - 28 Feb 2014 at 5:23am
|
|
IP Logged |
|
martinz
Newbie
Joined: 27 Feb 2014
Online Status: Offline
Posts: 12
|
Posted: 28 Feb 2014 at 7:13am |
You are absolutely correct! I was just coming back here to remove or update. I was checking the output and came to that conclusion - faulty logic on my part... I guess I'll have to generate two records.
 But you certainly understand what I'm trying to do - Thanks!
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 28 Feb 2014 at 8:12am |
|
if you had a table of months, you would think that you link to that for the starting and ending dates, and that would give you duplicate records, then your group would be just the month value from the common table.
might/should work.
|
|
IP Logged |
|
martinz
Newbie
Joined: 27 Feb 2014
Online Status: Offline
Posts: 12
|
Posted: 28 Feb 2014 at 9:23am |
|
The more I think about this, I think DBlank is right - because by grouping with only one record it will only ever get evaluated once. I'm a noobe to CR so doing the whole table of duplicate records is probably a big lift for me (but I'll do some research). It seems such an easy requirement but the way grouping works, I don't think that is going to be an option unless I have a single record for each open and each close; but then that will add two records in a month where the open and close happened in the same month. What I really want is a single record line in a month showing the open and close - if either date fell in that month. Which would mean that record could show up two times on the report but only a single time under each different month.
I think the Group logic is backwards from what I need. I need to go through all the months and pull records into the month based on an either/or open/close date falling in that month. I think CR Grouping goes through the records (one time) and puts it into a Month which can be an either/or but it is only going to do it one time.
Wow this is getting harder that I thought it would be...
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Feb 2014 at 10:11am |
lockwelle was suggesting another way to create two rows when applicable (if the record is open and closed).
addin the table twice with an intermediate table of months would allow you to join the table_1 to months on open date and table_2 to months on close date resulting in two rows of data that could be joined on the month table value that they share.
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 28 Feb 2014 at 10:31am |
|
yeah, I guess that is how it would have to be done in CR. I was thinking in SQL and you could join with an OR...but that's not available in CR.
I was just trying to think how to multiple up the rows easily in CR.
To get started you could create the month table in the report using the command object.
Create a command like:
select mName = 'January', mNo = 1
union Select 'February', 2
union Select 'March', 3
etc
then link the tables as DBlank described...
again, it should work
|
|
IP Logged |
|
|
|
