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.

[SME]

Are you sure you want to do this. Check my response in this article if you can achieve it from there :-

Customized Export to XML or Excel

[SME]

Also, have you tried reporting from OOB table Variable Ownership...I think you will get the exact result you want. Check the screen shot below :-

[patk]

I did try this and for most variables it is giving me the display value, but it is giving me the sys_id   for my value on Reference type variables .

[SME]

Hmmmm

There might be many ways to do it. One of the way I can think right now is :-

1. create a field in Dependent Item (sc_item_option) table called Display Value.

2. Write a script to take the value field, query the reference table specified in question field and get the display value.

3. you can show that display value in your report.