How to create report with count from another table?

[TT3]

I need to create a report for company table. The report should show company name, total incident reported and total number of users. I am not sure if this is even possible in ServiceNow. Any idea?

Report format:

Company Name (From core_company table)	Company Phone (From core_company table)	Total Incident Reported (From incident table)	Total users (From sys_user table)
ABC	123-456-7893	5	2
PQR	456-456-4569	2	1
XYZ	789-789-7890	50	25

Basically, for company how can we bring other fields by querying separate tables.

Things I tried:

Create database view to join company, incident and user, but just after I join user, the view started showing invalid records as it tried to join company with incident and user all together. This would have been very easy in traditional development/reporting environment.

[Kalyani Jangam1]

By Using database view it will possible but for database views there is common field required between incident and company table(core_company) as OOB there is no such common field. In your scenarios if any common field then you will proceed with database view.

[TT3]

Yes, we do have reference to company field in incident, however I am not sure if that will help to achieve what I need. In other words, this is what it shows in the view:

(An agency have 4 users and so far 2 incidents have been logged)

Company Name	Incidents	Users
ABC	INC1	User1
ABC	INC2	User2
ABC	INC1	User3
ABC	INC2	User4
ABC	INC1	User1
ABC	INC2	User2
ABC	INC1	User3
ABC	INC2	User4

As you can see, it is repeating much of the details and I am not sure if I can get count of both columns in view.

[Kalyani Jangam1]

Hi,

As per your requirement, tried following take company field and incident for user and incident consider caller field in incident table like below

Create report directly in incident table and Group by both company and Caller field so that we can understand which user and company associate with incident like below

Please check this helpful or not