Dashboard report for time in Workflow

[Michelle_Troxel]

I have a request to create a dashboard report to show 

1. Date document entered the workflow

2. Date document exited the workflow

Or time in workflow between #1 and #2

Or if still in the workflow, how long it's been there

Basically they want to know how long it's taking the users to get through these documents.

3. Volume

4. User names (not IDs)

5. Include one of the keywords off the document and the document name, etc.

I know we can use the Transition reporting option on the data provider. But using that, the report gives me usernumber, not name (which means nothing to my customers) and doesn't give me the other items. I'm pretty sure it's including all the service account activity for the timer too. So I think I need to do a custom sql query.

I tried to write a sql query off the wflog table. Struggle there is the documents go through multiple processes before getting to the queue where the users actually act on it. The service account is the entry account for document so I can't exclude that user. I can't figure out how to narrow down to just the final queue with the actions as sql is returning the initial and processing queue actions as well.

Does anyone have a query already built to show this? It seems to me this should be a standard dashboard report because people like data on productivity...but it was not included in our standard set. Reporting Dashboards are not my forte 😛 

thanks!

Michelle

[Larissa_Armand]

Michelle,

This should get you close at least: 

select L.ITEMNUM,
trim(SQ.STATENAME) as StartQueue, L.STATENUM as StartQueueNum, L.ENTRYTIME as QueueEntry, L.USERNUM as EntryUserNum, trim(EU.USERNAME) as EntryUser,
trim(DQ.STATENAME) as DestinationQueue, L.STATENUMTO as DestinationQueueNum, L.EXITTIME as QueueExit, L.EXITUSERNUM as ExitUserNum, trim(XU.USERNAME) as ExitUser
from LCSTATE SQ, USERACCOUNT EU, WFLOG L
left outer join USERACCOUNT XU on L.EXITUSERNUM = XU.USERNUM --join to users for exit user, if it exists
left outer join LCSTATE DQ on L.STATENUMTO = DQ.STATENUM --join to queues for destination queue, if it exists
where L.STATENUM = SQ.STATENUM --join to queues for start queue
and L.USERNUM = EU.USERNUM --join to users for entry user
and L.LCNUM = '###' --put the ID of the life cycle here
and L.STATENUM = '####' --put the ID of the queue here
order by 1
;

[James_Perry]

Larissa, thank you again for being a query superhero. Your snippet helped me create a query to check for days to completion as well as for items still in the life cycle.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDGODECLARE @LCNUM INT;SET @LCNUM = '131' --PUT THE ID OF THE LIFE CYCLE HEREDECLARE @QNUM INT;SET @QNUM = '163' --PUT THE ID OF THE QUEUE HEREDECLARE @THRESHOLD INT;SET @THRESHOLD = '0' --PUT THE MINIMUM NUMBER OF DAYS IN QUEUE TO CHECKSELECT	l.lcnum AS 'LC Number'	, RTRIM(LC.lifecyclename) AS 'Life Cycle'	, l.itemnum AS 'Log Item'	, RTRIM(ID.itemname) AS 'AutoName'	, RTRIM(sq.statename) AS 'Start Queue'	, l.statenum AS 'SQ Number'	, l.entrytime AS 'Entry Time'	, l.usernum AS 'Entry User Number'	, RTRIM(eu.username) AS 'Entry UserID'	, RTRIM(eu.realname) AS 'Entry User'	, RTRIM(dq.statename) AS 'Destination Queue'	, l.statenumto AS 'DQ Number'	, l.exittime AS 'Exit Time'	, l.exitusernum AS 'Exit User Number'	, RTRIM(xu.username) AS 'Exit UserID'	, RTRIM(xu.realname) AS 'Exit User'	, CASE WHEN (l.exittime) <> '1964-01-01 00:00:00.000' THEN DATEDIFF(day, l.entrytime, l.exittime) ELSE '-1' END AS 'Days In Queue'FROM hsi.lcstate sq	, hsi.useraccount eu	, hsi.wflog l	LEFT OUTER JOIN hsi.useraccount xu 		ON l.exitusernum = xu.usernum --JOIN TO USERS FOR EXIT USER IF IT EXISTS	LEFT OUTER JOIN hsi.lcstate dq 		ON l.statenumto = dq.statenum --JOIN TO QUEUES FOR DESTINATION QUEUE IF IT EXISTS	LEFT OUTER JOIN hsi.lifecycle LC		ON LC.lcnum = l.lcnum	LEFT OUTER JOIN hsi.itemdata ID		ON ID.itemnum = l.itemnumWHERE 	l.statenum = sq.statenum --JOIN TO QUEUES FOR START QUEUE	AND l.usernum = eu.usernum --JOIN TO USERS FOR ENTRY USER	AND l.lcnum = @LCNUM	AND l.statenum = @QNUM	--AND DATEDIFF(day, l.entrytime, l.exittime) > @THRESHOLD --USE TO SHOW ITEMS IN QUEUE LONGER THAN THRESHOLD	AND (l.exittime) = '1964-01-01 00:00:00.000' --USE TO SHOW ITEMS STILL IN QUEUEORDER BY 	17 DESC, 	3;

[Melissa_Foster]

@Jim Perry and @Larissa Armand , Thank you for taking the time to make and share the queries!

[Peter_Davies]

Those were helpful I just created a report for users based on the above