Workflow 2.0 Approval Hierarchy - Graphical Representation

Early in the morning, I received a request from a friend of mine; Steve Chapman, inquiring about the capability to extract the approval hierarchy configured within Dynamics GP, workflow 2.0 so that it can be presented "Graphically" as it was available with the old Business Portal - Requisition Management module. The idea is quite tempting and I was tempted enough to to roll my sleeves up as the SQL fun time was about to begin.

The idea was to simplify the data stored within the approval hierarchy in some sort of a child-parent relationship, just like the old fashioned employee-manager quiz we used to have in the university. Theoretically speaking, It must be straight forward to do so, as illustrated int he graph below:

Employee-Manager Hierarchy Relationship

The steps configured within the approval hierarchy do have a parent-child relationship, and this relationship should be prepared in some sort of a staging area, so that further SQL functions can run seamlessly and visualize in a diagram-like result. The table that is directly targeted is WF100003; Workflow Step Table. This table includes all the steps and their associated details such as "Assign to User", "Predecessors"... etc. For that, I created the following view:

CREATE VIEW Vw_WorkflowHierarchy

AS

SELECT  A.ID AS ID ,

        ISNULL(B.ID, 0) AS ParentID ,

        A.WORKFLOW_STEP_NAME AS WorkflowStepName ,

        CASE ISNULL(B.ID, 0)

            WHEN 0 THEN ''

            ELSE 'Assign To: ' + RTRIM(LTRIM(C.ADDomain)) + '\'

                + RTRIM(LTRIM(C.ADDisplayName))

        END AS AssignToDetails ,

        '' EmptyColumn

FROM   

(

SELECT  CONVERT(INT, ROW_NUMBER() OVER ( ORDER BY DEX_ROW_ID )) AS ID ,

        Workflow_Name ,

        WORKFLOW_STEP_NAME ,

        WF_Step_Predecessor ,

        Workflow_Step_Assign_To

FROM    WF100003 AS B

WHERE   WORKFLOW_NAME

              = 'Requstion Management Workflow'

) AS A

LEFT OUTER JOIN

(

SELECT  CONVERT(INT, ROW_NUMBER() OVER ( ORDER BY DEX_ROW_ID )) AS ID ,

        Workflow_Name ,

        WORKFLOW_STEP_NAME ,

        WF_Step_Predecessor ,

        Workflow_Step_Assign_To

FROM    WF100003 AS B

WHERE   WORKFLOW_NAME =

              'Requstion Management Workflow'

) AS B ON A.WF_Step_Predecessor = B.Workflow_Step_Name

LEFT OUTER JOIN WF40200 AS C

ON A.Workflow_Step_Assign_To = C.UsersListGuid

 

Vw_WorkflowHierarchy Result

Data Visualization   Here comes the interesting part !

I remembered an old article which I came through long time ago written by MVP Brad Schulz, he developed a legendary stored procedure (usp_DrawTable) that technically analyzes such relations and populate it graphically through SQL. It is one of the thing that I have read and would never forget as it is definitely a piece of art. So I decided to use his master piece in presenting the approval hierarchy graphically through SQL.The article can be found on this link, and the stored procedure can be found on ups_DrawTable.

After creating the stored procedure, I passed the data stored within the staging into the stored procedure by considering the following script:

IF OBJECT_ID('tempdb..#TreeData', 'U') IS NOT NULL

    DROP TABLE #TreeData

SELECT  ID ,

        ISNULL(ParentID, 0) AS ParentID ,

        WorkflowStepName ,

        AssignToDetails ,

        EmptyColumn

INTO    #TreeData

FROM    Vw_WorkflowHierarchy

           EXEC usp_DrawTree 1

Here is the astonishing result

Graphical Approval Hierarchy - SQL Result

Thanks to Brad for he creative work; good deeds remain and shine. Thanks to Steve as well for the unique inquiry which was absolutely mind-stretching.

Best Regards,
Mahmoud M. AlSaadi