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
<< Prev Page  of 3 Next >>
Author Message
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Aug 2009 at 10:11am
Interesting. this helps clarify and most likely your culprit is the select statment... the "{@OpenDate} = {?DateRange}" is likely changing your Outer join into an inner join.
Get rid of that to test and see if you get the missing records you were expecting.
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:21am
If I remove that how am I supposed to use the {?DateRange} parameter to search during a specific timeframe?
 
I tested it by removing the select statement and it pulled all change orders, but it is still only showing either opened or closed status tickets.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Aug 2009 at 10:34am
This was for testing only. Although I just read the SQL posted and see no mention of a Close_date table or an Open_Date table as you mentioned earlier. Onle the Chg Table with the an Open_Date and CLose_Date field.
Am I missing somehting here?
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 11:27am
No, you have it right, I just referred to them backwards earlier.  I point to both the open_date and close_date fields in the Chg table.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Aug 2009 at 11:45am
OK. We are back to the issue then that there is only one row of data. It just has a Close Date or NULL in that field.
Hence you will see it only once in your report. You can still do overall counts of Open or Closed on these but no grouping.
I am trying to help another person with a similar problem.
 
Does this seem like the same probelm you have?
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 11:56am
Which field are you reffering to?  The close_date field?  That's a number field, and when I browse the data it's pulling dates of tickets that fall within that DateRange.  I don't see Null.
 
I tried the formula you provided in the other post and it says it's missing a ")"
 
if isnull (Opened.week) then closed.week else opened.week
 
Should I do this where Opened.week = chg.open_date
and closed.week - chg.close_date?
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 12:03pm
These are the results of my report, exported into Excel:
 
Department
Department/Category		 Change # Status Opened Date Closed Date
Customer Benefits 949 Closed 7/29/2009  2:48:34 PM 7/30/2009  11:51:19 AM
Customer Benefits 933 Cancelled 7/27/2009  1:02:25 PM 7/27/2009  1:38:49 PM
Executive Office 954 Closed 7/30/2009  11:13:05 AM 7/30/2009  2:24:18 PM
Finance 944 RFC 7/29/2009  12:51:23 PM
Human Resources 960 Closed 7/30/2009  12:19:09 PM 7/30/2009  2:28:30 PM
Information Systems 968 Closed 7/31/2009  11:01:29 AM 8/10/2009  2:00:37 PM
Information Systems 935 Open 7/27/2009  1:22:46 PM
Information Systems 940 Released 7/29/2009  9:14:36 AM
 
As you can see, ticket 954 was closed on 7/30/2009  2:24:18 PM, but the opened date is 7/30/2009  11:13:05 AM.  What formula would I need to use to get that ticket to show up twice?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Aug 2009 at 12:08pm
You can't use a formula. You have to make the data get pulled in twice which is much trickier. read the entire post I gave you just a bit ago and it explains that more clearly.
I did not see anything about grouping for your needs.
Why do you need it to show up twice?
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 12:15pm
Because the intent of the report is to show all opened and closed tickets during a period of time.  We can't have one ticket that only shows as Closed when it was opened during that same date range, because it throws the numbers off.  There can't be a ticket that was closed without it being created.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Aug 2009 at 12:46pm

change your select statement to be an OR (this works only for the entire erport not if you are goinmg to group on some other field in the report and look for sub totals on that group)...

Your select statement will be something like ...
(chg.opendate in start date to end date
OR
chn.close date in start date to End Date)
and {chgcat.sym} conditions here
 
Then conditionally count your records with formulas or Running Totals (your choice)
 
Open count formula
if table.opendate in startdate to enddate then 1 else 0
Sum this formula field fo get a total # of opened in range
Close count formula
if table.closedate in startdate to enddate then 1 else 0
Sum this formula field fo get a total # of closed in range
 
 
RTs will use a formual for Evaluate on as
table.open_date in startdate to enddate
table.end_date in startdate to enddate
IP IP Logged
<< Prev 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.031 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.