Group by EITHER of two date fields
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20518
Printed Date: 04 May 2024 at 9:57am
Topic: Group by EITHER of two date fields
Posted By: martinz
Subject: Group by EITHER of two date fields
Date 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... |
Replies:
Posted By: DBlank
Date 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.
|
Posted By: martinz
Date 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. |
Posted By: martinz
Date 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} |
Posted By: DBlank
Date 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.
|
Posted By: martinz
Date 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! |
Posted By: lockwelle
Date 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. |
Posted By: martinz
Date 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... |
Posted By: DBlank
Date 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.
|
Posted By: lockwelle
Date 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 |
Posted By: martinz
Date Posted: 28 Feb 2014 at 2:12pm
|
Guys thanks so much for the help! I will have to read up on how to do this, and I will. Won't this still cause an issue of having multiple records in the same month. If a ticket is opened and closed in the same month, won't it group two different line records in the month. Sorry, while I do code a bit - CR is fairly new to me. Also if I may, please make sure I'm not going the wrong way to a solution. All I'm trying to show is all tickets opened or closed by month over a number of years. And I was hoping to just have a single line record in a month if the ticket was either opened or closed (or both) in that month. Sorry if I'm being redundant... |
Posted By: hello
Date Posted: 03 Mar 2014 at 3:39am
| If your ticket numbers are issued in sequence, you could find out the first ticket number for each month and make 12 separate but similar reports...one for each month. |
Posted By: DBlank
Date Posted: 03 Mar 2014 at 4:00am
|
martin,
you may need to rethink how you want it counted as you will count a ticket twice if it runs over a month regardless of how you count it with an open or close in the same month.
If you pull in a unique idetifier you can always do a distinct count on that field.
|
Posted By: hello
Date Posted: 03 Mar 2014 at 6:19am
|
Originally posted by DBlank
martin, you may need to rethink how you want it counted as you will count a ticket twice if it runs over a month regardless of how you count it with an open or close in the same month.
If you pull in a unique idetifier you can always do a distinct count on that field. Agree. He must be referencing the open ticket when creating the closing ticket...somehow... |
Posted By: martinz
Date Posted: 03 Mar 2014 at 7:03am
|
Again thanks - I really appreciate the feedback! I need to show tickets opened and closed in each month. I have two other formula fields counting Opened and Closed tickets for a history line graph. So I think on the total counts I'm OK (if understand the way CR processes this). For example Total Closed running total field is based on Count of TicketNumber where ticket status is "C" / Reset Never. I think that is what you referenced doing above. That seems to map out correctly and produce a good line graph. The detail section I want to show the tickets opened and closed by month and be able to have those totals at the bottom of the month. I suppose I could still use a running total field and reset it by month - correct? That would just leave how to show the line items for that month, and while it would be nice to have a single line, using the duplicate record approach should handle that - correct? If all that is correct, I'm fuzzy on the last part - can you point to something to get me going in that route? Again thanks so much... |
Posted By: DBlank
Date Posted: 03 Mar 2014 at 7:18am
|
The running total (RT) wil not work if the fields span over the month. The reset breaks it from doing what you want. But if you don't reset it you won't have your monthly counts.
You likely can get creative and use 24 running totals with conditional suppression but I think you are just looking for something simpler.
I think you should really consider how you are building your data set to accomidate your output need.
Do you have rights to creating a view or stored procedure in your data source or are you limited to crystal only solution? Can you write a command?
|
Posted By: lockwelle
Date Posted: 03 Mar 2014 at 7:49am
| depending on how many months, you could use variables, and only increment the correct one...or an array...though I don't know much about graphs, since no one ever asks me for them, so my solutions may not work. |
Posted By: martinz
Date Posted: 03 Mar 2014 at 7:55am
|
DBlank - I'm not sure I understand RT not working. If I have 4 different RT's - 2 of which are tracking Open and Close by ticket number Count with reset set to "never" and 2 different tracking open and close set to ticket open/close date Count and "reset on the Group change" - will that not give me both monthly and full report counts? Those numbers are looking right in general. That is to say if I group on close only or open only - the monthly numbers look right as do the overall numbers. It is just that I can't do both. I do not have access to the data source views, just limited command build. I was looking at the following over the weekend based on your initial post: http://publib.boulder.ibm.com/infocenter/iadthelp/v6r0/index.jsp?topic=/com.businessobjects.integration.eclipse.doc.crdesigner/reportdesigner/crtskdefiningvirtualtablesbasedonacommandsqlquery.htm |
Posted By: DBlank
Date Posted: 03 Mar 2014 at 8:50am
|
The monthly RT's that span months are a problem because the one row will fall into only one month and therefore only b econuted in one RT.
I will guess you would be groupin on the open date which will mean the close of the ticket is in the wrong month never appears it the subsequent month. If you grouped on the close date then it would have missed the open record because it was not in teh prior months data.
As lockwelle suggests variables might work (they are about the same as the RT's here) but I doubt you would be able to graph them as you wanted.
|
Posted By: martinz
Date Posted: 03 Mar 2014 at 9:02am
|
I'm going off the assumption that at some point I will be able to list both opened and closed by month. So that is really the whole point of the original question. When I grouped on Open or Close - it was just to test the numbers, not how I want the final report to work. I was testing the logic - which did produce the proper monthly and overall total. But of course only looking at either Open or Close in a single run. So in essence two reports - one for Open and one for Close. |
Posted By: DBlank
Date Posted: 03 Mar 2014 at 9:08am
|
Yes if you are running two reports and only counting open in one and closed in the other your RT's reset on change of group (if set per respective month).
I thought you were still trying to get all of the numbers in this single report.
|
Posted By: martinz
Date Posted: 03 Mar 2014 at 9:52am
| I am, I think the thread split into different topics. So what should I be looking at to pull in these records as two different records (open and close). Or could I even connect to the DB with two different connections and link the ticket number? Which I think is where you were pointing me - to Command Connections at the same DB? Again thanks! |
Posted By: DBlank
Date Posted: 04 Mar 2014 at 4:08am
|
I again would just use a command with a union in it.
You can use paramteters in the command if you need to set the date ranges at run time.
This will basically just be two SQL select statments with a UNION between them. Make sure you set theorder the columns inteh same way in each statement part.
Something like
Select 'Open' as combinedType,opendate as CombinedDate, ticketnumber, ticketType, etc.
from TicketsTable
where opendate is between x and y
UNION
Select 'Closed' as combinedType,closedate as CombinedDate, ticketnumber, ticketType, etc.
from TicketsTable
where closedate is between x and y
|
Posted By: martinz
Date Posted: 04 Mar 2014 at 4:56am
I think I finally understand  Thanks, I'll post back just for the record... |
Posted By: martinz
Date Posted: 05 Mar 2014 at 2:58am
Well I thought I understood  but DBLANK worked it out for me! I'm posting the SQL command statement here in case it might help another down the road. Thank you to all for helping this newbie and to DBlank for taking time to look at my missed shot and correcting the aim!  SELECT 'Open' as CombinedType ,"Work_Orders"."Open Date & Time" as CombinedDate ,"Work_Orders"."Open Date & Time" , "Work_Orders"."Work Order #" , "Work_Orders"."Close Date & Time" , "Work_Orders"."Open Date" , "Work_Orders"."Close Date" , "Work_Orders"."Group Name" , "Work_Orders"."Work Order Type" , "Work_Orders"."State:" as combinedState , "Work_Orders"."Incident Description" FROM "MyDB"."MyOffice"."Work Orders" "Work_Orders" WHERE "Work_Orders"."Group Name"=N'MyOffice' UNION SELECT 'Closed' as CombinedType ,"Work_Orders"."Close Date & Time" as CombinedDate ,"Work_Orders"."Open Date & Time" , "Work_Orders"."Work Order #" , "Work_Orders"."Close Date & Time" , "Work_Orders"."Open Date" , "Work_Orders"."Close Date" , "Work_Orders"."Group Name" , "Work_Orders"."Work Order Type" , "Work_Orders"."State:" as combinedState , "Work_Orders"."Incident Description" FROM "MyDB"."MyOffice"."Work Orders" "Work_Orders" WHERE "Work_Orders"."Group Name"=N'MyOffice' AND "Work_Orders"."Close Date" IS NOT NULL |