Have you ever wondered about how your SSRS reports performance was?

  • How many people are using the reports?
  • How fast are the reports being rendered?
  • Are people running reports, or running them to export them?
  • What is the size of the reports?

In this blog post today I am going to give a brief overview of what I did to create the SSRS Report Performance and then how to use it after you have downloaded the Power BI Template File.

Overview for SSRS Report Performance

I had a requirement where we were looking for not only the usage of our SSRS reports, but also the performance and size of the SSRS reports.

So I created it in Power BI, so that this could be seen by the required people in the organisation.

To see the details of the query that I wrote you can go into the Edit Queries and see the TSQL Query that I wrote to extract the required data.

Further in the blog post I will explain how to easily take this file and run it against your own SSRS Reporting database.

Finally, this is the first draft for this report, so any suggestions, changes or additions will be welcome. Please leave me a comment.

Below is an overview of what each Page does.

Report Usage

As you can see above, this shows details around reports usage, who has been running the reports. As well as the execution times.

I have also put in a Report Name Filter (using the Smart Filter by Okviz.com) in order to filter for reports. As well as using calendar slicers to filter for specific periods.

Reports Performance

With this report we are looking for the report performance. This enabled us to view where the time was being taken for our reports that were being rendered.

I have used the Report Format, as a means to filter the reports.

As well as providing a legend with each report format type, so that you can easily see which one is taking the longest average time to complete.

I have also included the average times for the three area’s when a report is run. As this can help when trying to identify a specific report and where it is taking the longest. So by clicking on a report name at the top, it can then filter this below.

We have noticed that when people are exporting the reports to CSV or Excel that the majority of the time is spent in the processing of the report.

Report Format

What this page does is to show the Report Format that is being run and used over time, as well as reports usage.

This enables us to get an understanding of what reports the users are using, or are they running a report, to just extract the data? Or running mobile reports more than the standard SSRS reports?

I have also put in a Report Name Filter (using the Smart Filter by Okviz.com) in order to filter for reports. As well as using calendar slicers to filter for specific periods.

Report Size

The final report, is the report size. And this is simply to see which reports are the largest. And to see the report size over time.

A thing to note, is that the current screenshots are from SQL Server 2016, and I currently can identify which is a mobile report, but I cannot identify the name of the report.

I have also put in a Report Name Filter (using the Smart Filter by Okviz.com) in order to filter for reports. As well as using calendar slicers to filter for specific periods.

Download of Power BI – SSRS Reports Performance Template

You can download the Power BI – SSRS Reports Performance Template File here:

SSRS Report Performance.pbit

How to use Power BI Template File for SSRS Reports Performance

Below are the steps to complete once you have downloaded the SSRS Reports Performance template file.

Changing the Parameters

Once you open the file you will see a screen with the following parameters as shown below:

As you can see above we have got 3 parameters to enter.

  • The first is the SSRS SQL Server Name. EG: SQLServer\InstanceName
  • The second is the Report Server Database name. EG: ReportServer
  • The final parameter is a Future Scope with the Report Speed. You can leave this as the default for now.

Click Load to load your SSRS Reports Performance analytics reports.

Uploading to the Power BI Service – Enterprise Gateway Connection

If you want to use the Enterprise Gateway connection, you must ensure that you have got it installed and configured in both the Power BI service, and on your Server that has access to your SQL Server.

NOTE: I have found that it works as expected when both the SSRS SQL Server Name and the name that you configured in the Power BI Service for the Enterprise Gateway are identical.

Now you can create a scheduled refresh and share this with users in your organisation.

Keeping history for SSRS ReportServer Database

Just as a final note, currently the SSRS ReportServer database only keeps 3 months’ worth of history.

So if you want to keep the entire history I would recommend that you extract this data into another table. After which you can then modify the query to look at your historical table. If you do require assistance with this please let me know.