Report on Item Variables in the Service Catalog with a Database View

[Yvonntje]

Your company might have a need to report on Service Catalog Variables, one example for a use case might be to predict future requests to stock up on the right product.

Historically, it has been tricky to generate these kinds of reports in ServiceNow and we improved our variable reporting in newer releases. I will show you not only where the various variables are stored in relation to the Requested Item tables, but also how to build a database view that will link those tables and will work release independent.

A good way to report on Service Catalog Request Item Variables is using a Database View.   I will give you show you how to create a report on item variables using a database view.

In order to successfully create a database view to report on item variables in the service catalog, there are a few things you will need to know. First, the Requests are stored in the sc_request table. Second, the Catalog Items are stored in sc_req_item. Third, the variables you will find on the sc_item_option table. But, keep in mind that, the actual value for the variables in relation to the request is stored in sc_item_option_mtom.

Those of you familiar with Database views will know that sys_id is your best friend when linking tables, the same applies in this case.

sc_req_item links to sc_item_option_mtom by the sc_req_item sys_id

sc_item_option_mtom links to sc_item_option by the sc_item_option.sys_id

Confused? Let's see how to set this up, step-by-step:

Step 1: Create a Database View

To get started, you will need to create a database view. Start by navigating to System Definition > Database Views > New.

Give your Database View a meaningful name, in my example "screq_item_var_report." My label name is a bit long, "Request Item Variable Report," I thought for this example a descriptive name might be a good idea.

Step 2: Create Tables for the database view

After submitting, a "View Tables" list will appear. Here, the tables you want to join will be added. Click "New" and enter the following:

1. Table: Options (sc_item_option)
2. Variable prefix: opts
3. Order: 100
4. Where clause: leave blank      
5. Submit

Add the second table, again Click "New"

1. Table: Variable Ownership (sc_item_option_mtom)
2. Variable prefix: varown
3. Where clause: varown.sc_item_option = opts.sys_id
4. Order: 200
5. Submit

                                                                                                                                 

You will notice that the View Tables List on the Database View is getting fuller, now the last one to be added.

1. Click the new button.
2. Table: Requested Item (sc_req_item)
3. Variable prefix: reqitem
4. Order: 300
5. Where clause: varown.request_item=reqitem.sys_id
6. Submit

Once the tables have been added to the Database view and all of the catalog request item options have been named, you will have completed your database view that allows you to report on Service Catalog items.

You can now create   a report based on "screq_item_var_report" and add fields like item, number, question, value etc. to will show the variables and their respective requested items.