Report unique requested items by assignment group type

kirkr · ‎09-12-2017

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

kirkr · ‎09-13-2017

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.

View solution in original post

Jaspal Singh · ‎09-12-2017

Hi,

Since you say you may have 3 catalog tasks you can try & group them by Request Item & stack them by Assignment Group.

kirkr · ‎09-13-2017

Unfortunately you can't stack in a Trend report. Doing a Bar chart takes forever on anything larger than a few days of requested items causes the report to take WAY to long, usually timing out.

Even then the report isn't terribly pretty and can't group on date ranges (month, week, etc) like a Trend does without a custom field.

Jace Benson · ‎09-12-2017

Sounds like your reporting on sc_task. If you could report on sc_req_item you wouldn't have the specific issue you're stating.

kirkr · ‎09-12-2017

How would I pull the Catalog Tasks from a report on the Requested Item table?