Field Limting

[gennaroalpha7]

Hi -

I have a input box called 'Asgnmnt.Assignee'. This is where technicians are assigned tickets - each ticket can have multiple technicians/assignments assigned.

I would like to create a report that only list tickets that have only one technician/assignment. For example, I was thinking of creating a formmula like this, and inserting that formula into the report.

{Asgnmnt.Assignee} <= [highlight #FCE94F]1[/highlight]

My question: Is this the correct method of going about this? When I check this it highlights the number 1 and says 'A String is Required Here'

The other method I tried is to list the technicians and pull the report based on these technicians, but doing it this way requires maintenance.


Thanks for your help.

Alpha7

 

[pmax9999]

You would need to group the report by {Table.TicketNo}. Then, add the following Group Selection formula:

DistinctCount({Asgnmnt.Assignee},{Table.TicketNo}) = 1

Hope this helps

Cheers
Pete

 

[gennaroalpha7]

Hello -

What is the definition of {Table.TicketNo}? Would this be the grouping of the ticket numbers, which are in the column 'CallLog.CallID'

Why do you need to create this group?

Thanks for your help.

Alpha7

 

[gennaroalpha7]

Hi -

I would like to rephrase my requirement:

I would like to create a report that lists the first assignee/technician of each ticket. Some tickets have one or multiple assignees. Will the formula given above produce that result?

and

What is the definition of {Table.TicketNo}? Would this be the grouping of the ticket numbers, which are in the column 'CallLog.CallID' Why do you need to create this group?

I have created this group and inserted the formula through the Section Expert in the detail area of the report.

Thanks for your help.

Alpha7

 

[fisheromacse]

you will want to insert a group on the ticket number (the '{Table.TicketNo}' in Pete's reply is a placeholder which you will replace with the actual table/field names)

Place your detail info in the group header and suppress the details section.

It should show the first 'set' of info in the header. if it not the item you want, try reversing the sort order.

 

[gennaroalpha7]

Hi -

I followed Pete's instructions and grouped my field 'CallLog.CallID', which lists out ticket numbers, and on the left side grouped header margin under Section Expert I entered the formula

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

and I suppressed the details section.

It looks as though it produced correct results. Please provide me some feedback...

Thanks.

Alpha7

 

[gennaroalpha7]

Another method I tried - is to create a formula 'DistinctCount({Asgnmnt.Assignee},{CallLog.CallID}) = 1' under formula fields called 'Assign_Assignee' and placed it in the grouped header section over my 'Asgnmnt.Assignee' field and suppreseed that section.

Please provide feedback as to which is the correct method, placing the formula into the left margin - grouped header or creating a formula and placing it into the grouped header section over my 'Asgnmnt.Assignee' field.

Thanks for you feedback and assistance.

Alpha7

 

[pmax9999]

Firstly, what I suggested was in response to the way you initially described what you are trying to achieve. Your re-phrased requirement is very different, so my solution will not give you what you now say you want.

Secondly, my solution was to create the group and then apply the code I provided as a Group Selection formula, not as an ordinary formula, and not for conditional suppression.

And thirdly, the {Table.TicketNo} field I referred to was a generic reference to indicate a field that uniquely identifies each distinct call/ticket/incident, so yes the {CallLog.CallID} is almost certainly that field (but obviously without being familiar with the dataset you work with I can only advise in broad/general terms). The reason it needs to be grouped is because you said you want to identify only those call/ticket/incidents that had only one technician assigned. There are actually other ways to deal with it, but they are much more difficult t deal with in a forum environment.

If you still require assistance, please advise exactly what you now want to achieve. If there is some reason why you believe it is not possible to group the report as suggested, please explain that also.

Cheers
Pete

 

[gennaroalpha7]

Hello -

Sorry for the confusion. I would like to create a report that lists the first assignee/technician of each ticket. Some tickets have one or multiple assignees.

Here are my fields...

1. '{Asgnmnt.Assignee}' this is the name of the assignee
2. '{CallLog.CallID}' this the number of the ticket e.g. 1092528


Thanks for the feedback and guidance.

Alpha7

 

[pmax9999]

In that case, fisheromacse's suggestion is the way to do it. Group on {CallLog.CallID}, sort by the date field that indicates when it was assigned to the {Asgnmnt.Assignee}, move the contents of the Details section to the Group Header, and suppress the Group Footer and Details sections.

Cheers
Pete

 

[gennaroalpha7]

Hi -

Just one more question please. I am a bit confused as to where to add and put the (above) Group Selection Formula...

Thanks.

Alpha7

 

[pmax9999]

The Group Selection formula was proposed as part of the solution to your original question. It is NOT required as part of the solution to the "rephrased" requirement.

However, if you ever need to use the Group Selection formula, it can be found under Report => Selection Formulas => Group.

Pete

 

[gennaroalpha7]

Hi -

I just have one more question for this report, please. The requirement was the first one - I screwed up. If the requirement was to report tickets with only one assignee to the ticket - could the Group Selection Formula (above) be used with this slight modification?

DistinctCount({Asgnmnt.Assignee},{Table.TicketNo}) <= 1

I added the less than symbol.

Thanks for your guidance and patience.

Alpha7

 

[pmax9999]

That depends on your data. Can there be a circumstance where there is less 0 Assignees? I'd suggest it is more likely that there is NULL Assignees, in which case the Group Selection code should be:

DistinctCount({Asgnmnt.Assignee},{Table.TicketNo}) = 1 or
Isnull(DistinctCount({Asgnmnt.Assignee},{Table.TicketNo}))

In any event, adding the "<" won't hurt.


Cheers
Pete

 

[gennaroalpha7]

Hi Pete -

The report is still listing some 2nd level assignees, just to do a quick check. But this is probabley due to the fact that 1st, 2nd, and probably 3rd levels are bucketed into one bucket instead of multiple buckets e.g. 1st, 2nd, and 3rd level support clearly separated in the Database - but they're not.

But, looking more closely at other choices that distingushes the assignees is the date and time that assignees are assigned to the tickets. Those two fields are 'Asgnmnt.DateAssign' and 'Asgnmnt.TimeAssign'

It's beyond my knowledge so any insight/help would be appreciated. I am including a link to the file for reference.

https://www.dropbox.com/s/8v04n18glrd47c

Thank you.

Alpha7

 

[pmax9999]

Your link doesn't work, but firstly, make sure the sort order is set to sort first on {Asgnmnt.DateAssign} (ascending order) and then {Asgnmnt.TimeAssign} (ascending order).

Also, did you move the details section components to the GH ({Asgnmnt.Assignee}) section and suppress the Details section?

Cheers
Pete

 

[pmax9999]

Actually, ignore my last post - it was for the "revised" objective, not your original post.

 

[gennaroalpha7]

Hi -

This link should work.

Link

Thanks.

Alpha7

 

[pmax9999]

OK, I have looked at the report.

Firstly, untick the "Supppress" formatting on the Detail section and get rid of the conditional suppression formula. At this point, the report is doing what I understood you want it to.

To verify this, I removed the Group Selection formula, which identified that there are numerous CallIDs with multiple Assignees (01068838 and 01110399 are just 2). When I reinstated the Group Selection formula, those two CallIDs can be seen in the Group Tree (down the left hand side) but do not appear on the report itelf - ie, they have been excluded.

Is this not what you are trying to achieve?

If there are examples of CallIDs on the report that you believe should not be there, please identify them and explain why you believe they should not be there.


Cheers
Pete

 

[gennaroalpha7]

Hi -

I have to leave now. But later this evening I will upload the latest report. Please take a look.

These two ticket numbers are showing on my version, but according to you they should not.

What I am trying to achieve is to report on tickets that only have one assignee, and not multiple assignments.

Thanks and have a good one.

Alpha7