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
  • 879 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
Dr Atul G- LNG
Tera Patron
Tera Patron
In response to vivek72

‎03-13-2025 03:58 AM

Hi @vivek72 

Sorry, it's not possible. I misunderstood. I thought these were two separate fields, but they are variables, and we cant apply the sum function to the variables. Alternatively, you can create two fields, copy the data into them, and then create a report.

*************************************************************************************************************
If my response proves useful, please indicate its helpfulness by selecting " Accept as Solution" and " Helpful." This action benefits both the community and me.

Regards
Dr. Atul G. - Learn N Grow Together
ServiceNow Techno - Functional Trainer
LinkedIn: https://www.linkedin.com/in/dratulgrover
YouTube: https://www.youtube.com/@LearnNGrowTogetherwithAtulG
Topmate: https://topmate.io/atul_grover_lng [ Connect for 1-1 Session]

****************************************************************************************************************
0 Helpfuls

Go to solution
vivek72
Tera Guru
In response to Dr Atul G- LNG

‎03-13-2025 04:21 AM

hmm ok, thanks!

I thought may be with newer versions SN has introduced a way to do this 😞 

0 Helpfuls

Go to solution
Ankur Bawiskar
Tera Patron
Tera Patron

‎03-13-2025 04:02 AM

@vivek72 

try this

  1. Creating New Report
  • Navigate to Reports > Create New.
  • Define the report name and select the table as Requested Item [sc_req_item].
  • Add conditions to filter RITMs for the specific catalog item and the desired month. For example:
    • Catalog Item is your_catalog_item_name.
    • Opened is on or after start_of_month.
    • Opened is on or before end_of_month.
  1. Adding Variables to Report
  • In the report builder, add the dev effort and test effort variables as columns.
  • Use the Sum aggregation for both columns to get the total values.

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader
0 Helpfuls

Go to solution
vivek72
Tera Guru
In response to Ankur Bawiskar

‎03-13-2025 04:39 AM

Hi @Ankur Bawiskar ,

 

Thanks for replying.

I did everything but still stuck on same below point, do you have a screen shot on how to do this  :

  • "Use the Sum aggregation for both columns to get the total values."

I am unable to find a way to use sum for catalog item variables or report columns.

I was able to add required variables to report but unable to show sum for both variables or even for 1 column/variable.

0 Helpfuls

Go to solution
Ankur Bawiskar
Tera Patron
Tera Patron
In response to vivek72

‎03-13-2025 04:43 AM

@vivek72 

try to use calculated field

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader
0 Helpfuls