Cost Center Database view review

[Joel O]

Hi everyone,

I'm working on a database view and I'm not getting the outcome I'm looking for. Grant it I'm not sure I'm able to with the 3 tables I'm including in this view, but your quick review and comments are needed. 

Expected Outcome: We have applied a cost center to all end users, thus populated the Cost Center table according to our internal requirements. Since all callers will have a cost center associated, we need to pull a report on total incidents submitted per month and charge back to the caller's cost center on the amount of time spent to resolve the tickets.

Sample question to report on: What is the total time spent in resolving tickets for Cost Center name: Corp Serv, code: 123456, over the course of a month and which agent(s) had resolved those tickets?

My ideal report would be a multiple pivot table that has the list of Cost Center names & codes as columns and then the assigned to agents as rows with the time spent based on the information. 

A screen shot of my attempted database view is below. When my cost center table is not active (1st screenshot), I'm able to pull some information but the Cost Center is empty. If I make it active (2nd screenshot), I get the error seen on the 3rd screenshot. Even if I make it left join, same result on an empty Cost Center field. I think I just need fresh  and more experienced eyes on correcting my mistakes. 

Screenshot #1

Screenshot #2

Screenshot #3

Appreciate your review and replies. 

Thank you. 

[-O-]

Did you add all the fields used in the where clauses to the list of fields for the tables.

If you click table "sys_user" and the View Table record opens, in related list "View Fields" do you have "Cost center"?

[Joel O]

I did not and so I went to add the fields in the where clause to their tables: cost center in the sys_user table and caller_id to the incident table. when I went to Try It, it gave me blank information, no data in the fields. 

Although reading your other reply, I see your point and this does make sense. I suppose I'll have to look into rearranging my database view to make your suggestion work. I'll update this thread with my revisions. 

Thank you

[-O-]

You're welcome

[-O-]

Also there is no point in using left joins.

That would include incidents that have been created for users that have no cost center assigned.

But since the reason is to charge cost centers, what's the point of including records that are not associated with any cost center.

Unless you want to audit unassigned incidents.

Also this view is a live report that might not be what you need.

I mean if you now issue a report, you'll get a result and you will invoice it.

Next month some of the users will change Cost centers and someone audits charges and discovers that what has been invoiced, does not correspond to what the report shows at that moment.

What I'm trying to say is that ideally you should not report on cost centers associated with users, but you should save the cost centers with the incidents.

Thus even if a user at some point in time changes the cost center, both the invoicing and later audits will be correct and will match up.