Afna CRM: Get CRM Reports statistics

[Blog bot]

Источник: http://a-crm.blogspot.com/2018/01/ge...tatistics.html
==============

If you want to see how may times a certain report has been executed, run this query on the ReportServer database. First replace the [CRM DATABASE NAME_MSCRM] with the correct CRM database name. If the database is not on the same server, then you can't simply joint the tables in one query, but still you can get the report Id list from the Reps (Name column).

;WITH Reps AS
(
SELECT c.Name,
c.[Path],
COUNT(*) AS TimesRun,
MAX(l.TimeStart) AS [LastRun]
FROM [ReportServer].[dbo].[ExecutionLog](NOLOCK) AS l
INNER JOIN [ReportServer].[dbo].[Catalog](NOLOCK) AS c ON l.ReportID = C.ItemID
WHERE c.Type = 2 -- Only show reports 1=folder, 2=Report, 3=Resource, 4=Linked Report, 5=Data Source
GROUP BY l.ReportId,
c.Name,
c.[Path]
)
SELECT CRM.Name, CRM.ReportId, Reps.TimesRun, Reps.LastRun, Reps.Path FROM Reps JOIN [CRM DATABASE NAME_MSCRM]..Report CRM ON Reps.Name = '{' + CAST(CRM.reportid AS CHAR(36)) + '}'


Be aware that the statistic is there for a limited time. Execute this to get the minimum date: SELECT MIN(ExecutionLog.TimeStart) FROM [ReportServer].[dbo].ExecutionLog

Источник: http://a-crm.blogspot.com/2018/01/ge...tatistics.html