Using SQL Query Tables to get Descriptions

Rodney, now that we've all had a good laugh. Your spirit is the same one I had 10 years ago when I formed my PDM consulting business so I get it. You want to learn. Good for you.

Try the following:

1. Create your data connection in Excel
2. Use the code below in your query. Change the database name to your PDM database name on line 5. This is from the SW knowledgebase solution S-059164
3. It queries all the variables in the list on line 14 per configuration
4. Change the file filter to the filetypes you want to search for in line 16
5. Bring the data into a sheet called "PDMData" or something similar in your workbook
6. Use that PDMData sheet as the data source for a Pivot chart or Pivot table

------------------------------------------------------------------------------------------
-- SQL script to show the latest values of mutiple variables by file and configuration
-- This version displays each variable value in separate columns
------------------------------------------------------------------------------------------
USE YourPDMDatabase
IF OBJECT_ID('tempdb..#VariableList') IS NOT NULL DROP TABLE #VariableList
IF OBJECT_ID('tempdb..#TempVariables') IS NOT NULL DROP TABLE #TempVariables


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='[Part Number], [Description]'
-- 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, ConfigurationName NVARCHAR(255), VariableName NVARCHAR(255), ValueText NVARCHAR(Max) )
INSERT INTO #TempVariables (DocID, ConfigurationName, VariableName, ValueText)
SELECT DocumentID, ConfigurationName, VariableName, ValueText
FROM VariableValue VV
INNER JOIN
(SELECT DocumentID AS DocID, ConfigurationID AS ConfID, VariableID AS VarID, MAX(RevisionNo) AS RevMax
FROM VariableValue
JOIN #VariableList ON #VariableList.VarID=VariableValue.VariableID
GROUP BY DocumentID, ConfigurationID, VariableID) T
ON VV.DocumentID=T.DocID
AND VV.ConfigurationID=T.ConfID
AND VV.VariableID=T.VarID
AND VV.RevisionNo=T.RevMax
INNER JOIN DocumentConfiguration
ON DocumentConfiguration.ConfigurationID=T.ConfID
INNER JOIN Variable
ON Variable.VariableID=T.VarID
where vv.ConfigurationID = (select Revisions.ActiveConfigurationID from Revisions
where Revisions.RevNr = (Select Max(Revisions.RevNr) From Revisions
Where Revisions.DocumentID = vv.DocumentID)
and Revisions.DocumentID=vv.DocumentID
)


SET @SQLScript='SELECT FileName, ConfigurationName, ' + @VariableList + '
FROM
(
SELECT Path + Filename AS FileName, ConfigurationName, 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 Path+FileName LIKE ''' + @FileFilter +
''') AS SourceTable


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


EXEC sp_executesql @SQLScript


DROP TABLE #VariableList
DROP TABLE #TempVariables