Creating a Report Server Usage Report with SQL Server Reporting Services

Creating a Report Server Usage Report with SQL Server Reporting Services

Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports book coverFor IT to consolidate and optimize many reports in a business reporting platform, it must know what users are using reports and how often. It’s critical to know how long different reports take to run and when the peak and lull times occur throughout the day for scheduling subscriptions and maintenance tasks.

A server usage report is an indispensible tool for wrangling out of control reports. With it, IT leaders can easily answer questions like:

  • What reports are being run most often?

  • Who is running certain reports and when?

  • What reports have excessively long running queries?

  • What reports don’t ever get run?

  • What similar reports may be candidates for consolidation?

    Product Versions

  • Reporting Services 2000

  • Reporting Services 2005

  • Reporting Services 2008

    What You’ll Need

  • Connection to the ReportServer with read permission

  • Three datasets and queries for each report section

  • Data range parameters

Fortunately, Reporting Services gives you the ability to capture all of these important facts in the report server management databases, ReportServer and ReportServerTempDB. The following example report server usage report comes directly from the ReportServer database without first staging the data in another location. In a full, production solution where you deal with a lot of report activity and may want to analyze usage history over a long period of time, the report usage data should be transformed into a staging database.

The Report Execution Log report, shown in Figure P5-1, contains three separate report sections that each deliver a different set of statistical information. An hourly view of report server usage is an excellent tool for scheduling subscriptions and avoiding resource contention on the server. The daily view shown in Figure P5-1 is better suited for long-term usage and capacity planning.

SQL Server Reporting Services Report Execution Log

Figure P5-1

This report has two parameters, used to set the start and end date of the period being analyzed.

The first section at the top of the report body contains a combination column and line chart, representing the number of report executions during a day. The line chart on the same axis represents the aggregate volume of data sent from a data source.

The second section is a table listing the top five most frequently executed reports for the date selection period.

The last section shows all of the reports that were not executed during the specified period of time. This is an effective tool to find unused or seldom-used reports that can be removed or consolidated with others.

Since Reporting Services was introduced in 2003, the complexity and number of tables in the ReportServer database have increased. In SSRS 2008 R2, there are 31 tables. Only three tables are necessary to return information about report execution details, report details, and the users running reports. For the purposes of this example, the only significant difference affecting this report is that the name of the report execution log table has changed. In SSRS 2000 and 2005, the table name was ExecutionLog and in SSRS 2008 it was changed to ExecutionLogStorage.

In SSRS 2008, two views were added to the ReportServer database to simplify execution log queries and to provide backward compatibility. A view named ExecutionLog covers the ExecutionLogStorage table so references to the former table name remain to be valid in newer product versions.

Designing the Report

The three report sections are driven by three separate datasets. You will create these datasets with common parameters and then add corresponding data regions to the report.

1. Create a data source with a connection to the Report Server database on the production report server or whatever report server you need to analyze.

2. Open SQL Server Management Studio and connect a new query editor window to the ReportServer database.

NOTE: The ReportServer database can be given a different name during setup or after using the Reporting Services Configuration Manager. If it has been given a different name, substitute it in the instructions that follow.

3. Design the following query in the SSMS query editor. Note the literal dates range in the sample script. Make whatever changes to these variable assignments are needed to return a good sampling of report execution activity.

– Execution Log:

 

– For debugging (remove next 4 lines after pasting to dataset) –

DECLARE @DateFrom Date

DECLARE @DateTo Date

SET @DateFrom = ’2008-01-01′

SET @DateTo = ’2008-06-30′

– End debugging script –

 

SELECT    

      DATEPART(Hour, TimeStart) AS ReportYear

    , DATEPART(Month, TimeStart) AS ReportMonth

    , DATEPART(Day, TimeStart) AS ReportDay

    , DATEPART(Hour, TimeStart) AS ReportHour

    , Type

    , COUNT(Name) AS ExecutionCount

    , SUM(TimeDataRetrieval) AS TimeDataRetrievalSum

    , SUM(TimeProcessing) AS TimeProcessingSum

    , SUM(TimeRendering) AS TimeRenderingSum

    , SUM(ByteCount) AS ByteCountSum

    , SUM([RowCount]) AS RowCountSum

FROM

(

    SELECT TimeStart, Catalog.Type, Catalog.Name, TimeDataRetrieval,

  TimeProcessing, TimeRendering, ByteCount, [RowCount]

    FROM

    Catalog INNER JOIN ExecutionLog ON Catalog.ItemID =

       ExecutionLog.ReportID LEFT OUTER JOIN

    Users ON Catalog.CreatedByID = Users.UserID

    WHERE ExecutionLog.TimeStart BETWEEN @DateFrom AND @DateTo

) AS RE

GROUP BY

      DATEPART(Hour, TimeStart)

    , DATEPART(Month, TimeStart)

    , DATEPART(Day, TimeStart)

    , DATEPART(Hour, TimeStart)

    , Type

ORDER BY

      ReportYear

    , ReportMonth

    , ReportDay

    , ReportHour

    , Type

