Other Options for Filtering Data on SQL Widgets

Comments

• Jason_Meyer Customer Advanced IT Monkey ✭✭✭

  May 2018

  We would like to have 'filtering' capabilities on the Dashboards and Analytics pages.

  0
• Mikkel_Madsen Customer Advanced IT Monkey ✭✭✭

  July 2019

  and completed/resolved, closed please :)

  0
• Chris_Hardwick Member IT Monkey ✭

  July 2021

  A @resolvedfilter based on resolved date would be useful for us.

  0
• Geoff_Ross Cireson Consultant Super IT Monkey ✭✭✭✭✭

  July 2021

  @Chris_Hardwick

  If you are good at SQL and very very clever you can make this work.

  What the @createdFilter function does is append to your query an extra WHERE clause with:

  Created > 'whatever option you picked'
  

  All you need to do is make sure the date you want to filter on is returned as 'Created'. You can do this by wrapping your whole query in another SELECT statement and alias your resolved date column as 'Created'. Here's an example with a super simple query.

  SELECT
  	WorkItemId,
  	ResolvedDate AS Created
  FROM (
  	SELECT
  		WorkItemId, 
  		ResolvedDate
  	FROM [ServiceManagement].[dbo].[WorkItem]
  	WHERE ResolvedDate IS NOT NULL
  ) t
  

  Let me know if this works for you.

  Geoff

  0
• Chris_Hardwick Member IT Monkey ✭

  July 2021 edited July 2021

  https://community.cireson.com/discussion/comment/23008#Comment_23008

  Thanks @Geoff_Ross,

  I'm not an expert at SQL and also not very Clever 8-). Luckily I have someone who is who sorted it out for us. (Onya Deco!) FYI here's the query for an analytics dashboard displaying work item counts (SR and IR's) per analysts based on Resolveddate(IR's) or CompletedDate(SR's):

  Note the StatusId's and TierId's would need to be modified to suit to individual environments.

  
  

  SELECT TOP 50 ISNULL(b.udisplayname, 'Unknown User') as AssignedUser, COUNT(DISTINCT b.workitemid) As ResolvedCount

  FROM (

  Select a.Created, a.udisplayname, a.workitemid, a.AssignedUserID

  FROM (

  Select

  case 

  when CompletedDate is not null then CompletedDate

  when ResolvedDate is not null then ResolvedDate

  else NULL

  end as Created,

  wi.AssignedUserID as AssignedUserID,

  u.DisplayName as udisplayname,

  wi.WorkItemID as workitemid

  From WorkItem wi

  INNER JOIN CI$User u ON wi.AssignedUserId = u.Id

  LEFT JOIN [Access_CI$User_WorkItem] a ON wi.Id = a.WorkItemId

  WHERE ((ResolvedDate IS NOT NULL) OR (CompletedDate IS NOT NULL))

  AND StatusId IN ('BD0AE7C4-3315-2EB3-7933-82DFC482DBAF', '2B8830B6-59F0-F574-9C2A-F4B4682F1681', 'B026FDFD-89BD-490B-E1FD-A599C78D440F') --Closed, Resolved, Completed

  AND wi.TierId IN ('A523B637-923E-3234-223A-A89EE23C0EFF', 'A2D23160-423D-C237-D231-1D032523D8E2', '09C23CE1-2235-C236-B230-66ECE62351B2', '6BD23CDB-123C-823E-1E02-CD8C8F92372F', 'FCE2363C-8A83-2F26-EF04-0C2EC237E3CD', '97323EA4-8236-E23F-8FD7-C27A423F6141') -- Our appropriate resolver groups both sr's and ir's

  ) as a

  
  

  where 

  @createdFilter

  ) as b

  
  

  GROUP BY AssignedUserID, b.udisplayname

  ORDER BY ResolvedCount DESC

  0