How do I get the display value of a catalog reference variable using a database view?

[patk]

I have a catalog item that contains different variable types, including multiple reference variables. I am trying to create a database view to use to create a report that lists the requested item, question and response that was entered in a request. The values for the reference variables are showing as a sys_id and from my on-line research, I had read that all that is needed is to do a left join on sc_cat_item. I have tried numerous ways, but have not been able to get the display value. Here are the tables and where clauses I have currently set in my database view:

table                                                           order                 prefix                             where clause                                                                                                                     left join

sc_item_option                             100                   opt                                                                                                                                                                                                     false

sc_item_option_mtom       200                   mtom                             mtom.sc_item_option = opt.sys_id                                                 false

sc_req_item                                       300                   ritm                                   mtom.request_item = ritm.sys_id                                                     false

sc_cat_item                                       400                   cat                                       mtom.sc_item_option = cat.sys_id                                                 true

The report I have below shows the reference as a sysid. My selected fields in my report, using the above view, are as follows:

Parent Item

Dependent Item.Question.Type

Dependent Item.Question

Dependent Item.Value

Dependent Item.Order

I would like to produce a report as follows:

parent Item                       type                                         question                                                                             value                                                                                                                         order

RITM0010001             select Box                       How many items are needed?         50                                                                                                                                 100

RITM0010001             Reference                       Manager?                                                                         (manager's actual name not reference#)         200

RITM0010001             Reference                       Group?                                                                                 (group's actual name not reference#)                   300

RITM0010001             Single Line Text     Comments?                                                                   These are the comments I entered.                         400

Any direction on this would be appreciated.

[Vladi1]

Hello,

Variable ownership did not work for us when we wanted to filter by assigned group. I combined this table with sc_task table and I have ability to filter by group or person. It is still in test phase, so I don't want to show database view yet,

Vladimir Markovic

[Vladi1]

After several attempts to create this report I followed steps provided in SNOW articles "Reporting on Service Catalog Variables" and "Database Views".

The only "issue" is that I cannot group by the value, but I was able to use sort condition A-Z for value and group them together by alphabetical order.

Reporting on Service Catalog Variables - ServiceNow Wiki

Database Views - ServiceNow Wiki