AnsweredAssumed Answered

Folder Data Card SQL Query

Question asked by Jeff Thomas on Jun 6, 2013
Latest reply on Jun 7, 2013 by Jeff Thomas

I have found a sample query on line that I am trying to modify for use in creating a report with the Folder Card vaiables layed out in a table. This query will only collet the variables in the folders not the foler itself. I am new to this and can't see what needs to be changed. We would like to use the fold creation to create a new project which has all data for that project embedded in the folder variables. This report is required for the weekly management meetings.

 

One other thing I can't figure out is how to turn a .sql document into a .crp file, save-as is not available.

 

 

Thanks For the help.

 

Jeff Thomas

 

 

 

 

 

------------------------------------------------------------------------------------------
-- SQL script to show the latest values of mutiple variables by file and configuration
-- This version displays each variable value in separate columns
------------------------------------------------------------------------------------------
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='[Project_Number], [Customer], [Scope], [Project_Value], [Probability], [Job_Status], [Quote_Reviewer], [QuoteRev], [Invoices]'
-- 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, VariableName NVARCHAR(255), ValueText NVARCHAR(Max) )
INSERT INTO #TempVariables (DocID, VariableName, ValueText)
SELECT DocumentID, VariableName, ValueText
FROM VariableValue VV
INNER JOIN
(SELECT DocumentID AS DocID, VariableID AS VarID, MAX(RevisionNo) AS RevMax
FROM VariableValue
JOIN #VariableList ON #VariableList.VarID=VariableValue.VariableID
GROUP BY DocumentID, VariableID) T
ON VV.DocumentID=T.DocID
AND VV.VariableID=T.VarID
AND VV.RevisionNo=T.RevMax
INNER JOIN Variable
ON Variable.VariableID=T.VarID

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

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

EXEC sp_executesql @SQLScript

DROP TABLE #VariableList
DROP TABLE #TempVariables

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Attachments

Outcomes