Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Open AND Closed status for Date Range Post Reply Post New Topic
Page  of 3 Next >>
Author Message
MBurnettt
Newbie
Newbie


Joined: 14 Aug 2009
Location: United States
Online Status: Offline
Posts: 14
Quote MBurnettt Replybullet Topic: Open AND Closed status for Date Range
    Posted: 14 Aug 2009 at 7:15am
I have an existing report that I'm running for Date Range 7/27-7/31 and the problem I run into is that if a ticket was opened AND closed during the same period, only the "Closed" status of that ticket shows in the report.  There is nothing in my Select Expert that filters only "Active" or "Inactive" tickets, so the results should be any value.
 
I thought maybe it was my Join in the Database Expert that links my two tables, but it's currently set to Left Outer Join, chg.status <-chgstat.code, so it should return all results.
 
I'm using a {?DateRange} Parameter field. I have a formula but it isn't returning accurate results:
 
if {chg.ACTIVE_FLAG} = 1
then "Open"
else "Closed"
 
This shows a ticket that was closed on 7/30 but it doesn't appear in the results as being opened on 7/28.
 
I tried the following formula, but it still doesn't pull Open AND Closed status on the same ticket, and returns a True/False statement:
 
if {?DateRange}=PDMTimeToDateTime ({chg.open_date})
then {chgstat.sym}="Open"
else if {?DateRange}=PDMTimeToDateTime ({chg.close_date})
then {chgstat.sym}="Closed"
 
I know I'm close to finding an answer but it just isn't coming to me.  I know this can probably be done using a UNION, but how does that translate to an if/then statement? 
 
I'm on a deadline and need to figure this out before Monday.  I haven't found anything helpful in the forums and could really use some expert advice.  Any assistance would be greatly appreciated.
 
Michael
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Aug 2009 at 7:27am
I assume your close_date field is NULL when a ticket is still open.
If you used the close date as any filtering at all you need to address this.
I do not understand what you are trying to pull so what is your select statement now?
IP IP Logged
MBurnettt
Newbie
Newbie


Joined: 14 Aug 2009
Location: United States
Online Status: Offline
Posts: 14
Quote MBurnettt Replybullet Posted: 14 Aug 2009 at 7:36am

I'm not filtering on closed date. 

I'm running a report for the Software Development Lifecycle, pulling all Dev, BA and QA categories within a certain Date Range:
 
{@OpenDate} = {?DateRange} and
{chgcat.sym} startswith ["BA", "Dev", "PMO", "QAA", "QAP.Other", "QAP.Performance Testing"]
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Aug 2009 at 8:37am

I am rereading this and need some clarity here.

Is the issue that the data is not coming into the report at all or you are having trouble grouping it properly? Meaning you are looking at a date and seeing what was opened and closed on that date?
Also be aware that just because you have a left outer join does not mean it is going to function that way. Your select statement can change it to an inner join.


Edited by DBlank - 14 Aug 2009 at 8:38am
IP IP Logged
MBurnettt
Newbie
Newbie


Joined: 14 Aug 2009
Location: United States
Online Status: Offline
Posts: 14
Quote MBurnettt Replybullet Posted: 14 Aug 2009 at 8:48am
I want all tickets to show in a particular date range regardless of status.  I have a Change Order that only shows in the report as "Closed", even though it was opened during that same time period.  The chart should reflect all Opened, Closed, and New tickets during a Date Range.  Since I don't have a select statement that filters on status, I'm not sure why the report isn't showing both the Open or "Active" AND Closed or "Inactive" status on the same ticket.  I hope that makes sense. 
 
I've even tried changing the link for the table to either Inner or Left Join, and receive the same results. 
 
Is Crystal preventing a ticket from appearing more than once if the status is different, as though it's treating them with an OR statement?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Aug 2009 at 9:03am

I am still unclear on your process here but your last statement has me worried and.

Are you grouping on a status based on a formula to determine if a field is Open or Closed?
If there is only one row for each instance it only appears once in the Group that it meets the first IF then condition for. It will not appear in both.
 
Is this what you are trying to do?
IP IP Logged
MBurnettt
Newbie
Newbie


