Create a Database View that contains all users and any INC or RITM where they are the caller or requested_for

[Jason Sturgeon]

Is there a more efficient way to build this view?

I have a requirement to report on all users and show all incidents where they are the caller and all requested items where they are the requested for person. The organization wants to know who has needed service from the IT department and those who haven't regardless of who opened the ticket. Looking for trends by other factors such as department or regional differences, high and low volume needs. I can get there once I get a view that contains the users and the related task records.

When I create a DB View left joining to sys_user to incident I get the results I need. Left join sys_user to sc_req_item, that works too. Try doing both in the same view the system times out. 

Any help or ideas is appreciated! 🙂

[Brian Lancaster]

I was getting a strange access violation error until change the order of one of the tables pointing to the sys user table.

 

[Josh Cooper]

Forgive me I'm not fluent in SQL or I'd help with the query, but both incident and sc_req_item roll up to task, so you might try building your database view off task, and in the where clause limit it to where sys_class_name == incident or sc_req_item to prevent getting EVERYTHING.

Note that if it's taking a lot of time, you can also go into the specific tables in the database view configuration and limit the fields you want to show up in the view.  By default it brings EVERYTHING, which can get large.  No need to deal with all the data if you only want 10-15 fields.