Livereports - Workflow Assignments and Initiators

Hi,Does anyone have a Livereport which not only shows workflow assignments, user name & Due dates but also includes who the initiator was for the workflow which has generated the assignment? Any suggestions would be appreciated.

Find more posts tagged with

Comments

Eric_Saavedra

Message from Eric Saavedra <esaavedr@opentext.com> via eLink

eLink

You can use the following as a startingpoint (written in Oracle):

SELECT T3.SUBWORK_TITLE"Workflow Title",
       T5.FIRSTNAME || ' ' || T5.LASTNAME AS "Intiator",
       T2.FIRSTNAME || ' ' || T2.LASTNAME AS "Performer",
       T1.SUBWORKTASK_TITLE "Task",
       T1.SUBWORKTASK_DATEDUE_MAX "Due Date"
FROM   WSUBWORKTASK T1
       INNER JOIN KUAF T2 ON T1.SUBWORKTASK_PERFORMERID= T2.ID
       INNER JOIN WSUBWORK T3 ON T1.SUBWORKTASK_SUBWORKID= T3.SUBWORK_SUBWORKID
       INNER JOIN WWORK T4 ON T1.SUBWORKTASK_WORKID= T4.WORK_WORKID
       INNER JOIN KUAF T5 ON T4.WORK_OWNERID = T5.ID
ORDER BY SUBWORKTASK_WORKID, SUBWORKTASK_DATEREADY,SUBWORKTASK_DATEDUE_MAX

The challenge you will have is there is noinherent way to determine order of steps. Task IDs are assigned as they areplaced on the map, so you cannot realistically order by Task ID. If you labelyour step names with the step number as the first part (not recommended) thenyou could sort on task title. I have arbitrarily chosen to sort by Date Readyand then Due Date, which will get a reasonable sort about 60% of the time.

Also note that I do not filter the rows returned,so any step that has a performer (including Item Handler steps or some XML WFExtensions steps) will be included in the results. You can filter these easilyif you use specific accounts for your Item Handler tasks (like an accountcalled 'WF Agent').

One suggestion if you want to create exactreports is to create an extra table that will store either the Task ID or Task Namealong with a sort code (you can create a Livelink Form to manage this table)that you can then join to in the query above and sort your results in theproper order.

From: eLinkDiscussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, June 26, 2007 4:33AM
To: eLink Recipient
Subject: Livereports - WorkflowAssignments and Initiators

Livereports - Workflow Assignments and Initiators

Posted by axasunuser4 (Smith, Nick) on 06/26/2007 07:05 AM

Hi,

Does anyone have a Livereport which not only shows workflow assignments, user name & Due dates but also includes who the initiator was for the workflow which has generated the assignment? Any suggestions would be appreciated.

Nicholas_Smith

Thanks Eric.I am using Livelink 9.5 and SQL server. Would you have the SQL in this format rather than Oracle? I have tried using the report but it simply errors.Dave

Eric_Saavedra

Message from Eric Saavedra <esaavedr@opentext.com> via eLink

eLink

Just change the concatenation pipes toplus signs in lines 2 and 3:

SELECT T3.SUBWORK_TITLE "Workflow Title",
       T5.FIRSTNAME + ' ' + T5.LASTNAME AS "Intiator",
       T2.FIRSTNAME + ' ' + T2.LASTNAME AS "Performer",
       T1.SUBWORKTASK_TITLE "Task",
       T1.SUBWORKTASK_DATEDUE_MAX "Due Date"
FROM   WSUBWORKTASK T1
       INNER JOIN KUAF T2 ON T1.SUBWORKTASK_PERFORMERID = T2.ID
       INNER JOIN WSUBWORK T3 ON T1.SUBWORKTASK_SUBWORKID = T3.SUBWORK_SUBWORKID
       INNER JOIN WWORK T4 ON T1.SUBWORKTASK_WORKID = T4.WORK_WORKID
       INNER JOIN KUAF T5 ON T4.WORK_OWNERID = T5.ID
