How to Create Database View ?

[Eddie5]

Can you guide on how to join tables using database view in servicenow ?

We are trying to join tables as below into one database view , can we create two joins and then add into one - how ?: 

1. asmt_assessment_instance
2. asmt_metric_result
3. task
4. sc_req_item
5. sc_req

Goal : We want to get survey results for Inc and Req and built breakdown on assignmentgroup
Note : assignemnt group field is only on request item form.

[Vivi Brasil]

Hello Eddie,

I would suggest you to check the video and the presentation available on this Community Blog's post:

• Simplifying Reporting on Surveys

It is a very good resource to understand how to create the Database View, and how to join the Survey tables.

I hope it could help you.

Best regards,
Vivi Brasil

[Ethan Davies]

Hey Eddie,

There should already be an out of the box database view that you can leverage for this kind of reporting. Navigate to your database views and search for the task_assessment_detail database view.

If you want to restrict the results that are returned from this database view to only incident and request then you could update the WHERE clause in the task table entry to only filter for those table types, it could look something like this:

The above WHERE clause might not be exactly spot (I didn't have any data to test with) on but you get the idea.

I've attached the database view and the tables as XMLs to this answer in case you don't have this database view present in your instance. Import sys_db_view first, then sys_db_view_table.

Hope this helps.

Please mark the answer as correct or helpful if it aided in solving your issue, thanks!

[Eddie5]

Hi Ethan,

Thanks for your response and it helps on database view, however we are still not able to achieve on our requirement to get request item (RITM) and request (REQ) table joined together with task and asmt_metric_result table.

Goal : We want to get form owner field on report which is available on request item and that field comes from Catalog Item table so we want to join metric table to task, request and request item table 

If you can help on which unique field we can join them together will really be of great help.

Thanks You!

[Ethan Davies]

Hey Eddie,

What you need is to add those tables to the database view and perform a left join on them with the task table.

Take a look at the documentation here, it contains an example that is very similar to your scenario.

Let me know how you get on, if you run into issues I can try and throw something together and post it here.

https://docs.servicenow.com/en-US/bundle/sandiego-platform-administration/page/use/reporting/task/example-left-join-db-view.html

Thanks,

Ethan

Please mark the answer as correct or helpful if it aided in solving your issue, thanks!