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

Go to solution
vivek72
Tera Guru

‎03-13-2025 02:12 AM

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.

0 Helpfuls
  • 883 Views
1 ACCEPTED SOLUTION

Go to solution
KKM
Tera Guru

‎03-13-2025 02:19 AM

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]

 

View solution in original post

10 REPLIES 10

Go to solution
vivek72
Tera Guru
In response to Ankur Bawiskar

‎03-13-2025 05:00 AM

Unfortunately, configure function field also not allowing RITM variables to be selected for adding values.

0 Helpfuls