Help with following reports

Hello,I have a workflow which has subworkflows.Report 1 below lists all executing workflows:SELECT DISTINCT workRequests.status_of_work_request "Status of Work Request",subwork_title "TITLE",workRequests.version,workRequests.Addition "Edition",work_dateinitiated "INITIATED ON",CASE work_status WHEN 2 THEN 'Executing' WHEN 1 THEN 'Suspended' WHEN -2 THEN 'Stopped' WHEN -1 THEN 'Done' END "WORKFLOW STATUS",CASE kuaf.type WHEN 0 THEN kuaf.lastname + ', ' + kuaf.firstname WHEN 1 THEN kuaf.name END "Project Manager",workRequests.Date_Request_Received,workRequests.Date_Response_Required,workRequests.Actual_Response_Date,workRequests.Client_Response_Received_Date,Work_WorkID "Workflow ID" FROM wfattrdata,wwork, kuaf, wsubworktask, wworkaudit, wsubwork, dtree, wmap, workRequests WHERE dtree.dataid=wmap.map_mapobjid AND dtree.dataid IN('724685') AND subwork_mapid=map_mapid AND work_workid=subwork_workid AND work_workid = subwork_subworkid AND work_workid = subworktask_workid AND subworktask_subworkid = workRequests.VolumeID AND workRequests.VersionNum = -1 AND kuaf.id=workRequests.Assign_Project_Manager AND work_workid=workaudit_workid AND subworktask_subworkid <> 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 status_of_work_requestReport 2 is kicked off from Report 1 and displays the current ready tasks.SELECT subworktask_subworkid "Sub Workflow ID",subwork_title "TITLE",work_dateinitiated "INITIATED", subworktask_title "TASK NAME",subworktask_dateready "DATE RECEIVED",subworktask_DateDue_Max "DUE DATE", CASE WHEN EXISTS (SELECT '*' FROM WSubWorkTask w1 WHERE WSubWorkTask.SubWorkTask_DateDue_Max < GETDATE() 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)THEN 'Overdue' ELSE 'No' END AS "LATE", CASE kuaf.type WHEN 0 THEN kuaf.lastname + ', ' + kuaf.firstname WHEN 1 THEN kuaf.name END "PERFORMER NAME", CASE work_status WHEN 2 THEN 'Executing' WHEN 1 THEN 'Suspended' WHEN -2 THEN 'Stopped' END "WORKFLOW STATUS", CASE subworktask_status WHEN -1 THEN 'Done' WHEN 2 THEN 'Ready' WHEN 3 THEN 'Started' WHEN 4 THEN 'Suspended' WHEN 5 THEN 'Executing'END "TASK STATUS" 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 AND subwork_returnsubworkid = 0 AND subworktask_status <> -1 ORDER BY 'DATE RECEIVED'Notice that in Report 2 the subworkflow ID is displayed, what I want is to be able to display the subwokrkflow ID on Report 1 also..How do i do that?

Find more posts tagged with

Comments

Victoria_Freihofer_(rgsinc01admin_-_(deleted))

Message from Olson, Leonard <<A HREF="mailto:len.olson@rgsinc.com">len.olson@rgsinc.com> via eLink

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

Have you tried including subworktask_subworkid "Sub Workflow ID" in the select statement of report no. 1? --If you have, what sort of problem did you see?

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, January 21, 2004 11:23
To: eLink Recipient
Subject: Help with following reports

Help with following reports

Posted by DeloitteAdmin on 01/21/2004 11:20 PM

Hello,

I have a workflow which has subworkflows.

Report 1 below lists all executing workflows:

SELECT DISTINCT workRequests.status_of_work_request "Status of Work Request",subwork_title "TITLE",workRequests.version,workRequests.Addition "Edition",work_dateinitiated "INITIATED ON",CASE work_status WHEN 2 THEN 'Executing' WHEN 1 THEN 'Suspended' WHEN -2 THEN 'Stopped' WHEN -1 THEN 'Done' END "WORKFLOW STATUS",CASE kuaf.type WHEN 0 THEN kuaf.lastname + ', ' + kuaf.firstname WHEN 1 THEN kuaf.name END "Project Manager",workRequests.Date_Request_Received,workRequests.Date_Response_Required,workRequests.Actual_Response_Date,workRequests.Client_Response_Received_Date,Work_WorkID "Workflow ID" FROM wfattrdata,wwork, kuaf, wsubworktask, wworkaudit, wsubwork, dtree, wmap, workRequests WHERE dtree.dataid=wmap.map_mapobjid AND dtree.dataid IN('724685') AND subwork_mapid=map_mapid AND work_workid=subwork_workid AND work_workid = subwork_subworkid AND work_workid = subworktask_workid AND subworktask_subworkid = wo! rkRequests.VolumeID AND workRequests.VersionNum = -1 AND kuaf.id=workRequests.Assign_Project_Manager AND work_workid=workaudit_workid AND subworktask_subworkid <> 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 status_of_work_request

Report 2 is kicked off from Report 1 and displays the current ready tasks.

SELECT subworktask_subworkid "Sub Workflow ID",subwork_title "TITLE",work_dateinitiated "INITIATED", subworktask_title "TASK NAME",subworktask_dateready "DATE RECEIVED",subworktask_DateDue_Max "DUE DATE", CASE WHEN EXISTS (SELECT '*' FROM WSubWorkTask w1 WHERE WSubWorkTask.SubWorkTask_DateDue_Max < GETDATE() 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)THEN 'Overdue' ELSE 'No' END AS "LATE", CASE kuaf.type WHEN 0 THEN kuaf.lastname + ', ' + kuaf.firstname WHEN 1 THEN kuaf.name END "PERFORMER NAME", CASE work_status WHEN 2 THEN 'Executing' WHEN 1 THEN 'Suspended' WHEN -2 THEN 'Stopped' END "WORKFLOW STATUS", CASE subw! orktask_status WHEN -1 THEN 'Done' WHEN 2 THEN 'Ready' WHEN 3 THEN 'Started' WHEN 4 THEN 'Suspended' WHEN 5 THEN 'Executing'END "TASK STATUS" 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 AND subwork_returnsubworkid = 0 AND subworktask_status <> -1 ORDER BY 'DATE RECEIVED'

Notice that in Report 2 the subworkflow ID is displayed, what I want is to be able to display the subwokrkflow ID on Report 1 also..

How do i do that?

DeloitteAdmin_(Delete)_2707331

OK,putting subworktask_subworkid into the first report provides me with the info i want, but it duplicates one of the records..I don't know why it duplicates it..That's the only problem i get..

Victoria_Freihofer_(rgsinc01admin_-_(deleted))

Message from Olson, Leonard <<A HREF="mailto:len.olson@rgsinc.com">len.olson@rgsinc.com> via eLink

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

I use very similar reports (obviously without your form data) and have found that once you include id #s from WSubWork and / or WSubWorkTask that you will start getting mulitiple rows for each workflow instance. That's why there are two separate reports --the first gives you a list of all your active workflows, and the second provides the details. You could combine the reports, but then your results would be a little hard to read. I think you will need to choose whether to have the subworktask_subworkid in report 1 or to just have a report that only lists each workflow once.

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, January 22, 2004 5:54
To: eLink Recipient
Subject: get duplicate records

get duplicate records		Posted by DeloitteAdmin on 01/22/2004 05:54 PM
In reply to: RE Help with following reports		Posted by RGSINC01Admin (Olson, Len) on 01/22/2004 08:55 AM

OK,

putting subworktask_subworkid into the first report provides me with the info i want, but it duplicates one of the records..

I don't know why it duplicates it..

That's the only problem i get..

DeloitteAdmin_(Delete)_2707331

Hi Len,I would be happy to have a report that lists only the subworkflow ID's and the titles of each subworkflow..Is this possible?Asha

eLink User

Message from Eric Saavedra <<A HREF="mailto:esaavedr@opentext.com">esaavedr@opentext.com> via eLink

eLink

SELECT SubWork_SubWorkID, SubWork_TitleFROM WSubWork

WHERE SubWork_SubWorkID <>SubWork_WorkID

From: eLinkDiscussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, January 23, 20047:33 PM
To: eLink Recipient
Subject: can i list only subworkflow ID?

can i list only sub workflow ID?		Posted by DeloitteAdmin on 01/23/2004 10:28 PM
In reply to: RE get duplicate records		Posted by RGSINC01Admin (Olson, Len) on 01/23/2004 08:29 AM

Hi Len,

I would be happy to have a report that lists only the subworkflow ID's and the titles of each subworkflow..

Is this possible?

Asha