How to display addition of Catalog item variables value in a report

[vivek72]

Hi,

I have a catalog item with 2 variables : dev effort and test effort.

I need to prepare a monthly report for this item which shows a total sum of dev effort and a total sum of test efforts from all RITMs raised in a particular month.

Please suggest a best way to achieve this requirement.

[KKM · Accepted solution]

Hi @vivek72,

To display the sum of Catalog Item variables' values in a ServiceNow report, follow these steps:

1. Identify the Catalog Item Variables

• Go to Maintain Items (sc_cat_item table).
• Open the required Catalog Item.
• Note the variable names (from the Variables tab).

2. Store Numeric Variables in a Reportable Table

ServiceNow stores variable values in the sc_item_option_mtom and sc_item_option tables, but these are not directly reportable.

• If your variables are numeric (integer, decimal, etc.), they are stored in the sc_item_option.value field.
• Join it with sc_req_item to fetch related records.

3. Create a Report Using Database Views (Recommended)

Since variables are stored separately from request items, you need a Database View to consolidate the data.

Steps to Create a Database View:

1. Navigate to: System Definition > Database Views.
2. Create a new view and name it (e.g., v_catalog_item_report).
3. Add Tables:
   • Primary Table: sc_req_item (Requested Item)
   • Join Table: sc_item_option
   • Join Condition: sc_item_option.request_item = sc_req_item.sys_id
4. Select Columns:
   • sc_req_item.number
   • sc_req_item.cat_item (Catalog Item Name)
   • sc_item_option.item_option_new (Variable)
   • sc_item_option.value (Variable Value)

4. Create a Report

1. Navigate to: Reports > Create New.
2. Select Data Source: Choose the Database View (v_catalog_item_report).
3. Choose Report Type: Use List or Sum Aggregation.
4. Add Conditions:
   • sc_req_item.cat_item = [Your Catalog Item Name]
   • sc_item_option.item_option_new = [Your Numeric Variable Name]
5. Use Sum Aggregation:
   • Group by sc_req_item.cat_item
   • Sum sc_item_option.value

5. Alternative: Scheduled Script to Store Aggregated Values

If reports are slow, use a scheduled script to copy summed values into a new field in sc_req_item for faster reporting.

6. Run & Validate the Report

• Save and run the report.
• Adjust formatting as needed.

Kindly mark it as correct/helpful, as it resolves your query. Please press like button for the resolution provided.

With Regards,
Krishna Kumar M - Talk with AIT3ch
LinkedIn: https://www.linkedin.com/in/mkrishnak4/
YouTube: https://www.youtube.com/@KrishAIT3CH
Topmate: https://topmate.io/mkrishnak4 [ Connect for 1-1 Session]

[vivek72]

Thanks @KKM !!

[Dr Atul G- LNG]

Hi @vivek72 

I don’t have the complete setup, but here are the high-level steps for you:

1. You can create a report on the sc_req_item table, set the type to Bar, and then at the bottom, instead of counting users, sum the values for Sum of Dev Efforts.

2. Next, create the same dataset using the same table and type, and now sum by Test Efforts and save it. In this case, you'll be able to see both efforts.

3. You can also add a condition, like RITM created in the last month, which is easily doable.

https://www.servicenow.com/docs/bundle/yokohama-now-intelligence/page/use/reporting/concept/c_Multip... 

[vivek72]

Hi @Dr Atul G- LNG ,

Thanks for replying.

However, report is not allowing me to select catalog item variables for aggregation field.

I set Aggregation to sum and then in next field I want to select catalog item variable but couldn't.

Group by field allows selection of items variables but I am not getting a way to show sum of catalog item variables from all RITMs