Reporting on Service Catalog Variables

[arnoldzwane]

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        

[salu]

Hello All,

I have created the data base view with catalog task and sc_item_option_mtom.I need it as a report in the one TASK record I need the all the varables.

Please suggest on the same.

Thanks in Advance

[rahulpandey]

HI @saranya,

In past, I had same requirement and afaik there is no OOB way to get this done. However I end up creating custom report using UI page and new table.

[salu]

Hello Rahul,

While creating the new table how can fit in the requirement.can you please explain it?

[rahulpandey]

Well,

I had created a UI page for report condition, it my case it was company, start date etc. On submit of UI form.

New table contains columns like below(example)

Parent Item reference of requested item

Number : Parent of catalog task

Question : string

Answer : string

On submit of UI page I did glide query from "sc_item_option_mtom" (example) and inserted in new table using script include.

then created a report on new table.

this is the idea how I did.

Caution I took

1.Deleted previous record in new table created by logged in user.

2.Report table will show records created by logged in user only. I guess i did it by on query BR.

this is just a rough idea.

[Shahed Shah1 · Accepted solution]

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