Reporting on Service Catalog Variables

arnoldzwane
Kilo Contributor

Hi Guys,

We have a situation where our Service Catalog is not using Requested Items; instead everything is handled within a Request; i.e. Workflow is based on Request table and Catalog Tasks are directly linked to Request. To show Variables on the Request form, we created a custom "Request Variable Editor" in the UI Formatter.              

Our challenge now is that we want to report on the variable information that is captured on the Service Catalog and we don't know in which table is this information stored. We checked the sc_item_option_mtom table where this information would normally be stored, but couldn't find it there; Only the RITM variable information is there. Could someone please advise where else can we look for this information.

Kind Regards

Arnold        

1 ACCEPTED SOLUTION

Shahed Shah1
Tera Guru

Hi Arnold



I'm not sure if you have got the answer you are looking for, so I'd let to take a step back and clarify a couple of things then reattempt the answers as provided by our community friends. As you are aware, there are two different "items" in the Service Catalog: Catalog Items and Record Producers.



When a user requests the Catalog Items, the target request (and requested items) records are created and the associated values for the variables are stored in the   sc_item_option_mtom table. Reporting on this is well covered in the Wiki: Reporting on Service Catalog Variables - ServiceNow Wiki



When a user submits a Record Producer, the target record   (e.g. Incident) is created where the variables are mapped to the respective fields (depending on the Record Producer's configuration). These variables are stored in the question_answer table. In this table you have the table_name and table_sys_id fields of the target record (e.g. table_name=incident) in addition to the question reference field that points to the variable. To report on this you can create a Database View for each table (e.g. a database view called Incident Variables) where you then join the respective tables (e.g. incident, question_answer, question, etc) using those fields. Following this you can then create a report on this Database View.



I hope that this is of use


View solution in original post

10 REPLIES 10

Pradeep Sharma
ServiceNow Employee
ServiceNow Employee

Hi Arnoid,



Can you check the entries are store on question_answer table.


Please let me know the outcome.


Thanks Pradeep, the information I 'm looking for is in this table. The only field I haven't figured out yet is for the Request reference number; and will check to see if I can filter using the Variable Set because I only need data from one variable set.



Tha nk you very much for a pointer; I will let you know if I manage to get this right.


Harish Murikina
Tera Guru

Hi Arnold,



            Select "sc_item_option_mtom " table you can see "Dependent item" option , show the related records and select dependent item.Question and Dependent item.value, you will get the report with catalog item question and value.



Below you can see catalog item questions and values in report.



report on catalog item vars.PNG


Regards,


Harish Murikinati.              


Hi Harish,



The sc_item_option_mtom table would have been a perfect solution, but this is only keeping variables linked to Requested Items.



Thanks