-
Re: Report to show all variable values associated with a document and data card.
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.
I did. It does not appear to work. I even tried to pull it in again. I copied the section out and saved it separately as a crp. Do you have one that works ? I did find a reference somewhere else that this section does not work.
-
Re: Report to show all variable values associated with a document and data card.
I didn't realize it doesn't work. Sorry to send you down a rabbit hole. I was going to say that getting this information from the database tables is not easy when I saw that report.
If I need to get variable values from files in PDM Professional, I use the API.
-
-
-
Re: Report to show all variable values associated with a document and data card.
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.>