Reports

[Akash Savani]

When creating reports which table & column would departments come under.

[Gerry]

I think that might be h_sys_groups, I know the name of the table might be counter-intuative so I could be wrong, but take a look there to start with

Gerry

[Steven Boardman]

@Akash Savani your users through the admin tool, and under Organisational Data > Organisations may have been assigned to Department groupings.  If this is what you are looking to report on then take a look in the h_sys_account_groups table.

If you are looking to create a report on requests by department then you would need to join the h_itsm_requests table with the h_sys_account_groups on the h_fk_user_id column in the request table and the h_user_id column in the account groups table. 

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

Hope that helps

Steve

[HGrigsby]

I may be missing something really obvious but I have followed the advice above and can;t get the join right. 

I can't find the h_fk_user_id to join in the requests table

What I want to achieve are reports  and measures that let me select the department to give figures for the calls logged for them over the previous month

Can someone offer some advice please?

 

[Steven Boardman]

@HGrigsby sorry in the report creator it is using the display value for the table column name, so if you use Customer ID from the request table this should give you what you need

[HGrigsby]

Thank you  Steve - I just found it!   

Is there a way to select the department as you run the report?  We have service reviews that we hold with the teams/departments every month so I'd like to have one report that I could run and select team and the date range.

I wondered if using a simple list might be the answer but the wikki page was blank

 

[Steven Boardman]

Hi @HGrigsby

You can use input parameters at report run time and you can add these in the report Select Filters to give you something like this when you do a data preview or run a report manually

In the example above i have added two user inputs as follows:

Department:

 

We can't use the simple list here, but what i have done is limit my group types to department in the first filter in the filters (h_sys_groups -> h_type equals 2 (department). Then i have added a against user prompted value, using a static list option and i have manually added my departments as options - so here Finance and Marketing - which are then available for me to select from when i run the report

Date:

Again use a against user prompted value and then choose Between Date / Time Selector on whichever date field you want to use (Date logged, Closed etc).

You can of course add and use which ever filters you want.

Alternatively you could set up the reports for each department and have them auto-created (scheduled) each month and distributed to documents in  document manager where each months version of the report will be added and you will have a single document for each department which will contain the history of each months figures which you can collaborate on and discuss the results.  You can also share the document with the required users and they will get this automatically and pick it up on the mobile app if needed. 

Using the report scheduling and publishing options you can create a report for each department and then inject the date range as a variable which will be used when the report is run, so for example if you ran this at 1am on the first of each month, you could have it look back at all requests logged in the lastmonth?

More info on this on the wiki here:  https://wiki.hornbill.com/index.php/Report_Scheduling

Hope that helps

Steve

[HGrigsby]

Thank you again Steve!   It has worked for me and I can get the first one done for this month then work out the scheduling and publishing for next.

Helen 

 

 

 

[HGrigsby]

Hi 

I think the answer is no ... but can I use the same tables to allow me to filter out a departments tickets to create a measure/widget/dashboard?

Helen 

 

 

[Steven Boardman]

Hi @HGrigsby

You can do something with the widgets to add to dashboards

If you choose a widget type of List of Counters and if you use something like this:

replacing the Ares/Finance with the group id's of your relevant departments.  

 You can then add these widgets to a dashboard to show counts by departments tweaking the date ranges as you need them.

You can get the h_group_id's for your departments via Database Direct in the admin console.  If you actually look at the h_sys_groups table you can use the values from the h_id column and remembering that departments (types) are of h_type = 2 where you might have different groupings such as cost centres or support teams with similar names but are different grouping types (1,3 etc).

 Hope that helps

Steve

[HGrigsby]

Thank you again for your help, Steve

I have a range of widgets now which I can use for these service reviews.   

It would be really useful if we could use the department or cost centre in some of the other types of widget to create graphs etc.

Thanks Helen 

[Steven Boardman]

@HGrigsby you could create a measure as follows

Again replace Ares/Finance/ with your departments

This would give you a history of the requests logged per month for the department last 12 (all configurable)

As department is not an attribute of the h_itsm_requests table you can't save it as a Saved Data Column on one measure so you would need to create a measure for each department, but when you have these you could create other widget types for your dashboards.

One example would be to create a data chart type using Measured Samples and then add a New Series choosing the relevant measure for each department to show comparisons between departments over time.

In my example i have just used one series (Measure) and called it Finance, but you can use the Add new Series to add more measures for other departments, you can also change the Sample Period to look at this year, start of last year etc and other options in the config options to do things like:

* Bar / Line

* Stack 

* Stack as 100%

* Show values as % etc. 

Once you have the measures you can also create scorecard types and include multiple measures on the same scorecard widget.

I hope this allows you to create more widget types for your dashboards

Steve