How to create Dynamics GP SmartList Workflow Reporting

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.

barry1


[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.

Share this post

Related Posts