Join three data base view tables for report

[Siva82]

Hi ServiceNow Community,
@Ankur Bawiskar 
@bhuvan 

I'm working on building a metric report on the sc_task table. To achieve this, I created a Database View that includes two tables: incident and metric_instance.

For the initial setup, I used the following Where Clause:

• Between sc_task and metric_instance:

   Sc Task : sc_sys_id = mi_id & (sc_assignment_group != mi_value)
  Metric Instance : metric_instance.definition = "637b0a258797a210f03865bd0ebb359f"

This view works correctly and returns the expected sc_task metric records in the report.

Now, I want to include incident metric records in the same report. So, I added the incident table to the same Database View with the following Where Clause:

Incident inc_sys_id = mi_id & (inc_assignment_group != mi_value)

However, once I added this third table and join, the report stopped returning data. It seems the join across all three tables is not working as expected.

Can you please help me with the task

Thank you 
Sivananda Reddy

[J Siva]

Hi @Siva82 
Could you please explain the reason for including both Service Catalog Tasks and Incident records in a single database view?
SC Tasks and Incident records are not related to each other as per OOB configuration, so combining them may not yield the expected results.
However, if you still want to build something that includes both SC Tasks and Incidents, I recommend creating a database view between the Task and Metric_Instance tables. 

Regards,
Siva

[Ankur Bawiskar]

@Siva82 

why you want incident to join with sc_task?

both tables are for different purposes.

if you have linkage between incident and sc_task then only it will work

how are you linking them?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Bhuvan]

@Siva82 

I am not sure if you tried to tag me but it is wrong ID.

Catalog Task sc_task table

Metric Instance metric_instance table

Incident Metric incident_metric table

When you first join sc_task and metric_instance tables you have a common ID sc_sys_id = mi_id, hence it is working fine. But when you join incident metric and metric instance, it does not have a common reference between 3 tables. You need to have a common reference across 3 tables to use the database view table. Please see below for sample on how to use 3 tables in DB view

As per community guidelines, you can accept more than one answer as accepted solution. If my response helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan