Report not displaying variables in database view.

[Balaram7]

Hi all,

I am trying to create a report on RITM and Attachment table by creating a database view.

Iam able to view the attachments and RITM number, but unable to select the variables.

Do i need add another table to database view to get variables information.

or is there any other way to get the following fields in the report.

(Attachment link, Ritm #, variables1, variable2, variable 3.)

please help me if there is any possible ways to get this requirement done.

[Tanushree Maiti]

For variable , you need to add another 2 tables in your database view (joining sc_req_item with sc_item_option & sc_item_option_mtom)

sample

Table                                                         Variable prefix                                     Where clause

sc_req_item                                                    ritm              

sc_item_option_mtom                                mtom                                                  mtom.request_item = ritm.sys_id

sc_item_option                                             option                                                options.sys_id = mtom.sc_item_option

Refer: https://www.servicenow.com/community/sysadmin-forum/ritm-report-with-all-variables-and-approval-deta...

[Balaram7]

Hi Tanushree,

Thanks for your response.

I have created database view, still i am unable to see any records displaying in the report and variables also unable to fetch,  can you please suggest me if there is any other way to get this information

.

[jeffreybell]

What you are doing is actually pretty complicated. I know the RTIM looks like one record, but behind the scenes (my understanding) ServiceNow stores variables like the following 

'Row'      RITM           Variable Name    Answer

-----------------------------------------------------------

1              RIMT001    Color                     Silver

2             RIMT001    RAM                       16GB

3             RIMT001    Storage                  512GB

Each answer is its own row in a separate table.  So when you try to build the report it looks kind of like

RITM                Color    RAM   Storage

---------------------------------------------------

RITM0001    Silver    16GB     512GB

So the database would have to flip three rows into 3 columns and that s a transformation (A pivot for all you Excel fans out there).

I would try (make sure) to join 'sys_attachment.table_sys_id' to the 'sc_req_item.sys_id', then add a condition in the report filtering 'sys_attachment.tablen_name = sc_req_item'

Its possible you might be able to also just build a report on the sys_attachment and then filter 'table_name = sc_req_item'  and then you can dot-walk to the RITM fields (this avoids a database view)

If neither of these approaches work and if someone doesn't provide a solution that works for you, I would open a case with ServiceNow and 'ask a question' as the type of case. See what the experts say.

[Ankur Bawiskar]

@Balaram7 

you have not given the proper order for the view tables, your where clause is proper

Just ensure the sequence is proper

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