ORDER BY SUBWORKTASK_WORKID, SUBWORKTASK_DATEREADY, SUBWORKTASK_DATEDUE_MAX

From: eLinkDiscussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, June 27, 20077:44 AM
To: eLink Recipient
Subject: Livereports - WorkflowAssignments and Initiators

Livereports - Workflow Assignments and Initiators		Posted by axasunuser4 (Smith, Nick) on 06/27/2007 10:38 AM
In reply to: RE Livereports - Workflow Assignments and Initiators		Posted by esaavedr (Saavedra, Eric) on 06/26/2007 11:30 AM

Thanks Eric.

I am using Livelink 9.5 and SQL server. Would you have the SQL in this format rather than Oracle? I have tried using the report but it simply errors.

Dave

Nicholas_Smith

Thanks for coming back so quick. I have tried the amended SQL and get the following error message:Livelink Error: Error processing request. [[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'BY'. - SELECT T3.SUBWORK_TITLE "Workflow Title", T5.FIRSTNAME + ' ' + T5.LASTNAME AS "Intiator", T2.FIRSTNAME + ' ' + T2.LASTNAME AS "Performer", T1.SUBWORKTASK_TITLE "Task", T1.SUBWORKTASK_DATEDUE_MAX "Due Date"FROM WSUBWORKTASK T1 INNER JOIN KUAF T2 ON T1.SUBWORKTASK_PERFORMERID = T2.ID INNER JOIN WSUBWORK T3 ON T1.SUBWORKTASK_SUBWORKID = T3.SUBWORK_SUBWORKID INNER JOIN WWORK T4 ON T1.SUBWORKTASK_WORKID = T4.WORK_WORKID INNER JOIN KUAF T5 ON T4.WORK_OWNERID = T5.IDORDER BY SUBWORKTASK_WORKID, SUBWORKTASK_DATEREADY, SUBWORKTASK_DATEDUE_MAX]

Eric_Saavedra

Message from Eric Saavedra <esaavedr@opentext.com> via eLink

eLink

You need to add a space between T5.ID andORDER BY in your sql.

From: eLinkDiscussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, June 27, 20078:01 AM
To: eLink Recipient
Subject: Livereports - WorkflowAssignments and Initiators

Livereports - Workflow Assignments and Initiators		Posted by axasunuser4 (Smith, Nick) on 06/27/2007 10:59 AM
In reply to: RE Livereports - Workflow Assignments and Initiators		Posted by esaavedr (Saavedra, Eric) on 06/27/2007 10:54 AM

Thanks for coming back so quick. I have tried the amended SQL and get the following error message:

Livelink Error: Error processing request.
[[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'BY'. - SELECT T3.SUBWORK_TITLE "Workflow Title", T5.FIRSTNAME + ' ' + T5.LASTNAME AS "Intiator", T2.FIRSTNAME + ' ' + T2.LASTNAME AS "Performer", T1.SUBWORKTASK_TITLE "Task", T1.SUBWORKTASK_DATEDUE_MAX "Due Date"FROM WSUBWORKTASK T1 INNER JOIN KUAF T2 ON T1.SUBWORKTASK_PERFORMERID = T2.ID INNER JOIN WSUBWORK T3 ON T1.SUBWORKTASK_SUBWORKID = T3.SUBWORK_SUBWORKID INNER JOIN WWORK T4 ON T1.SUBWORKTASK_WORKID = T4.WORK_WORKID INNER JOIN KUAF T5 ON T4.WORK_OWNERID = T5.IDORDER BY SUBWORKTASK_WORKID, SUBWORKTASK_DATEREADY, SUBWORKTASK_DATEDUE_MAX]

Nicholas_Smith

Thanks Eric,The report is now working.It does bring up though a lot of old workflow assignments/tasks. Is this because it is looking at completed assignments? I only need it to look at incomplete assignments. Is that possible?Sorry, i promise i will stop asking for changes if you are able to do this.Dave

Eric_Saavedra

Message from Eric Saavedra <esaavedr@opentext.com> via eLink

eLink

Add the following to you WHERE clause:

ANDT4.WORK_STATUS = 2

or if you want to get executing andsuspended workflows (since suspended can be considered active workflows, justpaused):

ANDT4.WORK_STATUS IN (1,2)

Your query should then filter outcompleted workflows.

From: eLinkDiscussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, June 28, 2007 5:14AM
To: eLink Recipient
Subject: Livereports - WorkflowAssignments and Initiators

Livereports - Workflow Assignments and Initiators		Posted by axasunuser4 (Smith, Nick) on 06/28/2007 07:51 AM
In reply to: RE Livereports - Workflow Assignments and Initiators		Posted by esaavedr (Saavedra, Eric) on 06/27/2007 12:08 PM

Thanks Eric,

The report is now working.

It does bring up though a lot of old workflow assignments/tasks. Is this because it is looking at completed assignments? I only need it to look at incomplete assignments. Is that possible?

Sorry, i promise i will stop asking for changes if you are able to do this.

Dave

Nicholas_Smith

One more query im afraid.I have amended as per your suggestion to the following SQL but it still keeps bringing old workflows dating back to 2005. I will look into it more tomorrow but in initial inspection it could be that for the workflows in question it does not hold a workflow completed date. Would you be able to check the SQL below to see if I have amended it correctly. Could you add into the report a paramater to bring up active assignemts outstanding from say April?Here is the SQL:SELECT T3.SUBWORK_TITLE "Workflow Title", T5.FIRSTNAME + ' ' + T5.LASTNAME AS "Intiator", T2.FIRSTNAME + ' ' + T2.LASTNAME AS "Performer", T1.SUBWORKTASK_TITLE "Task", T1.SUBWORKTASK_DATEDUE_MAX "Due Date"FROM WSUBWORKTASK T1 INNER JOIN KUAF T2 ON T1.SUBWORKTASK_PERFORMERID = T2.ID INNER JOIN WSUBWORK T3 ON T1.SUBWORKTASK_SUBWORKID = T3.SUBWORK_SUBWORKID INNER JOIN WWORK T4 ON T1.SUBWORKTASK_WORKID = T4.WORK_WORKID INNER JOIN KUAF T5 ON T4.WORK_OWNERID = T5.ID where T4.WORK_STATUS = 2 ORDER BY SUBWORKTASK_WORKID, SUBWORKTASK_DATEREADY, SUBWORKTASK_DATEDUE_MAXThanksDave

Eric_Saavedra

Message from Eric Saavedra <esaavedr@opentext.com> via eLink

eLink

Try this:

SELECT T3.SUBWORK_TITLE "Workflow Title",
      T5.FIRSTNAME + ' ' + T5.LASTNAME AS "Intiator",
      T2.FIRSTNAME + ' ' + T2.LASTNAME AS "Performer",
      T1.SUBWORKTASK_TITLE "Task",
      T1.SUBWORKTASK_DATEDUE_MAX "Due Date"
FROM WSUBWORKTASK T1
      INNER JOIN KUAF T2 ON T1.SUBWORKTASK_PERFORMERID = T2.ID
      INNER JOIN WSUBWORK T3 ON T1.SUBWORKTASK_SUBWORKID = T3.SUBWORK_SUBWORKID
      INNER JOIN WWORK T4 ON T1.SUBWORKTASK_WORKID = T4.WORK_WORKID
      INNER JOIN KUAF T5 ON T4.WORK_OWNERID = T5.ID
WHERE T4.WORK_STATUS = 2
AND (T1.SUBWORKTASK_DATEREADY > %1 AND T1.SUBWORKTASK_DATEDONE IS NULL)
ORDER BY SUBWORKTASK_WORKID, SUBWORKTASK_DATEREADY, SUBWORKTASK_DATEDUE_MAX

To test in Query Analyzer replace the %1with a date like '4/1/2007'

From: eLinkDiscussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, June 28, 2007 9:11AM
To: eLink Recipient
Subject: Livereports - WorkflowAssignments and Initiators

Livereports - Workflow Assignments and Initiators		Posted by axasunuser4 (Smith, Nick) on 06/28/2007 12:08 PM
In reply to: RE Livereports - Workflow Assignments and Initiators		Posted by esaavedr (Saavedra, Eric) on 06/28/2007 10:04 AM

One more query im afraid.

I have amended as per your suggestion to the following SQL but it still keeps bringing old workflows dating back to 2005. I will look into it more tomorrow but in initial inspection it could be that for the workflows in question it does not hold a workflow completed date.

Would you be able to check the SQL below to see if I have amended it correctly. Could you add into the report a paramater to bring up active assignemts outstanding from say April?

Here is the SQL:
SELECT T3.SUBWORK_TITLE "Workflow Title", T5.FIRSTNAME + ' ' + T5.LASTNAME AS "Intiator", T2.FIRSTNAME + ' ' + T2.LASTNAME AS "Performer", T1.SUBWORKTASK_TITLE "Task", T1.SUBWORKTASK_DATEDUE_MAX "Due Date"FROM WSUBWORKTASK T1 INNER JOIN KUAF T2 ON T1.SUBWORKTASK_PERFORMERID = T2.ID INNER JOIN WSUBWORK T3 ON T1.SUBWORKTASK_SUBWORKID = T3.SUBWORK_SUBWORKID INNER JOIN WWORK T4 ON T1.SUBWORKTASK_WORKID = T4.WORK_WORKID INNER JOIN KUAF T5 ON T4.WORK_OWNERID = T5.ID where T4.WORK_STATUS = 2 ORDER BY SUBWORKTASK_WORKID, SUBWORKTASK_DATEREADY, SUBWORKTASK_DATEDUE_MAX

Thanks
Dave

Nicholas_Smith

Thanks Eric,It is now coming up with an error. It looks to be from the WHERE command line but my SQL is not very good so i am struglling to fix it. Here is the error:Livelink Error: Error processing request. [[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error - SELECT T3.SUBWORK_TITLE "Workflow Title", T5.FIRSTNAME + ' ' + T5.LASTNAME AS "Intiator", T2.FIRSTNAME + ' ' + T2.LASTNAME AS "Performer", T1.SUBWORKTASK_TITLE "Task", T1.SUBWORKTASK_DATEDUE_MAX "Due Date"FROM WSUBWORKTASK T1 INNER JOIN KUAF T2 ON T1.SUBWORKTASK_PERFORMERID = T2.ID INNER JOIN WSUBWORK T3 ON T1.SUBWORKTASK_SUBWORKID = T3.SUBWORK_SUBWORKID INNER JOIN WWORK T4 ON T1.SUBWORKTASK_WORKID = T4.WORK_WORKID INNER JOIN KUAF T5 ON T4.WORK_OWNERID = T5.ID WHERE T4.WORK_STATUS = 2 AND (T1.SUBWORKTASK_DATEREADY > ? AND T1.SUBWORKTASK_DATEDONE IS NULL)ORDER BY SUBWORKTASK_WORKID, SUBWORKTASK_DATEREADY, SUBWORKTASK_DATEDUE_MAX]Could you have a look at this for me?Thanks

Nicholas_Smith

Hi Eric,I have got the report working (i did not set a paramet) but it is not picking up on all live workflow tasks! Thanks again for your help so far.There are atleast 3 users which have live assignemts that do not come up on the report. I have checked the record limit and this has not been readched. I do however have a report from the "ToDo" table and this does bring them up.The SQL i have for the todo report is:select subwork_title "workflow",subworktask_title "task",subworktask_performerID_name "assignewd To",subworktask_datedue_max "Due Date"from todo WHERE subworktask_datedue_max>%1Rather than trying to fix the report would it be easier to amend the above to show intiator?ThanksDave

Eric_Saavedra

Message from Eric Saavedra <esaavedr@opentext.com> via eLink

eLink

If that fits your requirements, then yes!I would definitely recommend creating a new LiveReport based on the existing ToDo report. It is much easier (and quicker) to extend something that alreadyworks rather than writing something from scratch! Good idea, Dave.

From: eLinkDiscussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, June 29, 2007 2:31AM
To: eLink Recipient
Subject: Livereports - WorkflowAssignments and Initiators

Livereports - Workflow Assignments and Initiators		Posted by axasunuser4 (Smith, Nick) on 06/29/2007 05:27 AM
In reply to: Livereports - Workflow Assignments and Initiators		Posted by axasunuser4 (Smith, Nick) on 06/29/2007 03:43 AM

Hi Eric,

I have got the report working (i did not set a paramet) but it is not picking up on all live workflow tasks!

Thanks again for your help so far.

There are atleast 3 users which have live assignemts that do not come up on the report. I have checked the record limit and this has not been readched. I do however have a report from the "ToDo" table and this does bring them up.

The SQL i have for the todo report is:
select subwork_title "workflow",subworktask_title "task",subworktask_performerID_name "assignewd To",subworktask_datedue_max "Due Date"from todo WHERE subworktask_datedue_max>%1

Rather than trying to fix the report would it be easier to amend the above to show intiator?

Thanks
Dave

Nicholas_Smith

Thanks Eric,One final questiion. I have not been able to work out how i link back from the Todo table to identify and produce a report which includes the initiator. The SQL i have purely for selecting the basic data is:select subwork_title "workflow",subworktask_title "task",subworktask_performerID_name "assigned To", subworktask_datedue_max "Due Date" from todoWould you be able advise me of the SQL? My objective is to end up with a report which shows me all outstanding assignments, Due Date, Assigned to, Initiator and when the step was assigned.Have a good weekend.Dave

Jeff_Brown_(G*_ECM_Champ)

This will get you part of the way there. It should be easy to modify this to include the info you are looking for:SELECT KUAF.Name, KUAF.firstname, KUAF.lastname, subworktask_dateready,subworktask_title FROM WSubWorkTask, WebActiveWork A, KUAF WHERE A.Work_Status = 2 AND WSubWorkTask.SubWorkTask_WorkID = A.Work_WorkID AND WSubWorkTask.SubWorkTask_SubWorkID = A.SubWork_SubWorkID AND WSubWorkTask.SubWorkTask_Status = 2 and WSubWorkTask.SubWorkTask_PerformerID = KUAF.ID group by KUAF.name, KUAF.firstname, KUAF.lastname,subworktask_dateready,subworktask_title

Eric_Saavedra

Message from Eric Saavedra <esaavedr@opentext.com> via eLink

eLink

If you review the ToDo view you will seeall the information you want is listed there, you just need to add the columnsin your SELECT clause. Here is an example:

SELECT SubWork_Title"Workflow",
      Work_OwnerID_Name "Initiator",
      SubWorkTask_Title "Task",
      SubWorkTask_PerformerID_name "AssignedTo",
      SubWorkTask_DateReady "Assigned on",
      SubWorkTask_DateDue_Max "Due Date"
FROM ToDo

Note that you will be getting the login name as apposed tothe user's full name. But you can adjust the query by joining to the KUAF tableon the Work_OwnerID and SubWorkTask_PerformerID fields from the ToDo view(examples of that in the previous posting, or you can look at the source of theview to see how you connect to the KUAF table). Hope this helps.

From: eLinkDiscussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, June 29, 2007 8:16AM
To: eLink Recipient
Subject: Livereports - WorkflowAssignments and Initiators

Livereports - Workflow Assignments and Initiators		Posted by axasunuser4 (Smith, Nick) on 06/29/2007 11:04 AM
In reply to: RE Livereports - Workflow Assignments and Initiators		Posted by esaavedr (Saavedra, Eric) on 06/29/2007 10:37 AM

Thanks Eric,

One final questiion. I have not been able to work out how i link back from the Todo table to identify and produce a report which includes the initiator.

The SQL i have purely for selecting the basic data is:

select subwork_title "workflow",subworktask_title "task",subworktask_performerID_name "assigned To", subworktask_datedue_max "Due Date" from todo

Would you be able advise me of the SQL?

My objective is to end up with a report which shows me all outstanding assignments, Due Date, Assigned to, Initiator and when the step was assigned.

Have a good weekend.
Dave