1 Reply Latest reply on Jan 11, 2017 12:12 PM by Michael Dekoning

    Report that returns files in specific state and user who put them in that state

    Chris Manger

      I am hoping someone may have created a report like this or something close.  I am looking for a report that returns files in a specific state and the user who transitioned the files into that state.

       

      1. Prompt for folder to search in

      2. Prompt for State

       

      Column 1 - State

      Column 2 - User who transitioned file into that state

      Column 3 - Date/Time the file was transitioned to that state

        • Re: Report that returns files in specific state and user who put them in that state
          Michael Dekoning

          Chris,

          See if this satisfies your requirements. You'll have to copy and paste the code into a .crp file.

           

          @[DocumentsInWorkflowstateByFolder]

          §Name [Documents in selected workflow state and folder]

          §Company [SolidWorks User Community]

          §Description

          [This query lists all documents in a selected state and folder and the user and date of the transition.]

          §Version [1.1]

          §Arguments

          [

          ProjectID StartProjectID [1] [Enter start folder. E.g "$\Top folder", or browse for folder.]

          StatusID CurrentStatusID [1] [Select status]

          ]

          §Sql

          [          

          Declare @iStartFolder Int

          Declare @StatusID Int

           

          Set @iStartFolder = {StartProjectID}

          Set @StatusID = {CurrentStatusID}

           

          SELECT Documents.Filename, Status.Name, MAX(TransitionHistory.Date) AS [Date], Users.FullName

          FROM Documents

          INNER JOIN TransitionHistory ON Documents.DocumentID = TransitionHistory.DocumentID

          INNER JOIN Users ON TransitionHistory.UserID = Users.UserID

          INNER JOIN Status ON Documents.CurrentStatusID = Status.StatusID

          INNER JOIN DocumentsInProjects ON Documents.DocumentID = DocumentsInProjects.DocumentID

          INNER JOIN Projects ON DocumentsInProjects.ProjectID = Projects.ProjectID

          WHERE Projects.ProjectID = @iStartFolder

          AND Status.StatusID = @StatusID

          AND Documents.Deleted = 0

          GROUP BY Documents.Filename, Users.FullName, Status.Name    

          ]