Who is marking dockets complete?
If you have events audit trail turned on you can use the SQL code below to create reporting metrics (last 30 days) on who, and how many, dockets are being completed per professional.
What I like to do is embed the SQL code in an ODBC lookup* to the database. You can also set the connection setting to refresh on opening the workbook. Then, you can create cool pivots like in the screenshot. This enables management/supervising attorneys to see dynamically who is completing dockets in your system.
*you do need to connect to your SQL db to pull info, but it can be set read only, you just have to know how to create the ODBC in your environment.
FREE CODE! Use at your own risk. I would recommend having someone with knowledge of your system or how to run audit queries from development studio so you don't over tax your system for a report.
Audit tables are not the best organized in the system, and because of this, even with indexes performance is usually poor. Be careful of changes in the syntax. Call if you need help.
I left a bunch of the default data set columns there in case it assists someone in troubleshooting other issues.
/* Written By Jason Davey C2Axis 8/10/18
Problem: Need metrics to determine who is completing dockets Solution: custom data set for events audit trail. To check if the professional completing the docket is the same as assigned to the docket, the audit trail professional is cross checked against all the assigned docket professionals. Thats the OwnDocket flag. 1 is yes, 0 is no.
data set is hard coded to only show the last 30 days. You can adjust this look back period in the where condition, but be careful. I find that indexes built on the audit tables don't work very well. On one large system, it took 6 seconds to return 5000 results. */
select case when a.AssignedEventProfs like '%'+a.Initials+'%' then 1 else 0 end as OwnDocket, a.*
from ( SELECT Audit.Audit, Audit.AuditDate, Audit.SearchAuditDate, Audit.AuditType, Professionals.Initials, (select stuff((select Initials + ' , ' from Events e2 left outer join EventProfs on EventProfs.ProfSet = e2.ProfSet left outer join Professionals p2 on EventProfs.Professionals = p2.Professionals WHERE e2.events = events.Events FOR XML PATH('')) ,1,0,'' )) as 'AssignedEventProfs', proftype, profclass, Professionals.ProfName, Audit.ColumnLabel, Audit.BeforeValue, Audit.AfterValue, CAST(Audit.BeforeValue AS Varchar(100)) AS Before, CAST(Audit.AfterValue AS Varchar(100)) AS After, Events.Notes, Audit.AuditEventDate, Audit.Matterid AS AuditMatterId, Audit.FullName as AuditFullName, Audit.CompSort as AuditCompSort, Audit.EventDesc AS AuditEventDesc, Events.EndDate, Events.DoneDate, Events.ShortNote, Matters.AreaOfLaw, Events.Place, Matters.ClientSort, Matters.ShortDesc, EventTypesDocket.Authority, Events.Location, Events.PleadingIndexNo, Events.PleadingIndexType, Events.PleadingFr, Events.PleadingStatus, Events.PleadingStatusDate, Events.PleadingTo, EventMatters.Matters, Events.EventsParent, Events.EventKind, Events.Events,
Events.Events AS Docket, Events.EventClass, Audit.AuditEventsNo, DockComment
FROM Audit LEFT OUTER JOIN Professionals on Audit.Professionals = Professionals.Professionals LEFT OUTER JOIN Events ON Audit.Atom = Events.Events LEFT OUTER JOIN EventMatters ON Events.Events = EventMatters.Events LEFT OUTER JOIN Matters ON EventMatters.Matters = Matters.Matters LEFT OUTER JOIN EventTypes ON Events.EventTypes = EventTypes.EventTypes LEFT OUTER JOIN EventTypesDocket ON EventTypes.EventTypes = EventTypesDocket.EventTypes WHERE ((AUDIT.COLUMNLABEL='DONEDATE' and audit.audittype = 'Edit' AND AUDITDATE>dateadd(day, -30, getdate()))) --and events.EventsNo = '909101' and isnull(AfterValue,'') <> '' and EventTypes.EventKind = 'D' and initials IS NOT NULL ) a
