Home General Discussion

Creating SQL Query for Widget

Steve_CoxSteve_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


Best Answer

Answers

  • Steve_CoxSteve_Cox Customer IT Monkey ✭

    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_MiklianPeter_Miklian Customer Advanced IT Monkey ✭✭✭

    @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_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

    @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'

    )

Sign In or Register to comment.