Joined: 14 Aug 2009
Location: United States
Online Status: Offline
Posts: 14
Quote MBurnettt Replybullet Posted: 14 Aug 2009 at 9:15am
No, I'm not grouping based on status.  The only grouping I'm doing is number of Change Orders by department, regardless of status.  Status is set to any value.
 
My last statement which had you worried was just conjecture.  Something is preventing me from seeing the ticket in question listed twice in the report, once in Open status and when it was Closed.  Obviously, if the ticket is only showing once in a "Closed" status even though it was opened during the same date range, then my report is showing inaccurate results.
 
I see nothing wrong in my select statements or in the Database Expert.  I was hoping someone may have thought of something I haven't considered yet.
 
Let me know if you require further clarification.  I appreciate your help.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Aug 2009 at 9:37am

OK this one has me worried. Can you calrify this...

"My last statement which had you worried was just conjecture.  Something is preventing me from seeing the ticket in question listed twice in the report, once in Open status and when it was Closed.  Obviously, if the ticket is only showing once in a "Closed" status even though it was opened during the same date range, then my report is showing inaccurate results."
Most DB's are set up to continually update ONE row of data as it is updated from Creation to completion. There are often other tables that show the history and have multiple ros of data but the main table aways only has one row. Does your system create a new row in the table you are using everytime a record is changed? If so can you post some sample data of the data with all rows from creation to closure?


Edited by DBlank - 14 Aug 2009 at 9:39am
IP IP Logged
MBurnettt
Newbie
Newbie


Joined: 14 Aug 2009
Location: United States
Online Status: Offline
Posts: 14
Quote MBurnettt Replybullet Posted: 14 Aug 2009 at 10:03am
I'm not sure I understand what you're saying.  My data source, called "chg", has the tables "open_date" and close_date.  A new row is written to the open_date table anytime a ticket is created.  The same is true for the close_date table anytime a ticket status is changed to "Closed"  I am pointing to both of those tables in the data source, so I should get both opened and closed status tickets during a specific date range. 
 
Should I even be concerned with status, or should I be looking at the active flag?
 
active=1 which is open
inactive=0 which is closed
 
Either way, I'm not filtering on status or whether a ticket is active or inactive.
IP IP Logged
MBurnettt
Newbie
Newbie


Joined: 14 Aug 2009
Location: United States
Online Status: Offline
Posts: 14
Quote MBurnettt Replybullet Posted: 14 Aug 2009 at 10:07am
Here is the SQL Query of my report as it is so far:
 
 SELECT "chg"."chg_ref_num", "chg"."summary", "chg"."open_date", "ca_contact"."first_name", "ca_contact"."last_name", "ca_resource_department"."description", "ca_contact"."contact_uuid", "chgstat"."sym", "chgcat"."sym", "pri"."sym", "chg"."close_date", "chg"."active_flag"
 FROM   "mdb"."dbo"."ca_resource_department" "ca_resource_department" INNER JOIN (((("mdb"."dbo"."chg" "chg" INNER JOIN "mdb"."dbo"."chgcat" "chgcat" ON "chg"."category"="chgcat"."code") INNER JOIN "mdb"."dbo"."ca_contact" "ca_contact" ON "chg"."affected_contact"="ca_contact"."contact_uuid") INNER JOIN "mdb"."dbo"."pri" "pri" ON "chg"."priority"="pri"."enum") LEFT OUTER JOIN "mdb"."dbo"."chgstat" "chgstat" ON "chg"."status"="chgstat"."code") ON "ca_resource_department"."id"="ca_contact"."department"
 WHERE  ("chgcat"."sym" LIKE N'BA%' OR "chgcat"."sym" LIKE N'Dev%' OR "chgcat"."sym" LIKE N'PMO%' OR "chgcat"."sym" LIKE N'QAA%' OR "chgcat"."sym" LIKE N'QAP.Other%' OR "chgcat"."sym" LIKE N'QAP.Performance Testing%')
 ORDER BY "ca_resource_department"."description"
IP IP Logged
Page  of 3 Next >>
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.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.