Adding database view to report

[cicgordy]

Hi all, 

I want to create a report in servicenow on the sc_req_item table and I want to show a list of hardware catalog item and group by department (cmn_department, only issue is that I cannot dot walk from a "department - reference field variable" to group by department and other fields on that table, how can I do that?

I thought of creating a database view, so far I created the view tables 

and added the following fields to each of them: cmn_department with following fields("u_catalog_variable" and "dept_head") and sc_req_item with following fields("requested_for", "due_date", "number", "request", "quantity", "cat_item" and "stage". (see images below)

How do I make the report work? I guess I need to add "where clause"? how can I do that? 

Thanks

[Bert_c1]

Review existing Database views to see examples of "Where" clauses.  Add a where clause on the sc_req_item table in the Database view that links a field on sc_req_item to 'cmn_department'.  Looking at the two tables, I see cmn_department has a reference field 'user' so your where clause "ritm_requested_for = dept_primary_contact" may work for you.

[cicgordy]

Hi @Bert_c1 , will you also be able to help me with writing the where clauses please? These are the three tables I created. Thanks

[Bert_c1]

Please be clear on what fields in sc_req_item related to records in cmn_department. I posted a where clause in my previous response based on the DB view containing the two tables. You have added cmdb_ci since. There is a reference field named 'cmdb_ci' on 'sc_req_item' table to the 'cmdb_ci' table. to link those two tables, use: cmdb_sys_id=ritm_cmdb_ci for the where clause on that entry. but then linking cmn_department to cmdb_ci is not clear.