Help with a report...

Hello,Below is a report that was created by Len Olsen...I've modified this report for SQL and am trying to execute it..WHen I run the report i get No results found..I know this workflow is currently active..For the report below I found the Node ID of the map by going into the Workflow Status area and checking the number that appears in the openWorkflow() function whe I hover over the workflow link with my mouse.... Is that the correct way of getting the Node Id for the map?Can anyone see any problems with the report below?Thanks,AshaSELECT DISTINCT subwork_title "TITLE",work_dateinitiated, CASE kuaf.type WHEN 0 THEN kuaf.lastname + ', ' + kuaf.firstname WHEN 1 THEN kuaf.name END "INITIATED BY",Work_DateDue_Max, (GETDATE()-Work_DateInitiated+1) "DAYS DURATION", CASE (SELECT '*' FROM WWork w1 WHERE WWork.Work_DateDue_Max < GETDATE()AND w1.Work_WorkID = WWork.Work_WorkID) WHEN '*' THEN'Overdue' WHEN NULL THEN 'No' END AS "LATE", CASE work_status WHEN 2 THEN 'Executing' WHEN 1 THEN 'Suspended' WHEN -2 THEN 'Stopped'END "WORKFLOW STATUS", Work_WorkID "Workflow ID" FROM wwork, kuaf, wsubworktask, wworkaudit, wsubwork, dtree, wmap WHERE dtree.dataid=wmap.map_mapobjid AND dataid IN('709880') 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

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

I'm no expert on evaluating the CASE statements, but they look OK to me --though you might make sure you have spaces in your SQL where you need them. As for the Node ID of the map --you don't need to go to workflow status. Instead go to the actual workflow map and open it in either the paint function or general function (do not hover over the initiate link) and get the objId from the address bar of the browser. This will ensure you are getting the map ID and not the ID of an executing workflow instance.

Also I might consider doing without the CASE for the "INITIATED BY" column and write it like this:

kuaf.lastname + ', ' + kuaf.firstname "INITIATED BY",
The reason is that the CASE function determines whether to use a group name or an individual name, and since workflows are initiated by individuals you shouldn't need to check for a group name. --I apologize if my example included a check for group name; I often cut & paste SQL snippets when I know they work.

Also, does SQL use the double quotes around the column alias? Oracle requires the quotes when the alias contains a space, not sure if SQL formats the alias the same way...

Let us know if you get it working --it would be great to have both SQL and Oracle versions of these reports.

Len Olson

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, October 23, 2003 10:57
To: eLink Recipient

Help with a report...

Posted by DeloitteAdmin (Wayne, Amanda ) on 10/23/2003 10:52 PM

Hello,

Below is a report that was created by Len Olsen...

I've modified this report for SQL and am trying to execute it..

WHen I run the report i get No results found..

I know this workflow is currently active..

For the report below I found the Node ID of the map by going into the Workflow Status area and checking the number that appears in the openWorkflow() function whe I hover over the workflow link with my mouse....

Is that the correct way of getting the Node Id for the map?

Can anyone see any problems with the report below?

Thanks,

Asha

SELECT DISTINCT
subwork_title "TITLE",work_dateinitiated,
CASE kuaf.type WHEN 0 THEN kuaf.lastname + ', ' + kuaf.firstname WHEN 1 THEN kuaf.name END "INITIATED BY",Work_DateDue_Max, (GETDATE()-Work_DateInitiated+1) "DAYS DURATION", CASE (SELECT '*' FROM WWork w1 WHERE WWork.Work_DateDue_Max < GETDATE()AND w1.Work_WorkID = WWork.Work_WorkID) WHEN '*' THEN'Overdue' WHEN NULL THEN 'No' END AS "LATE",

CASE work_status WHEN 2 THEN 'Executing' WHEN 1 THEN 'Suspended' WHEN -2 THEN 'Stopped'END "WORKFLOW STATUS",
Work_WorkID "Workflow ID"
FROM
wwork, kuaf, wsubworktask, wworkaudit, wsubwork, dtree, wmap
WHERE
dtree.dataid=wmap.map_mapobjid
AND dataid IN('709880')
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

