4 Replies Latest reply on Oct 26, 2017 12:35 PM by Ulf Stockburger

    Report to show all variable values associated with a document and data card.

    David Ouellette

      I want to have a report that shows all the variable values associated with an engineering change doc file. I've looked through all the CRP files that I can find with no success. I do not have access to the sql server or the sql tool that will allow me to choose areas to search. So I'm locked into modifying existing crp sql quiries to get what I want.  I could also use a dump and pick what I want. The end result I'm working for is a excel file with EC status by number, bar graphs etc.

        • Re: Report to show all variable values associated with a document and data card.
          Michael Dekoning

          Did you look at the "DocumentData of the selected files" report in Report Examples 1.crp?

          • Re: Report to show all variable values associated with a document and data card.
            Ulf Stockburger

            Hi David Ouellette

             

            I use a SQL query that lists all variables assigned to a version of a document. To use it in the Report Generator, it is necessary to insert it in §Sql[] and assign the variables @cName, @fName and @version to the variables in §Arguments[]. Feel free to fit it to your needs. Further information: http://help.solidworks.com/2014/english/EnterprisePDM/admin/t_reports_formatting_query.htm. I hope it helps.

             

            -- <SQL Query: Lists all assigned variable values for the document.>

            -- Ulf-Dirk Stockburger 2014

            DECLARE @cName NVARCHAR(max) = N'' -- Configuration name

            DECLARE @fName NVARCHAR(max) = N'' -- File name

            DECLARE @version INT = 1 -- File version

            DECLARE @tabVarTmp TABLE (

            varId INT

            ,varText NVARCHAR(max)

            ,varRev INT

            ,varConfig INT

            ,ConfigurationName NVARCHAR(max)

            )

            DECLARE @tabVar TABLE (

            varId INT

            ,varText NVARCHAR(max)

            ,varRev INT

            ,varConfig INT

            ,ConfigurationName NVARCHAR(max)

            )

            INSERT INTO @tabVarTmp

            SELECT VariableID

            ,ValueText

            ,RevisionNo

            ,ConfigurationID

            ,NULL

            FROM VariableValue

            WHERE (

            DocumentID = (

            SELECT [DocumentID]

            FROM [dbo].[Documents]

            WHERE [Filename] = @fName

            )

            AND ConfigurationID = (

            SELECT [ConfigurationID]

            FROM [dbo].[DocumentConfiguration]

            WHERE [ConfigurationName] = @cName

            )

            )

            AND RevisionNo <= @version

            GROUP BY RevisionNo

            ,ValueText

            ,VariableID

            ,ConfigurationID

            ORDER BY RevisionNo

            ,VariableID

            ,ConfigurationID

            DECLARE @varRev INT = - 1

            DECLARE @varRevTmp INT = - 1

            DECLARE Cur CURSOR LOCAL STATIC

            FOR

            SELECT varRev

            FROM @tabVarTmp

            ORDER BY varRev DESC

            OPEN Cur

            FETCH FIRST

            FROM Cur

            INTO @varRev

            WHILE @@fetch_status = 0

            BEGIN

            IF @varRevTmp != @varRev

            BEGIN

            SET @varRevTmp = @varRev

            INSERT INTO @tabVar

            SELECT T0.varId

            ,T0.varText

            ,T0.varRev

            ,T0.varConfig

            ,T1.ConfigurationName

            FROM @tabVarTmp T0

            LEFT JOIN [dbo].[DocumentConfiguration] T1 ON T0.varConfig = T1.[ConfigurationID]

            WHERE T0.varRev = @varRev

            AND T0.varId NOT IN (

            SELECT varId

            FROM @tabVar

            )

            END

            FETCH NEXT

            FROM Cur

            INTO @varRev

            END

            CLOSE Cur

            DEALLOCATE Cur

            SELECT T1.VariableName [Variable]

            ,T0.varText [Variable Value]

            ,T0.varRev [Set in Version]

            ,T0.ConfigurationName [Configuration]

            FROM @tabVar T0

            INNER JOIN [dbo].[Variable] T1 ON T0.varId = T1.VariableID

            ORDER BY T1.VariableName

            -- </SQL Query: Lists all assigned variable values for the document.>

             

            BiI SAP-PDM