2 Replies Latest reply on Jun 7, 2013 2:24 PM by Jeff Thomas

    Folder Data Card SQL Query

    Jeff Thomas

      I have found a sample query on line that I am trying to modify for use in creating a report with the Folder Card vaiables layed out in a table. This query will only collet the variables in the folders not the foler itself. I am new to this and can't see what needs to be changed. We would like to use the fold creation to create a new project which has all data for that project embedded in the folder variables. This report is required for the weekly management meetings.

       

      One other thing I can't figure out is how to turn a .sql document into a .crp file, save-as is not available.

       

       

      Thanks For the help.

       

      Jeff Thomas

       

       

       

       

       

      ------------------------------------------------------------------------------------------
      -- SQL script to show the latest values of mutiple variables by file and configuration
      -- This version displays each variable value in separate columns
      ------------------------------------------------------------------------------------------
      DECLARE @VariableList NVARCHAR(255)
      DECLARE @FileFilter NVARCHAR(255)
      ---------------------------------------------------------------------------------------
      -- Alter the line below with the names of the variables in [] that you want to include
      SET @VariableList='[Project_Number], [Customer], [Scope], [Project_Value], [Probability], [Job_Status], [Quote_Reviewer], [QuoteRev], [Invoices]'
      -- Alter the line below with a path/filename filter if required
      SET @FileFilter='%'
      ---------------------------------------------------------------------------------------

      DECLARE @SQLScript NVARCHAR(Max)

      CREATE TABLE #VariableList (VarID INT)
      SET @SQLScript='INSERT INTO #VariableList
      SELECT VariableID
      FROM Variable
      WHERE VariableName IN (' + REPLACE(REPLACE(@VariableList,'[',''''),']','''') + ')'

      EXEC sp_executesql @SQLScript

      CREATE TABLE #TempVariables( DocID INT, VariableName NVARCHAR(255), ValueText NVARCHAR(Max) )
      INSERT INTO #TempVariables (DocID, VariableName, ValueText)
      SELECT DocumentID, VariableName, ValueText
      FROM VariableValue VV
      INNER JOIN
      (SELECT DocumentID AS DocID, VariableID AS VarID, MAX(RevisionNo) AS RevMax
      FROM VariableValue
      JOIN #VariableList ON #VariableList.VarID=VariableValue.VariableID
      GROUP BY DocumentID, VariableID) T
      ON VV.DocumentID=T.DocID
      AND VV.VariableID=T.VarID
      AND VV.RevisionNo=T.RevMax
      INNER JOIN Variable
      ON Variable.VariableID=T.VarID

      SET @SQLScript='SELECT FileName, ' + @Variablelist + '
      FROM
      (
      SELECT Path + Filename AS FileName, VariableName, ValueText
      FROM Documents
      INNER JOIN
      (SELECT DocumentsInProjects.DocumentID, Projects.Path AS Path
        FROM DocumentsInProjects LEFT JOIN Projects ON Projects.ProjectID=DocumentsInProjects.ProjectID) AS P
      ON P.DocumentID=Documents.DocumentID
      INNER JOIN
      #TempVariables T
      ON T.DocID=Documents.DocumentID
      WHERE FileName LIKE ''' + @FileFilter +
      ''') AS SourceTable

      PIVOT
      (
      MAX(ValueText)
      FOR VariableName IN
      (' + @VariableList + ')
      ) AS PivotTable
      ORDER BY FileName'

      EXEC sp_executesql @SQLScript

      DROP TABLE #VariableList
      DROP TABLE #TempVariables

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

        • Re: Folder Data Card SQL Query
          Charley Saint

          Jeff,

           

          If you browse to "C:\Program Files\SolidWorks Enterprise PDM\Report Examples 1.crp" you can see the format of a crp file and where you need to copy the SQL query in. You won't need to use most of the above query because unlike files, folders don't have a version. Essentially you just need to join the projects table to the variablevalue table to the variables table and you will get your current folder variables. It's a very simple query unlike what you have to do for files.