Workflow Status Reports
Writing LiveReports for workflow can be difficult, and there are not many useful "canned" reports for workflow. We developed the following report, which links to a sub-report, and we think they are worth sharing. The first report lists all active workflows for specified workflow maps. You use the map Node ID to get a listing for the maps you want in your status report. When you click the "Details" link you get status on active and completed steps for that workflow instance. The List Report gives overdue status for the overall workflow, and the Details Report gives overdue status for the individual steps. These reports were written for Livelink 9.1 running on Oracle 9. ============================================================Workflow List============================================================SELECT DISTINCT subwork_title "TITLE", TO_CHAR(work_dateinitiated , 'MM/DD/YYYY') "INITIATED", DECODE (kuaf.type, 0, kuaf.lastname || ', ' || kuaf.firstname, 1, kuaf.name) "INITIATED BY", TO_CHAR(Work_DateDue_Max, 'MM/DD/YYYY') "DUE DATE", (SYSDATE-Work_DateInitiated+1) "DAYS DURATION", DECODE ((SELECT '*' FROM WWork w1 WHERE WWork.Work_DateDue_Max < SYSDATE AND w1.Work_WorkID = WWork.Work_WorkID), '*', 'Overdue', NULL, 'No') AS "LATE", (select (kuaf.lastname || ', ' || kuaf.firstname) from WFAttrData, kuaf WHERE WF_AttrID=12 AND WF_ID=Work_WorkID and WF_EntryNum=1 and kuaf.ID=WF_ValInt) as "PROJECT LEAD", DECODE (work_status,2,'Executing',1,'Suspended',-2,'Stopped') "WORKFLOW STATUS", Work_WorkID "Workflow ID" FROM wwork, kuaf, wsubworktask, wworkaudit, wsubwork, dtree, wmap WHERE dtree.dataid=wmap.map_mapobjid AND dataid IN('110027', '977341', '1867754', '1255850', '1257044') AND subwork_mapid=map_mapid AND kuaf.id=work_ownerid AND work_workid=subwork_workid AND work_workid=workaudit_workid AND work_workid=subworktask_workid AND (subworktask_status=2 or subworktask_status=3 or subworktask_status=4 or subworktask_status=5) AND workaudit_status=1 AND NOT work_status<=-1 AND NOT subworktask_status<=1 ORDER BY 'INITIATED', 'PROJECT LEAD'Sub-Report Param 1: Workflow IDClick-Thru Sub-Report: (Set to Workflow Details Report)Report Type: AutoLiveReport*Note: The Project Lead column is from a USER Attribute and is set by the initiator. To use this column, you must have a USER Attribute on the Attributes Tab of your map, and you must set the WF_AttrID value to match the ID of your Attribute. (To see the ID number, go into the attributes tab of your map and place your cursor over the attribute name, then read the number in the status bar of your browser) If you don't need / want the Project Lead column, simply delete that portion of the SQL statement. ===========================================================Workflow Details===========================================================SELECT subwork_title "TITLE", TO_CHAR(work_dateinitiated , 'MM/DD/YYYY') "INITIATED", subworktask_title "TASK NAME", TO_CHAR(subworktask_dateready, 'MM/DD/YYYY') "DATE RECEIVED", TO_CHAR(subworktask_DateDue_Max, 'MM/DD/YYYY') "DUE DATE", DECODE ((SELECT '*' FROM WSubWorkTask w1 WHERE WSubWorkTask.SubWorkTask_DateDue_Max < SYSDATE AND (WSubWorkTask.SubWorkTask_DateDone > WSubWorkTask.SubWorkTask_DateDue_Max OR WSubWorkTask.SubWorkTask_DateDone is Null) AND w1.SubWorkTask_TaskID = WSubWorkTask.SubWorkTask_TaskID AND w1.SubWorkTask_WorkID = WSubWorkTask.SubWorkTask_WorkID AND w1.SubWorkTask_PerformerID = WSubWorkTask.SubWorkTask_PerformerID), '*', 'Overdue', NULL, 'No') AS "LATE", DECODE (kuaf.type, 0, kuaf.lastname || ', ' || kuaf.firstname, 1, kuaf.name) "PERFORMER NAME", DECODE (work_status,2,'Executing',1,'Suspended',-2,'Stopped') "WORKFLOW STATUS", DECODE (subworktask_status,-1,'Done',2,'Ready',3,'Started',4,'Suspended',5,'Executing') "TASK STATUS", DECODE (TO_CHAR(subworktask_DateDone, 'MM/DD/YYYY'),NULL,'Pending') "DATE DONE" FROM wwork, kuaf, wsubworktask, wworkaudit, wsubwork, dtree, wmap WHERE dtree.dataid=wmap.map_mapobjid AND subwork_mapid=map_mapid AND kuaf.id=subworktask_performerid AND Work_workid=%1 AND work_workid=subwork_workid AND work_workid=workaudit_workid AND work_workid=subworktask_workid AND workaudit_status=1 AND NOT work_status<=-1 AND NOT subworktask_status=1 AND NOT subworktask_status<=-2 ORDER BY 'DATE RECEIVED'%1=User Input 1 User Input Type: String Prompt: Workflow ID#Report Type: AutoLiveReportAdditional Notes:To use these reports, create a new LiveReport and copy the appropriate SQL statement into the SQL block. For the Workflow List Report, be sure to put the Node ID's for your maps into the dataid IN('110027', '977341', '1867754', '1255850', '1257044') line, replacing the numbers here with your own, following the format shown here. Also, if you use the "PROJECT LEAD" column, be sure to set the WF_AttrID value to match the ID of your own USER Attribute.---If you have any ideas for expansion / improvement, please post a response.
