The Now Platform® Washington DC release is live. Watch now!
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.
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:
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.
After submitting, a "View Tables" list will appear. Here, the tables you want to join will be added. Click "New" and enter the following:
Add the second table, again Click "New"
You will notice that the View Tables List on the Database View is getting fuller, now the last one to be added.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.