Adding Department Field to a Report

[AnthonyUSS]

Hi can someone tell me how to add 'department' when creating a report? Department is available in Platform Configuration> Organistaional Structure, however there is no department field available when creating a report in Service Manager??

[Frank Reay]

You have to join a few tables so you need to use a Report Type of SQL Schema Designer.

h_sys_account_groups tells you the Org Units linked to a person.

h_sys_groups tells you which of the Org Units is a Department (ie Type 2).

You have to join the above to something like the Requests table depending on the criteria (ie Filter) you are basing your report on.

[AnthonyUSS]

Thanks can see it now 

 

[Julie K]

Is this something that you were able to do yourselves or did you need support from Hornbill?

 

[Sam P]

I am quite new to Reports bu t this may help you as I was looking for the same thing.  You might need to add filters or change the columns according to what you need. 

all-users-with-department.report.txt

[Frank Reay]

I did it myself using the wiki and of course Google! If you can just use the Report 'Entity' option then that is easier but for the query above you need to Join tables so have to use SQL Schema Designer (and probably also Database Direct under Platform Configuration)

But it needs some time and effort. You will need some SQL Coding knowledge so it depends how technically minded you are (or if you have someone who can help).

[Julie K]

Is it possible to return the whole string within one field as we're looking to get the Company, Division and Department in one field? 

[SamS]

On 03/10/2023 at 08:56, Julie K said:

Is it possible to return the whole string within one field as we're looking to get the Company, Division and Department in one field? 

@Julie K, in short "No".
It is currently not possible to have a calculated field in the result set.