How to Create GP Workflow 2.0 Custom Reporting

Recently, I completed a GP 2015 R2 upgrade with Workflow setup for vendor and check batch approval. Microsoft has made changes to the Workflow to move it off SharePoint and the old Workflow version’s reporting capabilities could only be accessed through the Workflow web site. Knowing that the client was subjected to audit reviews on a quarterly basis, I needed to come up with a way for them to produce reports on both current/open and historical/approved workflows. I created the following SQL view in their company database and then in GP used SmartList Designer to create a SmartList based on the view.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE VIEW dbo.vw_WorkFlow_Status

AS

WITH CTE_FINAL (WorkflowInstanceID, Workflow_Name, Workflow_Step_Name, Approver, Workflow_Action, Completion_Date, Completion_Time, Comments )

AS

(select d.WorkflowInstanceID,

d.Workflow_Name,

d.Workflow_Step_Name,

CASE WHEN a.ADDisplayName is null THEN ”

ELSE a.ADDisplayName

END as [Assigned Approver],

CASE WHEN d.Workflow_Action = 1 THEN ‘Submit’

WHEN d.Workflow_Action = 2 THEN ‘Resubmit’

WHEN d.Workflow_Action = 3 THEN ‘Approve’

WHEN d.Workflow_Action = 4 THEN ‘Task Complete’

WHEN d.Workflow_Action = 5 THEN ‘Reject’

WHEN d.Workflow_Action = 6 THEN ‘Delegate’

WHEN d.Workflow_Action = 7 THEN ‘Recall’

WHEN d.Workflow_Action = 8 THEN ‘Escalate’

WHEN d.Workflow_Action = 9 THEN ‘Edit’

ELSE ‘Final Approve’

END as Workflow_Action,

convert(varchar(10),d.Workflow_Completion_Date, 101) as [Completion_Date],

right(‘0’+LTRIM(right(convert(varchar,d.Workflow_Completion_Time,100),8)),7) as Completion_Time,

d.Workflow_Comments

from dbo.WF30100 d

LEFT JOIN WF40200 a ON d.Workflow_Step_Assign_To = a.UsersListGuid

WHERE d.Workflow_Action = 10)

Select –c.WorkflowInstanceID,

c.Workflow_Name,

c.Workflow_Type_Name,

c.Workflow_Originator,

c.WfBusObjKey as Approval_Request,

CASE WHEN c.Workflow_Status = 1 THEN ‘Not Submitted’

WHEN c.Workflow_Status = 2 THEN ‘Submitted’

WHEN c.Workflow_Status = 3 THEN ‘No Action Needed’

WHEN c.Workflow_Status = 4 THEN ‘Pending User Action’

WHEN c.Workflow_Status = 5 THEN ‘Recalled’

WHEN c.Workflow_Status = 6 THEN ‘Completed’

WHEN c.Workflow_Status = 7 THEN ‘Rejected’

WHEN c.Workflow_Status = 8 THEN ‘Workflow Ended’

WHEN c.Workflow_Status = 9 THEN ‘Not Activated’

ELSE ‘Deactivated’

END as Workflow_Status,

CASE WHEN d.Approver is null THEN ”

ELSE d.Approver

END as Approver,

CASE WHEN d.Completion_Date is null THEN ”

ELSE d.Completion_Date

END as Completion_Date,

CASE WHEN d.Completion_Time is null THEN ”

ELSE d.Completion_Time

END as Completion_Time,

CASE WHEN d.Comments is null THEN ”

ELSE d.Comments

END as Comments

FROM dbo.WFI10002 c

LEFT JOIN CTE_FINAL d ON D.WorkflowInstanceID = c.WorkflowInstanceID

GO

Grant Select on dbo.vw_WorkFlow_Status to DYNGRP

Once your done adding the SQL view you can create a SmartList report with SmartList Designer and the complete SmartList should look like the screen shot below.

SmartList Workflow Report

Advertisements