Selection Criteria Needs Help- Please advise

[rico7937]

I have this selection criteria that I measure all tickets opened in the previous month and all tickets that are put in a servicerestored status for the previous month. Unfortunately I'm am not pulling tickets that were put in a servicerestored status for the previous month but may have been opened 2,3,4 months ago.

Ex. Ticket xxx Opened in Sept and put in servicerestored status in Dec. This ticket would not show up in my report since it was opened in Sept.

I would greatly appreciate any advice on what change I may need to do.
Here the selection criteria:


DTSToDateTime(tzcvtConvToLocalDatetimeByName(ToText(DateTime({PROBLEM.OPEN_DATE},{PROBLEM.OPEN_TIME}),"MM/dd/yyyy hh:mm tt"),"Eastern Standard Time"))
In Datetime (totext (Date(DateAdd ("m",-1,Cdate(year(CurrentDate),month(CurrentDate),1)))) & " 00:00:01") To Datetime (totext (Date(DateAdd ("d",-1,Cdate(year(CurrentDate),month(CurrentDate),1)))) & " 23:59:59")
and
{PROBLEM.PROBLEM_CODE} = "[SERVICERESTORED","CLOSED","TRANSFERRED"]
and
{PROBLEM.SEVERITY} = [1.00]
and
not
({PROBLEM.CAUSE_CODE} = ["Duplicate", "DUPLICATE"])
AND
not
({PROBLEM.PROBLEM_CODE} = ["Duplicate", "DUPLICATE"])
AND
(isnull({OUTAGE_DATA.ACTION_TYPE}) or

({OUTAGE_DATA.ACTION_TYPE} <> ["EXEMPT"]))

and
(isnull({PROBLEM.COMPONENT}) or

({PROBLEM.COMPONENT} <> ["NETWORK_HW","POWER"]))

and
({PROBLEM.GROUP_ID} LIKE ["IGS-VF*", "VFC-SD-LOCOU*","VFC-SD-NETW*","VFC-SD-P*","VFC-HLPDSK-EN*"])))

 

[rico7937]

Just to let you know that I am using CR V10 for this report.

 

[lbass]

Do you have a separate datetime field for statuses like service restored? I'm not sure how the later clauses relate to opened or restored status, but to get both those opened in the previous month and those with status changes in the previous month, you would need to set up the formula like this:

(
{table.opendatetime} in lastfullmonth or
(
{table.otherdatetime} in lastfullmonth and
{PROBLEM.PROBLEM_CODE} in ["SERVICERESTORED","CLOSED","TRANSFERRED"]
)
) and //etc.

-LB

 

[rico7937]

Yes we have seperate datetime fields.
We have:

1. {PROBLEM.OPEN_DATE}

2. {PROBLEM.SOLVED_DATE} (fro servicerestored tickets)

 

[lbass]

Well, plug in the correct dates into my suggestion and use that in the beginning of your original formula.

-LB