DeloitteAdmin_(Delete)_2707331

Hi Len,I managed to figure out what my problems were, there were a few probs with the syntax and also I was using the incorrect dataid!Below is the SQL version of the report you created to list workflows..Please have a look and let me know if thats ok, it seems to work for me..I've tried to modify the report you created to list the steps within each workflow, and I managed to get that working also...but the report that I get seems to double up on some tasks..I have a workflow which contains a sub-workflow, when I run that report it is listing a task from the main workflow, it then lists the same task again but the title is of the subworkflow?so I have a report that has a double of each task, with only the task title changing..I think it may have something to do with the WSubWork table? I have pasted both reports below i'm hoping you are able to provide me with some feedback..Thanks,Asha--- SQL Workflow instances report---SELECT DISTINCT subwork_title "TITLE",work_dateinitiated "INITIATED ON", CASE kuaf.type WHEN 0 THEN kuaf.lastname + ', ' + kuaf.firstname WHEN 1 THEN kuaf.name END "INITIATED BY",Work_DateDue_Max,(GETDATE()-Work_DateInitiated+1) "DAYS DURATION", CASE WHEN EXISTS (SELECT '*' FROM WWork w1 WHERE WWork.Work_DateDue_Max < GETDATE()AND w1.Work_WorkID = WWork.Work_WorkID) THEN 'Overdue' ELSE 'No' END AS "LATE", CASE work_status WHEN 2 THEN 'Executing' WHEN 1 THEN 'Suspended' WHEN -2 THEN 'Stopped'END "WORKFLOW STATUS", Work_WorkID "Workflow ID" FROM wwork, kuaf, wsubworktask, wworkaudit, wsubwork, dtree, wmap WHERE dtree.dataid=wmap.map_mapobjid AND dataid IN('532573') 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<=1ORDER BY 'INITIATED BY'---------SQL Workflow details report-----------------SELECT 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", CASE subworktask_DateDone WHEN NULL THEN 'Pending' END "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 SubWork_WorkID = Work_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'

DeloitteAdmin_(Delete)_2707331

Hello,I think I may have fixed the problem I was having with displaying steps and sub workflow steps within a workflow..I inserted the following line: SubWork_ReturnSubWorkID = 0 Into the second report to display workflow step details and I know no longer get duplicate values..I don't understand why this works and if it is the correct way to fix this problem, could someone please check this to see if it is correct?THe complete report is pasted below..Thanks,Asha------------------------------------------------------------SELECT 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", subworktask_DateDone AS "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 AND SubWork_ReturnSubWorkID = 0 ORDER BY 'DATE RECEIVED'

eLink User

Message from <<A HREF="mailto:Dave.Beliveau@sanmina-sci.com">Dave.Beliveau@sanmina-sci.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">RE: I think I have fixed the problem of duplication, can someone check my fix?

Hello,
I was able to get the reports to work except for the fact that the "Days Duration" field values are returned formatted as

"08/24/1900 10:16 AM". I expected the values to be formatted as a whole number rather than a date.
Does anyone know how to fix this?

Thanks,

Dave Beliveau
eBusiness - Team Connex
Sanmina-SCI
-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Sunday, October 26, 2003 11:59 PM
To: eLink Recipient
Subject: I think I have fixed the problem of duplication, can someone check my fix?

I think I have fixed the problem of duplication, can someone check my fix? Posted by DeloitteAdmin on 10/26/2003 11:56 PM

Hello,

I think I may have fixed the problem I was having with displaying steps and sub workflow steps within a workflow..

I inserted the following line: SubWork_ReturnSubWorkID = 0

Into the second report to display workflow step details and I know no longer get duplicate values..

I don't understand why this works and if it is the correct way to fix this problem, could someone please check this to see if it is correct?

THe complete report is pasted below..

Thanks,
Asha

