Report unique requested items by assignment group type

[kirkr]

I'm trying to create a report Trend report by Opened per Month that allows us to see a roll-up of all unique Requested Items that a Group type has been assigned a Catalog Task over a date range.

Within our groups we have a couple of extra Group types we use for various reporting and filtering

• Application
• Infrastructure

The trouble I'm running in to is that one Requested Item may have 3 Catalog Tasks assigned to an Infrastructure group, but we only want to count the Requested Item once.

Is there a method to do this with a report or by a Database View? I'm stumped

[Jace Benson]

My misunderstanding.   If the groups are on the sc_task, your only options are probably;

Database View *but this will have issues too*

Scripted

If you do a database view, your base table would sc_req_item and then you'd add sc_task_1 where request is sc_req_item.sys_id, you'd have to do teh same for each task, now your report would have to check sc_task_1.group.type = type1 || sc_task_2.type = type1 || sc_task_3.group.type = type1

Because you want to only count the Item once but count the groups each time they have a task, you are going to have this issue.   You cannot have both in a manageable way.

Performance Analtyics might give you a way, but I'm not sure how you'd do that since it runs collections on a frequency, you can probably have it run a script to group them by group type but then you still run the risk of have multiple per RITM as any team could be assigned a task that isn't part of the other tasks types.

[kirkr · Accepted solution]

Thank you for following up, I ended up needing to create a read-only field on the Requested Item table that stays updated with the group types of Assignment groups.

This ended up working for reporting purposes.

Your view idea would work fine if we always had a maximum number of Catalog Tasks. Unfortunately that is variable and could be 1, could be 8. Thank you for the idea though.

[SanjivMeher]

But if a Requested Item has 3 tasks, each tasks could be assigned to different group. So that report wont give you the right report. You will need all the task or if you are assiging a primary group to the Requested item, you may create a report on Requested item table itself.

[kirkr]

We aren't assigning a primary group on the Requested Item table.