How to report on a certain department?

[lee mcdermott]

Hi,

I have done very little in the reporting area and I am struggling a little in finding the fields I need in particular trying to work out the joins etc.

I would like to create a report that shows calls for certain department(s) in Hornbill against user defined date range.

I have tried adding  h_itms_requests and h_sys_groups (as h_sys_groups has a field of H_type where type 2 = department).

However I am not convinced this is correct,

 

Any ideas what tables I would need and how they are joined?

I assume I would need some sql syntax to get results where h_type = 2 and the department name = "actual department name to report on" ??

 

thanks 

 

lee

[Guest]

Hi @lee mcdermott

1) Are you looking to create the report in the standard reporting or through Hornbill Advanced Analytics? (e.g. Dashboards/Widgets)

2) Are you looking to report on a specific department or trying to show the results grouped by various departments?

Kind regards

Bob

[lee mcdermott]

@Bob Dickinson

Hi Bob,

Using the normal reporting tool. Initially I am just trying to get the report to display results for 1 department, but ultimately I would need to display results for a group of departments.

I seem close with the report I have created and have managed to get it to display some results since i logged this, but not 100% sure if I have the right tables selected and the joins are correct?

I have added the tables as below and the itsm_requests is a join to sys_accounts then sys_groups is a left join to sys_account_groups, then sys_account _groups is a left join to sys_accounts

 

thanks lee

[Guest]

Hi @lee mcdermott

This looks correct - it is a little long winded but you have used the correct tables and it appears to be the correct joins. Have you tried this and are the results as you would expect?

Kind Regards

Bob

[lee mcdermott]

@Bob Dickinson

Hi bob,

yes this does seem to be working(i think). 

Are you able to clarify my tables joins or table selections as to how why it is long winded? It's really my first venture into trying to get  some reports done and II couldn't figure out any way to get a users department to show and be linked to calls they log.

Is there an easier way to join these?

 

thanks

 

lee

[Guest]

Hi @lee mcdermott,

I've just had a look at your report and I can validate that what you have performed is correct. 

Unfortunately, in this scenario it is a bit long winded just because of the data structure in the database. As you can be a member of multiple Departments in Hornbill, this value is not something that can be accurate written to the request (as we would not know which department the request should be logged against in this case). Reports against Organisational structure are all a little like this - however, many other reports should be able to be created by just using one table (h_itsm_requests) or possibly 1 join. 

In the future, we are looking to bring reporting into the user app and make it application specific. This should make reporting easier, cleaner and with less effort needed especially around joins. We appreciate that whilst there are users who are SQL proficient, many of our customers are not and we aim to make reporting intuitive and accessible for any admin so there will be some additions to the system coming up. 

Kind Regards

Bob

[lee mcdermott]

@Bob Dickinson

 

Thanks for clarifying. That's sounds like good news to have a specific app to do reports ( i assume it will have a schedule ability?)

 

In this instance it was a good exercise for me to become more familiar with the database structure - it just took quite a while to work out the tables I needed and how to join them.

 

thanks for the advise and for checking it was correct.

 

lee

[lee mcdermott]

@Bob Dickinson

Hi bob - sorry just have a quick query I hope you can answer.

I have duplicated the report I created as I now need to report on a different department.  I have a problem in that the department I want to report on ( called :- Wellbeing - Children's Services) fails with an Invalid SQL statement error. After a little testing I suspect it is failing due to the name having a ' in it as other department names work fine without any ' in them.

 

Is there anyway to get around this ? on the filter I have tried putting the name in quotations but that did not work

 

thanks

 

lee

[lee mcdermott]

@Bob Dickinson

I think I have sorted it.

 

i have filtered on the group id which does not contain any ' rather then the group name and that has provided results