Home Service Manager Portal Feature Requests
We appreciate you taking the time to vote and add your suggestions to make our products awesome! Your request will be submitted to the community for review and inclusion into the backlog.

We recommend reviewing what is submitted before posting, in case your idea has already been submitted by another community member. If it has been submitted, vote for that existing feature request (by clicking the up arrow) to increase its opportunity of being added to Cireson solutions.

For more information around feature requests in the Cireson Community click here.

Other Options for Filtering Data on SQL Widgets

Brett_MoffettBrett_Moffett Cireson PACE Super IT Monkey ✭✭✭✭✭
Currently within Analytics, there is the @createdFilter option that works for any group of work items.
It would be very useful to have other options based on other enumerations such as:
  • Support group
  • Resolution category
  • Classification
  • Priority
  • Impact
  • SLO
  • etc.
48 votes

Submitted · Last Updated

Comments

  • Jason_MeyerJason_Meyer Customer Advanced IT Monkey ✭✭✭
    We would like to have 'filtering' capabilities on the Dashboards and Analytics pages.
  • Mikkel_MadsenMikkel_Madsen Customer Advanced IT Monkey ✭✭✭

    and completed/resolved, closed please :)

  • Chris_HardwickChris_Hardwick Member IT Monkey ✭

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

  • Geoff_RossGeoff_Ross Cireson Consultant Super IT Monkey ✭✭✭✭✭

    @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

  • Chris_HardwickChris_Hardwick Member IT Monkey ✭
    edited July 2021

    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

Sign In or Register to comment.