Extract all tickets

[gennaroalpha7]

Hello Everyone -

Can I please get some help from your expertise? Thanks in advance for your help.

I need to extract all tickets in a set period that have been assigned to the “BWHelpdesk” queue where multiple assignments to “BWHelpdesk” are ok but the results cannot include tickets that also have an assignment to another team (ie assignments to both “BWHelpdesk” and “ATG- Database”).

What would the code look like please.

Thank you.

G.

 

[Charliy]

Can you give us an idea of what the data looks like? Are the assignnments in the same record as the ticket? Are they in child records? Are they all in one field, or is there a record for each assignment?

 

[gennaroalpha7]

A ticket can have multiple assignments or just one assignment. We want to extract tickets, but only from one group/department. The tickets can have one or multiple assignments, but the assignments can only be to the BWHelpDesk group. We are having trouble isolating the tickets to only one group. Tickets are coming back, but tickets to assignments to other groups are also coming in too. So the data would look like this...

Open Date Last update Ticket # Ticket Status Keyword Group Name Call Type

2014-04-01 2014-04-04 1159026 Closed BWHelpDesk Transferring Data

2014-04-01 2014-04-04 1159032 Closed BWHelpDesk Boot

and so on....

thx.

G

 

[gennaroalpha7]

Hi, this is the code I have in the Group Selection, but it still bringing in tickets that have assignments to other groups too.


Distinctcount({Asgnmnt.Assignee},{CallLog.CallID}) = 1

and {Asgnmnt.GroupName} = "BWHelpdesk"

Thx.

G.

 

[pmax9999]

I assume this is the same issue as in thread767-1734723.

The code I suggested in that thread is not what you have quoted in this thread. Did you try what I suggested?

What happens if you use what I suggested previously as your Group election formula:

Distinctcount({Asgnmnt.GroupName},{CallLog.CallID}) = 1
and {Asgnmnt.GroupName} = "BWHelpdesk"

Pete

 

[gennaroalpha7]

Pete -

I tried that but tickets with assignments to other groups are also coming in.

I did notice that there was a link from the Calllog table to the Asgnmnt table that looked out of place. It was a link from (CallLog Table) DTLastMOd to (Asgnmnt table) DTLastMod. I removed that link and I am reviewing ticket by ticket to see if the correct tickets/data is coming in now. So now I only have 1 link between tables CallID to CallID.

Thx.

G.

 

[gennaroalpha7]

Hi,

I have a question? Please.

If the formula is used in the Group Selection...

Distinctcount({Asgnmnt.Assignee},{CallLog.CallID}) = 1

and {Asgnmnt.GroupName} = "BWHelpdesk"


Will it bring me back tickets with multiple or single assignments only within the BWHelpDesk? Or will it bring me back tickets with only 1 assignment in the BWHelpdesk group? It should bring back tickets with single or multiple assignment tickets only in the BWHelpDesk group. Going through the tickets/Data I think its only bringing back tickets with 1 assignment within the BWHelpDesk group - that's why I am asking.

Thanks for you valuable help.

G.

 

[pmax9999]

Firstly, I still think you are still using the wrong Group Selection formula. My approach should do what you want it to, but only if you followed the rest of the instructions in my post to the previous thread ie, remove the line from the Record Selection formula: {Asgnmnt.GroupName} = "BWHelpdesk"

If you have done exactly as instructed and you are not getting what you want, please share the report via Dropbox (or similar) and I will take a quick look to see if can identify any other problems.

Pete

 

[gennaroalpha7]

Right now its 3:45P my time. So I will add the report here in a few hours. Thanks Pete....


This is not in the record selection formula. Here's what in there now....

{CallLog.RecvdDate} >= totext(date
(
year(dateadd("q", -1, currentdate)),
if month(currentdate) in [1,2,3] then 10 else
if month(currentdate) in [4,5,6] then 01 else
if month(currentdate) in [7,8,9] then 04 else
if month(currentdate) in [10,11,12] then 07,
01
),'yyyy-MM-dd') and
{CallLog.RecvdDate} <= totext(date
(
year(dateadd("q", -1, currentdate)),
if month(currentdate) in [1,2,3] then 12 else
if month(currentdate) in [4,5,6] then 03 else
if month(currentdate) in [7,8,9] then 06 else
if month(currentdate) in [10,11,12] then 09
,

if month(currentdate) in [1,2,3] then day(dateadd("d", -1, date(year(currentdate), 01,01))) else
if month(currentdate) in [4,5,6] then day(dateadd("d", -1, date(year(currentdate), 04,01))) else
if month(currentdate) in [7,8,9] then day(dateadd("d", -1, date(year(currentdate), 07,01))) else
if month(currentdate) in [10,11,12] then day(dateadd("d", -1, date(year(currentdate), 10,01)))
),'yyyy-MM-dd')


thx.

G.

 

[pmax9999]

Just to be certain I tested my approach (albeit with simplified data) and it does work. It will return CallID records where the only GroupNames are "BWHelpdesk", regardless of the number of "BWHelpdesk" GroupNames.

Please ensure that the Group Selection formula has been changed to the Code I posted earlier.

If the report is still not working as expected please share the report file and also explain the relationship between {Asgnmnt.Assignee} and {Asgnmnt.GroupName}.

Cheers
Pete

 

