Options
Creating SQL Query for Widget
Steve_Cox
Customer IT Monkey ✭
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
0
Best Answer
-
Options
Peter_Miklian
Customer Advanced IT Monkey ✭✭✭
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:
- How To: Add and Edit a Dashboard Page
- How To: Manage Dashboard Queries
- How To: Set Dashboard DataSource Settings
- Web page displaying amount of SCSM Incidents, Service Requests and Change Requests
6
Answers
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:
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! :-)
@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.
@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'
)