How to pull reporting server reports?
I was once asked to collect all reports information from reporting servers.
And this is how I came up with.
========================================================
select c.Name as ReportName, d.name as DataSourceName, convert(nvarchar(10), max(e.timestart), 101) as LastAccessed, c.Path, COUNT(e.timestart) as TotalNum, c.Hidden
--,
--(case
--when c.Type = 1 then 'Folder'
--when c.type = 2 then 'Report'
--when c.Type = 3 then 'Resources'
--when c.Type = 4 then 'Linked Report'
--when c.Type = 5 then 'Data Source'
--when c.Type = 6 then 'Report Model'
--when c.Type = 7 then 'Report Part'
--when c.Type = 8 then 'Shared Dataset'
--end) as ReportType
from Catalog c
left join
DataSource d
on c.ItemID = d.ItemID
left join
ExecutionLog e
on c.ItemID = e.ReportID
where c.type = 2
group by c.Name, d.Name, c.Path, c.Hidden--, c.Type
order by c.path, c.Name
========================================================
*Problem with this query is that it's not actually pulling DataSourceName correctly sometimes.
If someone creates a report and that report doesn't have the same DataSourceName as on the server, the query above will pull 'DataSource1' as a DataSourceName.
So I had to pull all DataSourceName from reporting server and use the next query and put them into Excel to get the right DataSourceName.
This query will parse XML and give Connection string.
========================================================
;WITH XMLNAMESPACES -- XML namespace def must be the first in with clause.
(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
AS rd)
,SDS AS
(SELECT SDS.name AS SharedDsName
,SDS.[Path]
,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
FROM dbo.[Catalog] AS SDS
WHERE SDS.Type = 5) -- 5 = Shared Datasource
SELECT CON.[Path]
,CON.SharedDsName
,CON.ConnString
FROM
(SELECT SDS.[Path]
,SDS.SharedDsName
,DSN.value('ConnectString[1]', 'varchar(150)') AS ConnString
FROM SDS
CROSS APPLY
SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
) AS CON
-- Optional filter:
-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'
ORDER BY CON.[Path]
,CON.SharedDsName;
<http://gallery.technet.microsoft.com/scriptcenter/List-connection-strings-of-1a9a9adc>
========================================================
According to Microsoft, they
'do not document or support querying any Report Catalog tables'
You can check this
forum.
<http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/60dd3392-42d8-4dc4-b8e6-15e9aeaad29e/>
========================================================
or here's a simpler version
select
c.name ReportName,
d.name DataSource,
c2.Name SharedDataSourceName,
c.Path ReportPath,
c2.Path DataSourcePath,
convert(nvarchar(10), max(e.timestart), 101) as LastAccessed,
COUNT(e.timestart) as TotalNum, c.Hidden
from
Catalog c
left join DataSource d
on c.ItemID = d.ItemID
left join Catalog c2
on d.Link = c2.ItemID
left join
ExecutionLog e
on c.ItemID = e.ReportID
where c.Type = 2 and c.Path not like '%BackUp%' and c.Name not like '%old%' --and c.Name like '%Sub%'-- and e.TimeStart > '6/1/2012'
group by c.Name, d.name, c2.Name, c.Path, c.Hidden, c2.Path
order by c2.Path, c.Path
or this
SELECT
DS.Name AS DatasourceName,
C.Name AS DependentItemName,
C.Path AS DependentItemPath,
COUNT(EL.timestart) as AccessedSinceJune2012
FROM
ReportServer.dbo.Catalog AS C
INNER JOIN
ReportServer.dbo.Users AS CU
ON C.CreatedByID = CU.UserID
INNER JOIN
ReportServer.dbo.Users AS MU
ON C.ModifiedByID = MU.UserID
LEFT OUTER JOIN
ReportServer.dbo.SecData AS SD
ON C.PolicyID = SD.PolicyID AND SD.AuthType = 1
INNER JOIN
ReportServer.dbo.DataSource AS DS
ON C.ItemID = DS.ItemID
LEFT JOIN
ReportServer.dbo.ExecutionLog AS EL
ON C.ItemID = EL.ReportID
WHERE
DS.Name IS NOT NULL and c.Name not like '%old%' and c.Name not like '%test' and c.path not like '%BackUp%' and EL.TimeStart > '6/1/2012'
GROUP BY
DS.Name, C.Name, C.Path
ORDER BY
DS.Name;
*I just added executionlog in order to get how many times it was accessed
Original post is from here
< http://stackoverflow.com/questions/9638431/listing-all-data-sources-and-their-dependencies-reports-items-etc-in-sql-ser>
