Systems Engineering and RDBMS

Which Reports are being used by the teams?

Posted by decipherinfosys on February 5, 2009

This question was asked by the IT manager at the client site.  We had written over 50+ reports for them including some dashboards as well and the question was how many of those are actually being used by the end users (Sales, Operations, Supervisors).  This information can be obtained quite easily by running a SQL query against the ReportServer database.  Every time that a report is run, SSRS logs all that information into a table called ExecutionLog.  It resides in the ReportServer database.

In order to glean more information out of the system, we can join it with the Catalog table in the same database by doing this:

SELECT
C.Name                AS RPT_NAME
,    EL.UserName
,    EL.Format
,    EL.RequestType
,    EL.Parameters
,    EL.TimeStart
,    EL.TimeEnd
,    EL.[RowCount]
,    EL.Status
FROM dbo.ExecutionLog AS EL
INNER JOIN dbo.Catalog AS C
ON EL.ReportID = C.ItemID
WHERE EL.TimeStart > getdate() – 5
ORDER BY EL.TimeStart DESC

There are several pieces of useful information that we can extract from this – and can of course make this a report as well🙂

Here is another example – we can get the number of times a report was run by a particular user in the last 5 days:

SELECT
C.Name                AS RPT_NAME
,    EL.UserName            AS USERNAME
,    COUNT(*)            AS TOTAL_RUNS
FROM dbo.ExecutionLog AS EL
INNER JOIN dbo.Catalog AS C
ON EL.ReportID = C.ItemID
WHERE EL.TimeStart > convert(varchar(10), getdate() – 5, 101)
GROUP BY C.NAME, EL.USERNAME
ORDER BY C.NAME

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: