1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44
| declare @NomRapport varchar(425) = null
,@NomProc varchar(425) = null
;WITH
DatasetContent AS
(
SELECT C.ItemID
,CAST(CAST( CONVERT(varbinary(max),C.Content) AS VARBINARY(MAX) )AS XML) AS XMLDataset
FROM ReportServer.dbo.Catalog C
WHERE Type =8 --'Dataset'
)
SELECT Distinct
R.Path AS CheminRapport
,DS.Path AS CheminDataset
,DS.Name AS NomDataset
,Queries.X.value('(./*:CommandType/text())[1]','nvarchar(100)') AS CommandType
,Queries.X.value('(./*:CommandText/text())[1]','nvarchar(250)') AS CommandText
--,DSO.Path AS CheminDatasource
,DSO.Name AS NomDatasource
FROM reportserver.dbo.DataSets Lien
INNER JOIN ReportServer.dbo.Catalog DS
ON ds.ItemID =Lien.LinkID
INNER JOIN ReportServer.dbo.Catalog R
ON R.ItemID =lien.ItemID
INNER JOIN DatasetContent DSC
ON dsc.ItemID =DS.ItemID
CROSS APPLY DSC.XMLDataset.nodes('//*:Query') Queries(X)
INNER JOIN Reportserver.dbo.DataSource LienDSO
ON lienDSO.ItemID=DS.ItemID
INNER JOIN Reportserver.dbo.Catalog DSO
ON dso.ItemID=liendso.link
WHERE
R.Type = 2 --Rapport
AND DS.Type = 8 --Dataset
and (R.Name like '%' + @NomRapport + '%' or @NomRapport is null)
and(X.value('(./*:CommandText/text())[1]','nvarchar(250)')like '%'+ @NomProc + '%'or @NomProc is null)
ORDER BY 1,2,5 |
Partager