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

 

 

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?

 

mailto:%7b@OpenDate - {@OpenDate } = {?DateRange} and
{chgcat.sym} startswith ["BA", "Dev", "PMO", "QAA", "QAP.Other", "QAP.Performance Testing"]

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.

 

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?

 

Is this what you are trying to do?

 

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.

"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?

 

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.

 

 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"

Get rid of that to test and see if you get the missing records you were expecting.

 

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.

Am I missing somehting here?

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.

http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7351 - http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7351

 

Does this seem like the same probelm you have?

 

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?

 

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?

I did not see anything about grouping for your needs.

Why do you need it to show up twice?

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

Sum this formula field fo get a total # of opened in range

Close count formula

 

 

RTs will use a formual for Evaluate on as

Most likely your condition for Open is

did the Open_date fall in your paramter range (yes include, no exlcude) so  later this formual to make the fierlds be the real items from your report

 

 

I get a ")" is missing error.

{chg.open_date} in (2009,08,10) to (2009,08,14)
OR
{chg.close_date} in (2009,08,10) to (2009,08,14)

Not sure that you need the and {chgcat.sym} unless you need to filter by this for some reason.

 

The ) is missing.

 

I'm not sure what else it could be.  I'll play with it some more.

{chg.open_date} in Date(2009,08,10) to Date(2009,08,14)
OR
{chg.close_date} in Date(2009,08,10) to Date(2009,08,14)

{chgcat.sym} startswith ["BA", "Dev", "PMO", "QAA", "QAP.Other", "QAP.Performance Testing"]