How to filter a report by department

[dwalby]

Hi all,

I'm trying to setup a weekly scheduled report showing all open requests for a particular department, I've managed to setup the columns I require and filter by request type (Incident & Service Request), however I'm struggling to apply a filter to only show requests from a particular department.

Any suggestions on how to achieve this?

Thanks in advance

[Steven Boardman]

@dwalby perhaps the following forum discussion will help?

Steve

[dwalby]

Thanks @Steven Boardman - I'm struggling with this a little, this is what I've done so far (as I want the full department name, not ID) - I'm assuming the error is because I also need to join the h_itsm_requests table somehow?

[Steven Boardman]

Hi @dwalby you will need to do a couple of joins here:

Firstly add the h_itsm_requests table then the h_sys_account_groups - highlight the later and apply a join as below.

Once you have done this add the following table: h_sys_groups

if you then highlight the newly added table you can add the following join and you should be on your way.

 

You should then be able to refer back to the forum link above to apply the filters you want on your report, bearing in mind the below

You may be ok with the h_group_id values in this table, but if you want there full display names, then you can join the h_sys_acount_groups table and h_group_id (column) with the h_sys_groups table and the h_id (column), which will then give you access to the groups display name h_name (column).

For information purposes, It is the h_sys_groups table which holds all system groups (department, cost centres etc) and each holds a group type value, in the h_type column - department types are represented by integer value 2.  You should not need to know this if you are creating a report on requests departments but it may be good to know

Not forgetting that if you want to you can change the display name headers for columns - so maybe h_sys_groups.h_name to Department as shown below

 Steve

 

[dwalby]

@Steven Boardman - Thanks for this worked perfectly