Other Options for Filtering Data on SQL Widgets

Brett_Moffett

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.

Jason_Meyer

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

Mikkel_Madsen

and completed/resolved, closed please :)

Chris_Hardwick

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

Geoff_Ross

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

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

Geoff_Ross

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_Zeinhofer

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_Ross

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_Zeinhofer

@Geoff_Ross thank you very much :)

Geoff_Ross

Hi @Simon_Zeinhofer

A new version with fixes is now available.

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

Simon_Zeinhofer

@Geoff_Ross i am speechless :O

Thank you for this fast reaction :)

Geoff_Ross

@Justin_Workman is the real hero here.

Simon_Zeinhofer

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

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

Simon_Zeinhofer

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

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_Zeinhofer

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_Workman

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

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 :)