The results in Figure P5-2 show a record of every report execution. This includes the date and time the report request was made. All of the time based measures are the number of milliseconds elapsed for each event.

SQL Server Reporting Services record of every report execution

Figure P5-2

4. Create a new dataset named ExecutionLog. Copy and paste the query script from SSMS.

5. Delete the four debugging lines at the beginning of the script (starting with DECLARE and SET).

6. Create another dataset named Top5MostFrequent and use the following query script:

– Top 5 Most Frequent:

SELECT TOP 5  

      COUNT(Name) AS ExecutionCount

    , Name

    , SUM(TimeDataRetrieval) AS TimeDataRetrievalSum

    , SUM(TimeProcessing) AS TimeProcessingSum

    , SUM(TimeRendering) AS TimeRenderingSum

    , SUM(ByteCount) AS ByteCountSum

    , SUM([RowCount]) AS RowCountSum

FROM

(

    SELECT TimeStart, Catalog.Type, Catalog.Name,

      TimeDataRetrieval, TimeProcessing, TimeRendering, ByteCount, [RowCount]

    FROM

    Catalog INNER JOIN ExecutionLog ON Catalog.ItemID = ExecutionLog.ReportID

     WHERE ExecutionLog.TimeStart BETWEEN @DateFrom AND @DateTo AND Type = 2

) AS RE

GROUP BY

        Name

ORDER BY

        COUNT(Name) DESC

      , Name

7. Create another dataset named UnusedReports and use the following query script:

– Unused Reports:

SELECT Name, Path, UserName

FROM Catalog INNER JOIN dbo.Users ON Catalog.CreatedByID = Users.UserID

WHERE Type = 2 AND

    Catalog.ItemID NOT IN

    (

        SELECT ExecutionLog.ReportID

        FROM ExecutionLog

         WHERE ExecutionLog.TimeStart BETWEEN @DateFrom AND @DateTo

    )

    ORDER BY Name

8. The specific report design elements and specific features may be designed according to your personal preference. Using the finished sample report as a model, build the following elements:

a. Add a column chart based on the ExecutionLog dataset.

b. Use the ExecutionCount field for the series value.

c. Add two Category axis groups for the ReportMonth and ReportDay fields.

d. If you are using SSRS 2008, add a second series using the TimeRenderingSum field. Right click on this field in the Data pane and change the chart type for this series to a line chart.

Figure P5-3 shows the Report Builder 3.0 designer. The steps for designing the report in Report Builder 2.0 for SSRS 2008 are the same. The chart designer looks a little different but has similar functionality.

SQL Server Reporting Services Report Builder 3.0 designer

Figure P5-3

9. Add table data regions for the Top5MostFrequent and UnusedReports datasets. Style these to your liking.

10. Add appropriate report and page headers as well as textboxes to label each data region.

The execution log table rows are automatically purged after 60 days by default. This setting may be modified in Report Manager’s Site Settings page. The setting is labeled Remove log entries older than this number of days. If you want to report server usage older than a few months, it’s a good idea to transform this data to an archive table and use this in the query rather than the live execution log table. You can create an SSIS package to import the contents of the ExecutionLog to another table.

Final Thoughts

This report is an excellent resource for the system administrator to use to monitor the report server pulse. Using this and similar reports, you can check your report server at regular intervals to better understand the report and data source usage. If you know what reports are being run and who is running them, you have a much better chance of proactively meeting user needs and mitigating issues before they become problems.

Report solution architects and technical managers should pay attention to long-running queries, peak and idle periods, and overall server capacity and loads. Also, knowing which reports are not being used can help you remove unnecessary reports from the server and to consolidate those that can better meet more users’ need’s with fewer reports.

This article is excerpted from Part V: "Integrated Reporting Applications" 
of the book "Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports" by Paul Turley, Robert M. Bruckner (ISBN: 978-0-470-56311-3, Wrox, 2010, Copyright Wiley Publishing Inc.)

 

Tags:

Comments

5 Responses to “Creating a Report Server Usage Report with SQL Server Reporting Services”

  1. Anonymous says:

    Genius, Thankyou very much!

  2. Anonymous says:

    For the first Data set you want to select the Year as the Datepart.
    DATEPART(Hour, TimeStart) AS ReportYear
    Should Read:
    DATEPART(Year, TimeStart) AS ReportYear

  3. Anonymous says:

     This really helped me a lot. Thank you!

  4. Anonymous says:

    In the first query you have DATEPART(Hour, TimeStart) AS ReportYear. I believe it should be DATEPART(Year, TimeStart) AS ReportYear. Then you would need to change the GROUP BY as you have DATEPART(Hour, TimeStart) which would also need to be changed to DATEPART(Year, TimeStart).

  5. kshitiz says:

    hey
    I have a question ,I am running one report first time from report server in internet explorer. I need to know which report is running from the back end.Is there any query so that i could know which report is currently running. in execution log it enters data once it has been run, I need to konw which report is currenlty running?
    can you please help me on this

    Thanks

Leave a Reply

What is 4 + 15 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)