Customer/User Attributes Report

[QEHNick]

I've had a pleasant hour browsing the forums looking for an answer to my requirement (enough to spark several other ideas!),.

However I have been unsuccessful in finding out how to report on Customer/User Attributes, or indeed an customer variables.

I'm sure it's as plain as the nose on my face, any advice would be welcome - thank you.

 

[QEHNick]

I have found the "Customer Coworker" tables, and this is great for reporting.

Is it possible to use the same table for Widgets?

[Bob Dickinson]

Hi @QEHNick

What is the specific requirement? i.e. what would you like your widget to display. 

Thanks

Bob

[QEHNick]

Hiya Bob, 

I'd like it to display a "top ten" count of calls logged by departments for the current week. The department value is currently stored in Customer Attribute 3.

This will help us identify developing "hot spots".

[QEHNick]

I think I may have cracked it, although the date range may be contentious as to the correct results. There might be a better way using a "Filter" but I wouldn't like to guess how.

[Bob Dickinson]

Hi @QEHNick

What you have posted is basically "How many customers have each department set against their profile". If that is what you want, that is fine (with a couple of tweaks as per below - for example I've added a filter to only show customers who have an "active" status. 

 

 

HOWEVER - This has nothing at all to do with requests logged whatsoever as there is no link to the requests table. If you want to use this particular widget in analytics to show this info, there may possibly be a way of manipulating the data, but it will take a little while to work out and I'm not 100% sure its possible. However the main issue with it, is that it could vary - e.g.

Bob is a member of the HR department. He logs a new request on Monday. Nick checks the chart and sees "1 request logged this week from the HR department"

Later that week, Bob joins the IT department. Nick checks the chart again, and now sees "0 requests logged this week from the HR department, but 1 logged for the IT department" - despite the fact Bob logged it when he was working for HR. 
So this data is being collected at the point of time the report is run rather than it being a true reflection of the department at the time of logging. 

The solution - I'm not sure if you are doing this already. But if you have a spare unused custom field across the board, at the very start of every Business Process you could GET the customers attrib 3 field and write this value to your custom field on the ticket (it would have to be the SAME custom field used on every process e.g. h_custom_m or something). This will never change - and you can do all of your reporting from that custom field on the main request table (h_itsm_requests) rather than the table you have used above. The downside is that this will be a bit of work if you have lots of processes, so perhaps only do it on the ones you are keen to measure, and also you can't backdate this so it would only be applicable to tickets logged from the moment you save the new workflows. But it makes the reporting much easier and more accurate. 

However if you still want to go down the first method, let me know and if I get a few mins I'll see if anything at all could be done on that widget.

 

[QEHNick]

Thanks for the time and thought you put into that @Bob Dickinson.

I've modified it to this. The thought being, if they have logged on to the portal, they are likely to be seeking some sort of assistance.

It's not perfect by any means as I'm hoping to capture the number of calls logged per department (and some of these may come through the phones etc.)

If only you could link tables within a widget.

Thanks again Bob.

 

[Met]

Hey,

Have a look to see if this works for you. Use the h_itsm_requests table as the data source and then query the h_sys_accounts table in the series. Create a series for each department you want to report on.

You have to choose a field from the requests table to group on - you could group on source  type if you want to see the number of tickets logged through each channel by each department, otherwise here I've grouped on request type. But as Bob has said, these figures are point in time when the report is run, not when the request was logged.

 

For each department series you can use the following filter (there might be a better way to do this but it seemed to work for me). Replace the text in quotes with your department names: