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.
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.
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.
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.>
Did you look at the "DocumentData of the selected files" report in Report Examples 1.crp?