Which SSRS reports are being run through Dynamics CRM?

Microsoft Dynamics CRM uses SQL Server Reporting Services (SSRS) for its reporting platform. It’s a fairly decent integration, but it lacks one visible aspect from CRM: what reports are being run?

Getting detailed report execution (who requested the report, what parameters were used at runtime) would require digging deep, but if you just want to know which reports were run and when they were run, you can do it from two SQL queries.

For a list of all reports run for a CRM instance, use the SQL below. Replace “CRM_MSCRM” with the name of your CRM database, and CRM_ReportServer with the name of your SSRS reporting database.

with ReportNames as (
	select r.name, '{' + cast(r.reportid as nvarchar(40)) + '}' as reportid
	from CRM_MSCRM..FilteredReport r
)
SELECT
	rn.Name,
	E.TimeStart,
	E.TimeEnd,
	E.Status
FROM CRM_ReportServer.dbo.Catalog C with(nolock)
left outer join ReportNames rn on c.Name = rn.ReportID
LEFT OUTER JOIN CRM_ReportServer.dbo.ExecutionLog  E with(nolock) ON C.ItemID = E.ReportID
WHERE C.type=2
and rn.Name is not null
order by e.TimeStart desc

If you want a count of all reports run, along with the first/last date they were run, use the following (again replacing CRM_MSCRM and CRM_ReportServer as necessary):

with ReportNames as (
	select r.name, '{' + cast(r.reportid as nvarchar(40)) + '}' as reportid
	from CRM_MSCRM..FilteredReport r
)
SELECT
	rn.Name,
	count(*) as RunCount,
	MIN(e.TimeStart) as FirstReport,
	max(E.TimeEnd) as LastReport
FROM CRM_ReportServer.dbo.Catalog C with(nolock)
left outer join ReportNames rn on c.Name = rn.ReportID
LEFT OUTER JOIN CRM_ReportServer.dbo.ExecutionLog  E with(nolock) ON C.ItemID = E.ReportID
WHERE C.type=2
and rn.Name is not null
group by rn.Name
order by RunCount desc

It may not be all the detail you want, but it’s definitely a start, and a quick way to see what is being run, and what you can probably get rid of because it’s rarely if ever being run.