4 Replies Latest reply on Mar 13, 2018 11:16 AM by Steve Ostrovsky

    PDM Report showing duration file in a particular Workflow State?

    Robert Pickeral

      I have no experience in building queries in SQL on the server. Does anyone have a suggestion/solution for identifying the length specific files (.slddrw, .dwg) have sat in a particular Workflow State? A history of past files, perhaps a specified date range, last 60 days or similar? Much thanks!

        • Re: PDM Report showing duration file in a particular Workflow State?
          Steve Ostrovsky

          Delayed in State... Yeah I wish that were available quickly. Get asked for that report a lot.

           

          This will get you started. You'll have to run it in SQL Server Management studio. There's no date range in here, but you can add that in the Where clause at the end. You can also

           

          SELECT Distinct D.Filename 'File Name', S.Name 'Status Name', P.Path 'Project Path',

          CONVERT(VarChar, md.MaxDate, 101) "Date Entered State",

          CONVERT(VarChar, GETDATE(), 101) "Current Date",

          DATEDIFF (d, md.MaxDate, GETDATE()) "DaysInState",

          Datediff (d,md.MaxDate,getdate())/7 "Weeks in State"

          FROM Projects P

          INNER JOIN DocumentsInProjects DP ON P.ProjectID = DP.ProjectID

          INNER JOIN Documents D ON DP.DocumentID = D.DocumentID

          INNER JOIN Status S ON D.CurrentStatusID = S.StatusID

          Inner join (select DocumentID, MAX(date) as MaxDate

          from TransitionHistory

          Group by DocumentID)

          md ON md.documentid=d.documentid

          Where D.ObjectTypeID <> 0 and d.deleted = 0 and s.name is not null and s.name <> 'Obsolete'

          Order By filename

           

          If you know how to run the PDM Report Generator, here's the same SQL code in that format. Just save the below into a *.CRP file and then you can run inside PDM.

           

          /*******************                        ********************/

           

           

          @[DaysInState]

          §Name [Displays documents and their state and days in state]

           

          §Company [TPM]

           

          §Description

          [This query lists documents and their state and how long they've been in that state.]

           

          §Version [1.5]

           

          §Arguments

          [

          String   TNAME [N] [Enter State Name. Use "%" as wildcard. "Under Editing" - "Pending Review" - "Approved"]

          ]

           

          §Sql

          [

          SELECT (S.Name) AS 'Status Name', D.Filename AS 'File Name', P.Path As 'Project Path',

          CONVERT(VarChar, TH.Date, 101) AS "Date Entered State",

          CONVERT(VarChar, GETDATE(), 101) As "Current Date",

          DATEDIFF (d, TH.Date, GETDATE()) as "DaysInState",

          Datediff (d,th.date,getdate())/7 as "Weeks in State"

          FROM Projects AS P

          INNER JOIN DocumentsInProjects AS DP ON P.ProjectID = DP.ProjectID

          INNER JOIN Documents AS D ON DP.DocumentID = D.DocumentID

          INNER JOIN Status AS S ON D.CurrentStatusID = S.StatusID

          Inner Join TransitionHistory th ON th.documentid=d.documentid

          Where D.ObjectTypeID <> 0 and s.name is not null and s.name <> 'ECN Initiated' AND Status.Name LIKE {TNAME}

          Order By "Weeks in State" desc  

          ]

          /*******************                        ********************/