Service catalog item request frequency

[Tim Grindlay]

Hi,

I have a fairly straight forward request to produce a report of all service catalog items and how many times each of those catalog items has been requested. I was expecting to find something OOTB but it doesn't appear to exist, there are very similar reports like top n requested items but not exactly what I'm after. In excel, SQL, crystal reports or even MS access this is a fairly trivial task, but I can't seem to replicate that in ServiceNow. Possibly there is some way with performance analytics, but I don't know enough about pa yet to know how to accomplish this.

The solution I have come up with is creating a left join database view with sc_cat_item and sc_req_item. Now this gives me the record set I require, all catalog items and the requested items related to them, even if there are none. I can group total the list on the item field, which gives me the total I want, except for those items that have no related requested items. The total for these rows will show as 1 instead of 0, because the total is counting the number of rows. I want the total to count the number of requested items.

I want to avoid using scripting and dynamic content where possible so that users can still adjust the filters, or drill down and export as necessary.

Regards,

Tim

[Tim Grindlay]

Thanks for the comment. It's been a while since I looked at this, but I think the issue was the 'count the occurrence' part. In an SQL aggregate query you'd just distinct count the RITM number and group by the item, but the closest I can get is the pivot table as shown above. There is an option now to distinct count, but this appears to only work on certain field types, and you have to have a column field, you can't just have a total, and you also can't sort on totals.

[graycarper · Accepted solution]

This question was asked long ago, but since the solution only covers how to measure Catalog Items that produce Requested Items (as opposed to other Task types), I thought I'd drop a link to an article on the subject I just wrote: Measuring Catalog Item Usage Comprehensively Across All Produced Tasks in Performance Analytics. Hopefully someone else who sees this thread will find it useful.

-Gray

[Tim Grindlay]

This is amazing, thank you for your post Gray! Answers my question plus a lot more.   

[graycarper]

You're most welcome! I'm just sorry it's three years late!