Workflow LiveReport - Approved Workflow Count based on Multiple Aorkflows

We have a Document Approval Workflow. The SQL below is for a LiveReport that shows the approved document count per initiator. Is it possible to create a LiveReport to show the approved document count per initiator based on more than one Workflow? (Not a subWorkflows but separate Workflows with different MapObjIDs.)Thank you for your help. HiromiSelect K.Name "Initiator's Employee ID", Count (K.Name) "BKM Count" From WWork B, KUAF K, wfdispositions D, wsubwork S Where B.Work_WorkID in (Select S.SubWork_WorkID from WSubWork S, WMap M where M.Map_MapObjID=7335190 and S.SubWork_MapID=M.Map_MapID) AND B.Work_OwnerID=K.ID AND B.Work_Status=-3 AND D.wf_workflowid=S.subwork_workid AND S.subwork_workid= b.work_workid AND d.wf_taskid=3 AND D.wf_value='Approve' AND B.WORK_DATEINITIATED between %1 and %2 GROUP BY K.Name

Find more posts tagged with

Comments

Tim_Hunter

Hiromi, not as easy as you might hopeEvery workflow is going to have different Approve steps at different points within the workflow, which means the WF_TASKID will be different depending on where within the workflow the Approve task.You might be able to do something like this... SELECT K.Name "Initiator's Employee ID", COUNT (K.Name) "BKM Count" FROM WWork B , KUAF K , wfdispositions D, wsubwork S , wmap , dtree WHERE wmap.map_mapid = s.subwork_mapidAND wmap.map_mapobjid = dtree.dataidAND dtree.name LIKE '%Some%Test%To%Limit%To%Some%Name%'AND B.Work_OwnerID =K.ID --AND B.Work_Status =-3 (-3 is only archived, not sure if you want this)AND D.wf_workflowid =S.subwork_workidAND S.subwork_workid= b.work_workid --AND d.wf_taskid =3 (cant limit by this anymore, it could be any taskid)AND D.wf_value LIKE '%Approve%'AND B.WORK_DATEINITIATED BETWEEN %1 AND %2GROUP BY K.Name

Hiromi__Byrd_(tokeleuser4_-_(deleted))

AND B.Work_Status =-3 (-3 is only archived, not sure if you want this)>>>> Yes, it is only for the archived Workflow instances. AND d.wf_taskid =3 (cant limit by this anymore, it could be any taskid)>>>>I understand that the WF_TAskID will be different depending on the workflow map, so what can do for D.wf_taksid= ?I would like to create a LiveReport to count approvals from two or four different Wofkflows (workflow maps). Is it possible?AND dtree.name LIKE '%Some%Test%To%Limit%To%Some%Name%'>>>> What would be after in dtree.name LIKE '?Hiromi

Tim_Hunter

the wf_taskid is difficult, if you only have one approval step in each workflow and its named 'Approved' then you are probably ok just leaving this line commented out, the line "AND D.wf_value = 'Approved'" will take care of it. However if you have multiple approve steps in your workflow(s) this makes things more challenging and you may have to write different queries for each workflow and use UNION to combine them into a single statement.my example would of dtree.like will find a workflow that is named like that, I don't know how your workflows are named but I assume you want to retrieve only specific workflows.You also have an option of something like"and dtree.name in ('Workflow 1', 'Workflow 2', 'Another Workflow', 'My Workflow')again this is specific to your workflows, you can leave out the line completely if you want every workflow in your system

Jim_Coursey

Message from Coursey, Jim (AS) <jim.coursey@ngc.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

If you had multiple approvals, could you get all of them out on one query if you named them, for example, HR Approved, Accounting Approved, Engineering Approved, Production Approved, etc.?

Tim_Hunter

Sure using the followingAND D.wf_value like '%Approved%'But be aware you will have multiple rows for each workflow, 1 for each Approved step

Hiromi__Byrd_(tokeleuser4_-_(deleted))

Yes, there are two Approval steps in each of two Workflows. However, in the Approval count LiveReport, I only need to look at one of the two approval step (as that's the step the document is finally approved).If I ask the Workflow developer to one of the two steps "Approved TS" and the other "Approved LC" (naming each step differently), I can do "AND D.wf_value='Approved TS'. Don't you think?Hiromi

Tim_Hunter

Hiromi, that will work perfectly