Modifying SQL Query for specific PDM Variables

First query correct variable id for variable to use in your query.

SELECT [VariableID]

      ,[VariableName]

      ,[VariableType]

      ,[IsDeleted]

      ,[FlagUnique]

      ,[FlagMandatory]

      ,[FlagFreeUpdateAllVersion]

      ,[FlagFreeUpdateLatestVersion]

  FROM [VAULT_NAME].[dbo].[Variable]

  where variablename like '%revision%'

Then innerjoin VariableValue table to your query earlier query

Then add valuetext from variablevalue table to select.

Example:

Good way to test your queries is using SELECT TOP 10 to only select top 10 rows, makes testing much faster.

Another way you can use is find some known file from PDM and use it in WHERE clause. Example:

Where DP.Deleted = 0 and VV.VariableID = 61 and D.Filename like 'filename.slpdrt'

I have added here to Distinct to skip duplicate rows.

This will query all revisions of file by that variable, so you still have probably some work to do.

SELECT DISTINCT P.Path As 'Project Path', DP.DocumentID as DocumentID, D.Filename, VV.ValueText

FROM Projects AS P INNER JOIN  

DocumentsInProjects AS DP ON P.ProjectID = DP.ProjectID

INNER JOIN

Documents as D ON D.DocumentID = DP.DocumentID

INNER JOIN

VariableValue as VV ON VV.DocumentID = D.DocumentID

Where DP.Deleted = 0 and VV.VariableID = 61

Just to add to the confusion: Revisions in the database are versions, so as Eero Luotio points out, you're better off getting the variable value.

Hi John

Maybe this will help you. ShowAllAssignedVariables.7z

BiI SAP-PDM

Hey John,

I use this report all the time. It's pretty straightforward on how to add variable columns and filter based on path or specific configurations. It's not the fastest report in the world which could be fixed but then it'd be much harder to configure, hope it helps.

declare @report_cols as varchar(500)
set @report_cols = '[Description], [Part Number]' -- maintain this format no comma after last entry


declare @filter_path as varchar(250)
set @filter_path = '\Projects\' -- don't forget trailing slash, for entire vault use just \, but for speed limit it to certain folders


declare @filter_configs as varchar(250)
set @filter_configs = '(''@'', ''Default'')' -- maintain this format, comment below to not filter configs, for just @ use '(''@'')'


declare @sql as varchar(max)
set @sql = 'with maxvar as
(select documentid, configurationid, variableid, max(RevisionNo) revisionno from VariableValue
group by DocumentID, ConfigurationID, VariableID)


select path, filename, ConfigurationName, ' + @report_cols + ' -- replace , with + to not split up file and path
from (
select p.path, filename, dc.ConfigurationName, variablename, vv.valuecache
from documents d
inner join DocumentsInProjects dp on d.DocumentID = dp.DocumentID
inner join projects p on p.projectid = dp.ProjectID
inner join ProjectTreeRec ptr on ptr.ChildProjectID = dp.ProjectID
inner join Projects p2 on p2.ProjectID = ptr.ParentProjectID
inner join VariableValue vv on vv.DocumentID = d.DocumentID
inner join maxvar mv on vv.DocumentID = mv.DocumentID and vv.ConfigurationID = mv.ConfigurationID
and vv.VariableID = mv.VariableID and vv.RevisionNo = mv.revisionno
inner join variable v on v.VariableID = vv.VariableID
inner join DocumentConfiguration dc on dc.ConfigurationID = vv.ConfigurationID
where 
d.Deleted = 0 and dp.Deleted = 0  -- ignore deleted files
and p2.path = ''' + @filter_path + ''' -- filter to only files in a specific folder
and dc.configurationname in ' + @filter_configs + ' -- comment out this line to return all configs
) a
pivot(
max(valuecache)
for
variablename in (' + @report_cols + ')
) b'


execute(@sql)