Frage:
Ein Report soll nur in Teilen des Tresors suchen – wir legt man eine Ordnerabfrage im Report an?
Antwort:
Ein Report, kann eine Pfadeinschränkung verwende – wie nachstehend abgebildet:
Diese Funktionalität basiert auf der Variablenzuweisung in der Reportdatei (*.crp):
Die Variable (hier „ProjektID“) wird später in der SQL-Abfrage in geschweiften Klammern verwendet:
Der komplette hier verwendete Report, der als crp-Datei gespeichert und über den Reportgenerator verwendet werden kann, sieht so aus:
/******************* ********************/
@[ListAllFilesWithNamedOrCADBOMSUnderSpecificFolder]
§Name [List All Files With Named BOM or CAD BOM Under Specified Folder]
§Company [SolidWorks]
§Description
[This query will list all Sw assemblies or drawings with a Named or CAD BOM under a specific folder]
§Version [1.0]
§Arguments
[
ProjectID StartProjectID [1] [Enter start folder. E.g “$\Top folder”, or browse for folder.]
]
§Sql
[
declare @iProjectID int
declare @vInitPath nvarchar(260)
set @iProjectID = {StartProjectID}
set @vInitPath = (Select Path from Projects where ProjectID = @iProjectID)
Declare @DocsTable Table (DocumentID int)
–Fill temp table with all matching drawings and assemblies
Insert Into @DocsTable
Select D.DocumentID
From Documents D, Projects P, DocumentsInProjects DP, FileExtension FE
Where
D.DocumentID = DP.DocumentID
And D.ExtensionID = FE.ExtensionID
And P.ProjectID = DP.ProjectID
And D.Deleted = 0
And P.Deleted = 0
And (lower(FE.Extension) = ‘sldasm’ OR lower(FE.Extension) = ‘slddrw’ )
And P.Path like @vInitPath + ‘%’
SELECT
P1.path + D1.Filename as [Parent file],
BS.SourceDocumentVersion as [Parent Version],
D1.DocumentID as [Parent DocumentID],
D2.Filename as [BOM name],
CASE
WHEN BS.BomType IN (2,5) THEN ‘Named BOM’
WHEN BS.BomType IN (3,4) THEN ‘CAD BOM’
END AS [BOM Type],
D2.DocumentID as [BOM ID]
from BomSheets BS, Projects P1, DocumentsInProjects DIP1, Documents D1 , Documents D2, @DocsTable DT
where BS.SourceDocumentID = DT.DocumentID
and P1.ProjectID = DIP1.ProjectID
and DIP1.DocumentID = BS.SourceDocumentID
and D1.DocumentID = BS.SourceDocumentID
and D2.DocumentID = BS.BomDocumentID
and BS.BomType in (2,3,4,5)
ORDER BY [Parent file]