CRM reports - run counts

In my previous blog post I reported a means by which you could obtain SSRS report run times.  I have a variation on that theme that I used to determine what Microsoft CRM reports were being run at this client.


SELECT b.Name, COUNT(*)
FROM ReportServer.dbo.ExecutionLog3 a
INNER JOIN CRMDb..report b  -- this is the Report view in the CRM database
ON SUBSTRING(
a.ItemPath,  -- This is the report with the CRM database prefix
PATINDEX('%{%',a.ItemPath)+1,   -- parse the start of the report ID
LEN(a.ItemPath)-PATINDEX('%{%',a.ItemPath)-1) -- drop the end bracket from the report ID
= b.ReportID
GROUP BY b.Name


The above lists the CRM report name and the number of times it's been run.  You could query the TimeStart column from the ExecutionLog3 table for further information.