Recently, I completed a GP 2015 R2 upgrade with Workflow setup for General Ledger, vendor and check batch approval. Microsoft has made changes to the Workflow to move it off SharePoint; the old Workflow version’s reporting capabilities could only be accessed through the Workflow website. 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 done the SmartList should look like the screen shot below.
[avatar user=”bcrowell” size=”thumbnail” align=”left” /]BARRY CROWELL, MBA | BI/EDW Solution Architect
Barry is a Microsoft SQL Certified Professional with a strong knowledge of the Microsoft’s BI Stack (SSIS, SSRS, SSAS and Power BI). He has architected, developed and deployed clients’ Business Intelligence needs using Microsoft’s BI Stack and/or Solver’s BI360. His solutions have included SSIS ETL tools, SSRS reports and dashboards, Excel dashboards, Power BI reports and dashboards, and SSAS cubes. He has performed implementations as the lead consultant and/or end-user project manager. Barry has over 20 years of experience working in accounting and the Microsoft Dynamics GP industry, and in various industries such as housing authorities, universities, Tribal governments, and casino hospitality. He possesses a Bachelor’s Degree in Accounting and Business Administration from Black Hills State University and a Master’s in Business Administration from La Salle University. Barry’s experience in both the IT consultant field and experience as an accountant gives him the ability to understand the issues from both the IT and Finance prospective, and provide a solution that fits the needs for all parties involved.
