Building reports - All WI by Support Group
I tried to produce this information by a simple saved search, however the columns are limited. I need the report to include resolution/results and the date it was completed. Can I do this with some SQL in the Analytics section. I had such report written in SQL Server Report Builder on our original Service Manager install but cannot seem to match the tables/views in our new database.
Shane_White Cireson Support Super IT Monkey ✭✭✭✭✭@Emma_Jenkins
Try this, it is probably not the best thing to do but it works! Because it is a UNION you have to have the same column names thats why I named the SR ResolutionDescription.SELECT WorkItemId AS Id, Title, Status, PriorityId AS 'Priority',AssignedUser, AffectedUser, Category, Tier, ResolvedDate,ResolutionDescription, LastModifiedFROM dbo.SM_WorkItem_Incident AS wiIRWHERE Tier like '%Tier 1%'UNIONSELECT WorkItemId AS Id, Title, Status, Priority AS 'Priority',AssignedUser, AffectedUser, Category, Tier, CompletedDate AS 'ResolvedDate',SCSM.Notes_F647D4C9_9543_73C4_79D0_FCB06C288B5D AS 'ResolutionDescription', LastModifiedFROM [CiresonAnalytics].[dbo].[SM_WorkItem_ServiceRequest] AS wiSRLEFT JOIN [ServiceManager].[dbo].[MT_System$WorkItem$ServiceRequest] AS SCSMON wiSR.Id = SCSM.BaseManagedEntityIdWHERE Tier like '%Tier 1%'
This is for everything resolved today for a specific support group from the ServiceManagement DB
Have you tried using the Cireson Analytics DB? Using Incidents for an example you can do a lot of this without all of the joins:
Of course you can add bits to this to be more specific but this is a basic example, I would be happy to build something out more specific to what you are after if you let me know You could also add other work items onto this using a little fiddling with UNIONs
How is this? Bear in mind you just need to change the name of the Tier for whatever your Support Groups are called ('%support group%')
I have noticed Resolution Description is a shared column in both tables but when I run the report this is empty on SRs so not sure if and where that information would come from?
Really appreciate your help!
Leave this with me! The SR Implementation Notes as stored in the Service Manager DB but not pulled through into Cireson DBs so will try find away around this!
Try this, it is probably not the best thing to do but it works! Because it is a UNION you have to have the same column names thats why I named the SR ResolutionDescription.
That second script has given me exactly what I need. Thank you for your help, information you have provided has also given me a good basis for similar reports