Creating SQL Query for Widget

Steve_Cox

I'm not sure where the right place to post this is but this is all new to me so I thought I'd start here.

We have a custom dashboard for our Logistics dept. They would like us to add a Widget that displays a clickable summary of items assigned to them for the month. I've managed to create a custom Search query that displays all the work items that meets their criteria. How do I convert this to a SQL query that shows a summary of the data with links to details?

Thanks!

-Steve

Peter_Miklian

Unfortunately I don't think you can convert Work item search into any analytics report. You'd have to write the SQL query and create new dashboard page displaying result.

Some sources to study:

1. How To: Add and Edit a Dashboard Page
2. How To: Manage Dashboard Queries
3. How To: Set Dashboard DataSource Settings
4. Web page displaying amount of SCSM Incidents, Service Requests and Change Requests

Peter_Miklian

Unfortunately I don't think you can convert Work item search into any analytics report. You'd have to write the SQL query and create new dashboard page displaying result.

Some sources to study:

1. How To: Add and Edit a Dashboard Page
2. How To: Manage Dashboard Queries
3. How To: Set Dashboard DataSource Settings
4. Web page displaying amount of SCSM Incidents, Service Requests and Change Requests

Steve_Cox

Thank you Peter. A Cireson Support Engineer helped walk me through creating the SQL query I needed and it worked perfectly. I'll take a look at the resources you suggested too. I've got a lot to learn! :-)

Peter_Miklian

@Steve_Cox maybe you could share it to help some other Cireson customers in similar situation if it doesn't contain internal data or it's not a private property of your company/Cireson. Thanks.

Justin_Workman

@Steve_Cox - Here's a query against the ServiceManagement database that will do roughly the same thing your screenshot of the Saved Search will do. Then just apply it to a dashboard page like @Peter_Miklian suggested and the results should be very similar.

SELECT 

WorkItemId, 

Title, 

AffectedUser, 

AssignedUser, 

ds.DisplayString as 'SupportGroup',

LastModified

FROM WorkItem w

LEFT JOIN DisplayString ds

ON w.TierId = ds.ElementID

AND ds.LocaleID = 'enu'

WHERE w.Created > DATEADD(month, DATEDIFF(month, 0, getdate()),0)

AND 

(w.Title = 'UPS Delivery Intercept'

OR 

w.Title = 'Truck Claim'

OR

w.Title = 'Truck Return Request'

)