Reporting Server Report

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;

I googled and found this from Olaf Helper

<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>