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.
49 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 O.G.

    @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

  • Geoff_RossGeoff_Ross Cireson Consultant O.G.

    Hey lovely community folks,

    Page level filters on ANYTHING are now possible with the Page Filter Widget.

    Check this out

    https://kb.cireson.com/article/user-guide-add-in-page-filter-widget/2620

    https://cireson.com/blog/speed-up-service-desk-data-analysis-with-new-page-filter-widgets-add-in/

    Let me know your thoughts.

    Geoff

  • Simon_ZeinhoferSimon_Zeinhofer Customer Advanced IT Monkey ✭✭✭
    edited September 2022

    hello @Geoff_Ross ,

    might there be an issue with the DateBetween filter or with the date filter in general??

    After I set both Paramater names and displaynames + the Paramater Type DateBetween and save, the first parameter always gets deleted, also no displayname is shown in the dashboard.

    When I edit it again, the following is shown:


    When I pick only "Date" and want to filter e.g. for a startdate, my Month and day get swapped.

  • Geoff_RossGeoff_Ross Cireson Consultant O.G.

    Hi @Simon_Zeinhofer

    Hmmm. Software have bug? I wouldn’t have thought so. ;)

    Oops. Let me see if I can replicate this and we’ll go from there. I’ll open you a ticket in the support portal.

    Geoff

  • Simon_ZeinhoferSimon_Zeinhofer Customer Advanced IT Monkey ✭✭✭

    @Geoff_Ross thank you very much :)

  • Geoff_RossGeoff_Ross Cireson Consultant O.G.
  • Simon_ZeinhoferSimon_Zeinhofer Customer Advanced IT Monkey ✭✭✭

    @Geoff_Ross i am speechless :O

    Thank you for this fast reaction :)

  • Geoff_RossGeoff_Ross Cireson Consultant O.G.

    @Justin_Workman is the real hero here.

  • Simon_ZeinhoferSimon_Zeinhofer Customer Advanced IT Monkey ✭✭✭
    edited September 2022

    @Geoff_Ross I mean thanks to all of you guys for what you provide :)


    Tested the new version and it is fantastic :)

    Just one question (as harsh as it is to ask it after the add in came out one week ago): is it planned that more enumerations will be available? E.g. Hardware Asset Model etc.?

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

    @Simon_Zeinhofer - I just missed some of those list values out. I'm working on an update now...

  • Simon_ZeinhoferSimon_Zeinhofer Customer Advanced IT Monkey ✭✭✭

    @Justin_Workman I didn't mean to stress here ;) It was just a question :)


    But really cool you guys are working on that already! thank you so much!!

  • Geoff_RossGeoff_Ross Cireson Consultant O.G.

    Hi Simon,

    Haha, we always knew this would be asked for - just wondered how long it would take. Turns out it was very quickly. We love that you are using it and pushing the limits right out of the gate. Keep going.

    What kind of things are you building?

    Geoff

  • Simon_ZeinhoferSimon_Zeinhofer Customer Advanced IT Monkey ✭✭✭
    edited September 2022

    Hello @Geoff_Ross ,

    right now I added it to our KPI dashboards (which we build up on the Ciresonanalytics DB - took the query from @Brian_Wiest from here: https://community.cireson.com/discussion/4420/scsm-dw-query-backlog), where I now have a dashboard for all Incidents as well as one for filtering through the support groups and one to get the KPIs for a certain date (that's why I had to use the date filter so soon :D ).

    (Would be fine if you could also deactivate the filter so it shows all data, so I do not need to build 2 dashboards, one without filtering and one with it. But I guess this would be hard to accomplish, as it would need to rewrite the query when the checkbox is set)

    Today I already activated the Wordcloud widget with incident classifications and added it to our "IT Active Incident" dashboard - I guess our big boss won't be amused as "Other problems" is by far the biggest word in the cloud :D

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

    @Simon_Zeinhofer - I think it might be possible to do some "creative querying" to accomplish what you're after in terms of disabling all filters...I'll play with this and see what I can come up with. FYI, we're having an open floor meetup today where I'll be showing off all the new widgets if you want to join:

    https://get.cireson.com/communityopenfloor

  • Simon_ZeinhoferSimon_Zeinhofer Customer Advanced IT Monkey ✭✭✭

    Good morning @Justin_Workman ,

    thank yuo for putting so much effort into that :)

    Unfortunately the open floors are at a time, where I am no longer at work. But I will try to join it outside of my working hours next time :)

Sign In or Register to comment.