Creating a Database View between Incident and Problem Tables

[simonbourne]

I have a reporting requirement that requires dta from both the Incident and Problem tables.

I want a report that will list ALL the incidents and include the Problem ID, State and Created Date where there is a relationship between the Incidents and Problems.  If  I try this by setting up a report that dot walks from the Incident table to the Problem table then it works fine....for our internal analysts.  But our external clients cannot pull back the dot walked Problem fields.  They do have access to the Problem table because the same report will run successfully on the Problem table.

My thinking is that a database view between Incident and Problem could help.  I have a Reference field on the Incident Table called Problem_ID that maps directly to the Problem.Number field, so Incident.Problem_ID = Problem.Number.

How do I express that in the Where Clause of the database view?

How do I create that Database view so that it contains ALL incidents INCLUDING those with a Problem_ID and is not restricted merely to those where the relationship exists?

[Goran WitchDoc]

Hi Simon,

You need to check the "left join" field. No idea why it isn't on the form OOB, but that will solve you problem with should all records, not only those with a relationship   between the tables.

"Selecting Left join causes the left-hand table in the database view to display all records, even if the join condition does not find a matching record on the right-hand table. Select this check box for view tables that specify a Where clause. Selecting Left join for view tables without a Where clause does not affect the query."

You will find more info about it here: https://docs.servicenow.com/bundle/kingston-platform-administration/page/use/reporting/task/t_AddATa...

[simonbourne]

Goran,

I was just investigating that.   I've tried every combination of True False on the Where clauses but I always end up with the same number of records.

See below.... what am I missing?

[adamjgreenberg]

Simon,

Incident, change and problem are all rolled into task. I think you could simplify this by running a report against task directly.

task.sys_class_name = 'incident' , 'problem' or 'change'

task.a_ref_1 (problem.rfc) = sys_id of the linked problem or change.

Example:

I'm hoping this can get you on track to your goal.

[simonbourne]

Adam,

You're right, but the reporting solution that i am trying to achieve cannot be run from Task.

I'm looking to generate the report from the Incident table using information from related Problems, so the database view is the best approach