Request Connections Count Widget/Measure

[George Warren]

We are looking to find a way to nicely present a live view of active requests/incidents ordered by the count of how many connections it has (impacted/interested users), I have been able to produce this in reporting by linking the request table to the connections table so i can filter on just open statuses, but we would prefer to see this in a measure of widget. 

Is there a way to achieve this as looking at the documentation for measures it mentions a box for "Additional Tables - This is where you would specify any additional tables from which the measure will collect its data", but i cant see that box when creating a measure. Or should i be able to use the query box to specify filtering based on fields/columns within other tables?
 Measures (hornbill.com)
Otherwise it seems measures are just linked to a single table. 

[Conor]

Hi @George Warren

I can share an example widget config I have for change requests that you could tweak to your needs. It uses a 'List of Data' type widget, with a 'Custom SQL Query' List type, and the following SQL (again this is for changes but links the tables I think you are after):

SELECT h_request_id As Reference, h_itsm_requests.h_summary AS Summary, COUNT(h_request_id) AS Connections from h_itsm_request_connections
inner Join h_itsm_requests on h_request_id=h_pk_reference and h_requesttype='Change Request' and (h_status='status.open'  OR h_status='status.new')
Group by h_request_id
order by Connections desc
Limit 10

Looks like this:

 

[George Warren]

@Conor - Thank you so much this is exactly what I was looking for, greatly appreciated!