[gennaroalpha7]

Hello -

Here's the report in drop box. Please review...

https://www.dropbox.com/s/91gvfi50jtpc7yw/Quarterly_BWHelpdesk_Tickets_Report_v0.2.rpt

Please let me know if this works out. The report is a CR11 report.

The relationship between these two {Asgnmnt.Assignee} and {Asgnmnt.GroupName} is as follows. The Asgnmnt.Assignee is the analyst that has the assignment. The Asgnmnt.Groupname is the group/department that the analyst is in. Each assignment has an assignee/analyst and they are in a group/department.

Thanks.

G

 

[pmax9999]

Please do the following:
[ol 1]
[li]Amend the Group Selection formula as advised previously (on 3 or 4 occasions);[/li]
[li]Remove the Conditional Suppression on the Details Section;[/li]
[/ol]

If this doesn't fix the problem, share the amended version and I will take one more look.

I have previously stopped assisting you because you do not follow the instructions, and again you are doing the same.

Like others on these forums who assist people like you, I do so in our own time and because I like to help. However, we can only assist if you provide all relevant information, answer all questions asked and follow the instructions given to the letter. It is probably not appropriate, and should not be necessary to get you to provide a copy of the report, however I will get you to do it this time in a final attempt to assist.

Pete

 

[gennaroalpha7]

I was given this SQL code by our Data Base team. Please have a quick look.

They want me to use this, but I would rather do this with Crystal Syntax.

Here's the SQL code....

SELECT "CallLog"."CallID", "CallLog"."RecvdDate", "CallLog"."CallStatus", "CallLog"."ModDate", "CallLog"."KeyWord", a."GroupName", "CallLog"."CallType"
FROM "heat"."Heatdb"."CallLog" "CallLog" INNER JOIN "heat"."Heatdb"."Asgnmnt" a ON ("CallLog"."CallID"=a."CallID") AND ("CallLog"."DTLastMod"=a."DTLastMod")
WHERE ("CallLog"."RecvdDate">='2014-04-01' AND "CallLog"."RecvdDate"<='2014-06-30') AND a."GroupName"='BWHelpdesk'
and not exists (
select * from heatdb.Asgnmnt
where callid = a.callid
and groupname <> 'BWHelpdesk'
)
ORDER BY "CallLog"."CallID" DESC

Thanks.

G.

 

[gennaroalpha7]

Hi -

Here's the updated report with the changes. Please review.

https://www.dropbox.com/s/91gvfi50jtpc7yw/Quarterly_BWHelpdesk_Tickets_Report_v0.2.rpt

I have changed the group Selection formula per your suggestion...to

Distinctcount({Asgnmnt.GroupName},{CallLog.CallID}) = 1
and {Asgnmnt.GroupName} = "BWHelpdesk"

and removed the Conditional Suppression on the Details Section; not the formula for duplicates though. I am checking it, so far so good. Thx.

Thx.

G.

 

[pmax9999]

Please advise if report is working once you have finished checking. If not, let me know and I will take a look at the report then.

Pete

 

[gennaroalpha7]

Hi Pete -

It appears it's working. I have checked quite a few tickets and so far so good. Thx.

There's two reports that are kind of a pain. There's this one that pulls tickets with single or multiple assignments in the BWHelpdesk group/department. Here's the Group Selection formula for this report...

Distinctcount({Asgnmnt.GroupName},{CallLog.CallID}) = 1
and {Asgnmnt.GroupName} = "BWHelpdesk"

And, there's the report that pulls only single assignment tickets in the BWHelpdesk group/department. Can you please take a look at this one too? Albeit, you probably have already. If so, please review again. Thx. Here's the Group Selection formula for this report...

Distinctcount({Asgnmnt.Assignee},{CallLog.CallID}) = 1

and {Asgnmnt.GroupName} = "BWHelpdesk"


Thx.

G.

 

[pmax9999]

So by saying it now seems to be working you accept that the code I provided in the previous post over a month ago was correct.

To get those tickets that only have one instance where {Asgnmnt.GroupName} = "BWHelpdesk", amend the Group Selection formula to the following:

Count({Asgnmnt.GroupName},{CallLog.CallID}) = 1
and {Asgnmnt.GroupName} = "BWHelpdesk"

Pete

 

[gennaroalpha7]

Yes, it was correct. Thx. I copied the wrong formula to the Group Selection.

Is the code provided above for the second report - pulling single assignment tickets from the BWHelpdesk group/department?

Thx.

G.

 

[gennaroalpha7]

On the second, single assignment, report the group selection formula seems to be working...

just asking - what's the difference from...

Distinctcount({Asgnmnt.Assignee},{CallLog.CallID}) = 1
and {Asgnmnt.GroupName} = "BWHelpdesk"


To this...

Count({Asgnmnt.GroupName},{CallLog.CallID}) = 1
and {Asgnmnt.GroupName} = "BWHelpdesk"

Thx.

G.

 

[pmax9999]

The difference is Distinct.

When using DistinctCount It doesn't matter how many times the GroupName of "BWHelpdesk" appears, it is only counted once. In other words, it doesn't matter how many instances of the GroupName "BWHelpdesk" there are, as long as it is at least once. When using Count, there can only be one instance where the GroupName is "BWHelpdesk".

Pete