SRSS – Get list of Reports and Its Data sources and Datasets

I needed to get the list of my reports and what data source and dataset they use.

use ReportServer;
WITH XMLNAMESPACES ( DEFAULT ‘http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition’, ‘http://schemas.microsoft.com/SQLServer/reporting/reportdesigner’ AS rd )
SELECT distinct ReportName = name
,DataSetName = x.value(‘(@Name)[1]’, ‘VARCHAR(250)’)
,DataSourceName = x.value(‘(Query/DataSourceName)[1]’,’VARCHAR(250)’)
,CommandText = x.value(‘(Query/CommandText)[1]’,’VARCHAR(250)’)

FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
FROM ReportServer.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
) a
CROSS APPLY reportXML.nodes(‘/Report/DataSets/DataSet’) r ( x )
CROSS APPLY x.nodes(‘Fields/Field’) f(df)
ORDER BY name

image.png

 

 

Good Luck

SQL Server Reporting Service (SSRS) – Use email friendly name

I had my SSRS running smoothly, but there was one thing bothering me. That was email sender friendly name. In RS Configuration Manager you can setup e-mail settings, and enter senders e-mail address.
But every time I got an email with report, the sent from stated sql@sqlreports.com. But I need an friendly name.
So, I just added “SQL REPORTS” sql@sqreports.com in RSCM and it worked! IT also works with MSSQL 2012 server.

image