How to report on service catalog categories

[Bruno Fernande1]

How can I report on service catalog categories? For example, how many Hardware requests were raised last month? I cannot find any way to tie a REQ or RITM to the service catalog category it belongs to...

Do I need to create a database view for that?

[Chuck Tomasi]

Hi Bruno,

You might be able to do it by dot-walking the request item through the catalog item field to the category on that catalog item record.

requested item --> catalog item --> category

Reference:

Dot-Walking - Servicenow Wiki

[Uncle Rob · Accepted solution]

Ok, so REQ is basically a write off.   Barely useful for anything reporting wise, and you're correct that you can't link that to the Catalog Category.   REQ can be parent to multiple RITMs, and technically those RITMS can all exist in separate categories.

RITM (sc_req_item) is related to Catalog Item via the "item" (cat_item) column.   As Chuck states, you can dot walk up from there to the Catalog Category.

The only other thing you'll want to be aware of is that Catalog Items *can* inhabit multiple Categories.   In that case, I don't know what you'd do to measure the consumption.   This is why I wish SN would put more R&D into its existing #tagging functionality so we can just sum the presence of tags, or render tagged items in a configurable tag cloud.

[Bruno Fernande1]

Thanks!

[andrew_och]

sc_req_item has a one to one relationship to sc_cat_item so dot walking is fine to that point. However sc_cat_item to sc_category is a many to many relationship via table: sc_cat_item_category which means you cannot dot walk to it. The sc_cat_item.category is a legacy field which when populated or changed has business rules that should populate the many to many table, so while you can dot walk to that particular field, you are not necessarily getting all the data especially if you are building reports.

Scripts - Background

// Demo data Apple iPhone 5

var iphone5_sys_id = "0d08837237153000158bbfc8bcbe5d02";

var catItem = new sn_sc.CatItem(iphone5_sys_id);

gs.log(catItem.getCategories());