NOTE: MY POSTINGS REFLECT MY OWN VIEWS AND DO NOT NECESSARILY REPRESENT THE VIEWS OF MY EMPLOYER, ACCENTURE.
DIFFICULTY LEVEL: ADVANCED
Assumes good intermediate to advanced level knowledge and/or familiarity of Scripting in ServiceNow.
At least once a week I am asked: Is there a way to create a single combined report of my Service Catalog Requested Item variables? And, for that matter, where does this information reside inside of ServiceNow?
Well the answer to the first question is yes, and the trick to how to do it is in the answer to the second question!
A lot of people want to see their Requested Item variables, including which variables are associated with which item by requestor, a list of questions associated with a particular item or… well you get the idea!
It took a little bit to track down the solution when I first dug into it, but it really isn't anything bad. The best approach is a Database View (/tag/database-views/) that ties all of the necessary tables together to form the report.
So, where does everything live? If you dig down a bit, you'll find that:
1) Service Catalog Request Items (RITM) are stored in the sc_req_item table
2) Variables are stored in the sc_item_option table
However, there does not appear to be any sort of direct relationship between the two.
In comes the trick:
3) RITM to sc_item_option associations are actually kept in a third table: sc_item_option_mtom
But how is this done? I nod sagely, and I say: "sys_id, my friend!"
The mtom table (by the way, does anyone have any idea what "mtom" stands for? Item—to-item maybe?) allows several Service Catalog Requested Items to be associated to the same variables. Cool, huh?!
When I figured this out, I smacked myself in the forehead for not getting it the first time. After that, the rest was simple.
The relationships:
sc_req_item relates to sc_item_option_mtom (via sc_req_item.sys_id)
sc_item_option_mtom (via sc_item_option.sys_id) relates to sc_item_option
For the confused, here is a diagram:
The next step is to weave these together in a Database View, which can then be accessed via a report.
1. Create a Database View:
a. From the ServiceNow navigation search type: database
b. Click on System Definition / Database Views
c. From the Database Views list view, click on the new button to create a new Database View. Name it something meaningful so that you don't have to search far when pulling in the Database View for your report.
i. Name: SC RITM Variables (u_sc_ritm_variables)
ii. Label: SC RITM Variables
iii. Plural: SC RITM Variables
iv. Description: Example of a Service Catalog Ownership Database View
v. Click on the Submit button
vi. At the bottom of the form you will see the View Tables list view. Click the new button.
1. Table: Variable Ownership (sc_item_option_mtom)
2. Variable prefix: mtom
3, Order: 100
4. Click on the Submit button
vii. Add another table. Click the new button again.
1. Table: Options (sc_item_option)
2. Variable prefix: options
3. Order: 200
4. Where clause: mtom.sc_item_option = options.sys_id
5. Click on the Submit button
viii. Add yet another table (this is the final one, I promise). Click the new button again.
1. Table: Requested Item (sc_req_item)
2. Variable prefix: ritm
3. Order: 300
4. Where clause: mtom.request_item = ritm.sys_id
5. Click on the Submit button
d. Your Database View should now look like this:
e. You are done with the Database View! To test it out click the "Try It" link under the Related Links section of the Database View form.
2. Now create a new report and use the "SC RITM Variables" (u_sc_ritm_variables) table.
Create a new report and use a Source Type of: Table, and the Table: SC RITM Variables.
You will notice that the default fields of the report match your choices for the Database View List View.
So, what exactly are we looking at with all this data we are getting from the view? This would be a combination of all the Requested Items and their associated variables. Some suggested variables for the report include:
Number (RITM)
Item
Question
Value
Quantity
Approval
Due Date
Priority
Impact
Urgency
Opened
Opened By
Price
Request
Requester
Short Description
State
That's all it takes, you're done! You should now have your Database View and a list of the best variables for the view.
Enjoy!
Steven Bell.
If you find this article helps you, don't forget to log in and mark it as "Helpful"!
NOTE: ON APRIL 1, 2016 ACCENTURE COMPLETED THE ACQUISITION PROCESS OF CLOUDSHERPAS. AT THAT TIME THE CLOUDSHERPAS BLOG SITES WERE DOWNED FOREVER.
THIS IS THE RE-PUBLICATION OF MY ARTICLE FROM September 23, 2014 ON THE CLOUDSHERPAS SERVICENOW ADMIN 101 BLOG.
Originally published on: 05-13-2016 11:37 AM
I updated the code and brought the article into alignment with my new formatting standard.
8 Comments
