How to display a variable set variable in a list, for reporting?

[tombialkowski]

We have a variable called 'start_date', contained within a variable set for new hires in our service catalog. I would like to create a list of all catalog items requested, and include the start_date as a field in the list for reporting purposes. Can anyone suggest how might I go about this?

[tombialkowski · Accepted solution]

Figured it out.

Create a database view between sc_task and sc_item_option_mtom to join the two tables.

Create a report using this view.

Reporting on Service Catalog Variables - ServiceNow Wiki

[wiltonr]

Would you have an example of how you did this?   I am not very well-versed with database views.

[tombialkowski]

Hi Rhonda,

Creating a database view essentially joins 2 tables together so that you can query the single view, instead of having to query both tables separately and then glue the data together. The following link should get you started: Database Views - ServiceNow Wiki

As for an example - Here is the view I setup to join the Request Items to the variables:

This allows me to create a new ServiceNow report that uses the joined database view so that I can report on the items and their variables as though they both existed in the same table.

[wiltonr]

Thanks Tom,

I was just now able to get back to this.   This is getting me closer but not quite where I need to be.   It probably needs additional Where clauses.

This is an example of what I'm after:

I have a particular catalog item where there are two variable sets.   One of the variable sets contains a variable where Question is Requestor (reference to sys_user table) and another variable set contains a variable where Question is Name (reference to sys_user table).   I need to be able to create a report where the Catalog Item is <my catalog item> AND Requestor = <specific user> AND Name = <one or more specific users>

So basically i want it to return one row per Requested Item where Jane Doe is the Requestor and (Tom Smith or Jimmy John or Linda Allen) are the values of the Name variable, returning something like this (and ultimately where these Values are sys_user sys_id's, I'd really prefer to have the sys_user DisplayValue).

Does this make sense?

Thanks,

Rhonda

[tombialkowski]

It makes sense, but unfortunately I don't know enough to get to that point either. With the method I'm using you end up with something like the following:

RITM0000001         Question1         Answer1

RITM0000001         Question2         Answer2

RITM0000001         Question3         Answer3

RITM0000002         Question1         Answer1

...

I then add a condition to filter on the question I'm interested in, meaning I can only pull one of the question/answer pairs at a time. Either that or I pull multiple and end up with duplicate RITM#'s. My end goal is the same as yours - to be able to output 1 row per item and have multiple columns containing the question/item pairs.

Random thought - One method, or work-around, might be to add matching 'Value' columns to your request item table and have the workflow copy the supplied values from your variable sets into your new columns when the workflow runs.

Example

RITM #                               Question1Answer         Question2Answer

RITM0000001                 Answer1                                                   Answer2