------------------------------------------------------------
SELECT 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 ST! ATUS", subworktask_DateDone AS "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 AND SubWork_ReturnSubWorkID = 0 ORDER BY 'DATE RECEIVED'

[To reply to this thread, use your normal e-mail reply function.]

============================================================

Topic: Help with a report...
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=3245585&objAction=view

Discussion: Livelink LiveReports Discussion https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=view

Livelink Server: https://knowledge.opentext.com/knowledge/livelink.exe

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

The "Days Duration" field shows as a whole number in the Oracle version -you might check the use of the Get Date function for SQL and see if you need to convert to character or number when you make the calculation.

The WSubWork table causes duplicate listings for group steps and subflows because it creates an entry for each member on a group step / or for the steps in the subflow. Using the SubWork_ReturnSubWorkID = 0 line will limit the results to the steps launched by the parent workflow - this column is used to re-connect subflows to a parent when the subflow is conplete.

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, October 27, 2003 8:15
To: eLink Recipient

RE I think I have fixed the problem of duplication, can someone check my fix?		Posted by eLink on 10/27/2003 08:14 AM
In reply to: I think I have fixed the problem of duplication, can someone check my fix?		Posted by DeloitteAdmin on 10/26/2003 11:56 PM

Message from <Dave.Beliveau@sanmina-sci.com> via eLink

Hello,
I was able to get the reports to work except for the fact that the "Days Duration" field values are returned formatted as "08/24/1900 10:16 AM". I expected the values to be formatted as a whole number rather than a date.
Does anyone know how to fix this? Thanks,
Dave Beliveau
eBusiness - Team Connex
Sanmina-SCI
-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Sunday, October 26, 2003 11:59 PM
To: eLink Recipient
Subject: I think I have fixed the problem of duplication, can someone check my fix?
I think I have fixed the problem of duplication, can someone check my fix? Posted by DeloitteAdmin on 10/26/2003 11:56 PM Hello, I think I may have fixed the problem I was having with displaying steps and sub workflow steps within a workflow.. I inserted the following line: SubWork_ReturnSubWorkID = 0 Into the second report to display workflow step details and I know no longer get duplicate values.. I don't understand why this works and if it is the correct way to fix this problem, could someone please check this to see if it is correct? THe complete report is pasted below.. Thanks,
Asha ------------------------------------------------------------
SELECT 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 ST! ATUS", subworktask_DateDone AS "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 AND SubWork_ReturnSubWorkID = 0 ORDER BY 'DATE RECEIVED' [To reply to this thread, use your normal e-mail reply function.] ============================================================ Topic: Help with a report...
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=3245585&objAction=view Discussion: Livelink LiveReports Discussion https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=view Livelink Server: https://knowledge.opentext.com/knowledge/livelink.exe

eLink User

Message from <<A HREF="mailto:Dave.Beliveau@sanmina-sci.com">Dave.Beliveau@sanmina-sci.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">RE: RE RE I think I have fixed the problem of duplication, can someone check my fix?

Thanks for the tip, I got it to work in MSSQL by changing the "Days Duration" section of the statement to read
datediff(Day, Work_DateInitiated+1, GETDATE()) "DAYS DURATION",

Dave Beliveau
eBusiness - Team Connex
Sanmina-SCI
12A Manor Parkway

DeloitteAdmin_(Delete)_2707331

Ok, so you said that setting SubWork_ReturnSubWorkID = 0 will return steps launched by the Parent Workflow..I have a parent workflow with steps A,B,CWhere step B is a subworkflow which has steps 1,2,3when I run this report I get the following result:A, 1,2,3 C Now that is exactly what I want, but i'm a little confused by your statement that setting SubWork_ReturnSubWorkID = 0 will return steps launched by the Parent Workflow, because steps 1,2,3 are withing subworkflow B... I thought that it is subworkflow B that launches steps 1,2,3 and not the parent workflow?So following that thought how come my report displays A,1,2,3,C when 1,2,3 are within subworkflow B?Hope someone can explain that?Thanks,Asha