SQL Server Reporting Services Auto Refresh Report


By:   |   Updated: 2019-02-25   |   Comments (6)   |   Related: > Reporting Services Development


Problem

We are using paginated .RDL reports and host them using SQL Server Reporting Services as a means to monitor our servers. Unfortunately, the monitoring information needs to be refreshed constantly (like performance counters) and it is cumbersome to have to refresh the report every few seconds, especially if you must act on any given moment while troubleshooting an issue or waiting for an alert. In those cases, you would need a solution to auto refresh your report information, see how this is possible with a few configuration options and T-SQL using Reporting Services.

Solution

With the help of SQL Server Reporting Services and some T-SQL commands you can achieve a solution to auto-refresh data in the report, and even being able to generate graphs that change over time based on this. One thing to notice, Auto Refresh only works for HTML rendered reports, not for data exports, so if you want to auto-refresh exported data, you must export the report again.

Setup Example to Auto Refresh SSRS Report

We will use a simple report with basic information for this example, you can see how to format a report in this tip.

For this point, we will just put the machine information and the report execution time.

Sample SQL Server Reporting Services report

Then we will add a new dataset with the following T-SQL, this will generate a simple random number.

SELECT RAND() as [RandNumber];			
SQL Server Reporting Services Dataset properties

Then we add the field to our report, you can use just a drag and drop for this, since it is a scalar value.

Random number embedded in the SSRS Report

If you execute your report now with the Preview option, you can see a random number is generated each time you hit the refresh button in the toolbar.

Sample SQL Server Reporting Services Report

We will proceed to do the refresh automatically with a report property.

Auto Refresh SQL Server Reporting Services Report Properties

Return to report design mode and navigate to report properties.  There is a property called AutoRefresh with a default of 0 (meaning no refresh). On this property you define in seconds the refresh interval you want for your report. For this example, we set it to 5 (which is 5 seconds).

SSRS AutoRefresh Property

We save the report and then navigate to the report preview again.  You will see that every 5 seconds the report is refreshed (of course you will not be able to see it on a static image, but you get the point).

Example 2

Ok, this is great for just one static number, but what if you want to plot points and keep history of previous executions? For this case, things get trickier as we will see in the next example.

Auto Refresh SQL Server Reporting Services on a Chart

For our second example, we will plot the random number generated and a consecutive integer to indicate the iteration number and we will store it in a table that we will store in tempdb just for demonstration purposes, but you can save it any database you want.

We proceed to create the table that will store the sample data with the following T-SQL.

USE TEMPDB;
GO
 
CREATE TABLE TMP_data
(
   ID int IDENTITY(1,1) NOT NULL,
   RandNumber float NULL
);			

Once the table has been created, we will create a new dataset on our report and enter the following T-SQL.

INSERT INTO tempdb..TMP_data
VALUES (RAND());
 
SELECT TOP (20) ID, RandNumber 
FROM tempdb..TMP_data
ORDER BY ID Desc;			

Here is an explanation of what is being done. On each report refresh, the dataset is retrieved again, so we insert into the table a new random number on each iteration, there is no need to insert the ID field since we declared it as an IDENTITY with an auto increment of 1.

Then, we select the data on the table and since we want a clean chart, we only retrieve the last 20 items (I usually recommend sorting data at the database layer whenever possible, but we will do it just for this example).

Your data source and query should look like this:

SSRS Dataset 2

The next step is simple, just drag and drop any chart you want from the toolbox (we will use a simple line chart with all the defaults) and configure it to use the dataset we just created.

Adding a SSRS line chart to the report

Save the report and navigate to Preview mode. You should start seeing the chart being populated, here is the first execution.

Review the SSRS Report

After a few executions you should see something like this.

SSRS Report Updates

We put a limit of 20 items in our query, so with more than 20 iterations, we will just see the last 20 items as shown below.

20 data points on the SSRS report is the limit

Implement an Auto Cleanup Task

If you are not careful and you don’t implement a cleanup task, you could insert a lot of data into the table with this approach. We can prevent this by adding some more lines of code to our dataset.

We just modify our dataset T-SQL with the following code:

INSERT INTO tempdb..TMP_data
VALUES (RAND());
 
-- We add this extra line of code to just maintain last 20 records
DELETE FROM tempdb..TMP_data
WHERE ID < (SELECT MAX(ID)-19 FROM tempdb..TMP_data)
 
-- We no longer need the TOP 20 and ORDER BY in our select
SELECT ID, RandNumber 
FROM tempdb..TMP_data			

Here is an explanation of what is being done. We delete records to just maintain the last 20 records in our table and since we just maintain a maximum of 20 records, we don’t need the TOP or the ORDER BY in our SELECT statement anymore, so we can perform the sorting in the presentation layer (in this case the report).

So, we modify our dataset with the new code.

Dataset properties updated in SSRS to only have 20 records

After more than 20 executions, just the last 20 items are maintained and displayed.

Just 20 items are maintained in the SSRS Report

If you want your report to support concurrent users, you must add another field to indicate the ID of the session and filter by it, so other sessions don’t delete data from each other.

This type of auto refresh functionality can be used on monitoring reports or wallboard applications (for example a screen in a room showing latest data without user intervention).

I have published the report to a Report Server, you can see that the Auto Refresh works just fine and the data is being displayed correctly.

Final SSRS Report
Next Steps
  • SQL Server Data Tools (SSDT) is the tool from Microsoft to develop Reporting Services, Analysis Services and Integration Services solutions in a Visual Studio like environment, you can download the latest version from here.
  • This option is available for .RDL paginated reports, these features are not available in Power BI. Please take this into consideration if you plan to migrate your reports from SSRS to Power BI.
  • You can implement more complex logic on the dataset, so the table can be created and dropped automatically.
  • Be aware that this property may not work correctly on some browsers or embedded applications, so always do testing before going live.
  • You can see my other tips about Reporting Services here.


Last Updated: 2019-02-25


get scripts

next tip button



About the author
MSSQLTips author Eduardo Pivaral Eduardo Pivaral is an MCSA, SQL Server Database Administrator and Developer with over 15 years experience working in large environments.

View all my tips



Comments For This Article




Wednesday, April 22, 2020 - 10:56:18 AM - Josť Back To Top (85442)

Hi, We implemented this functionality to render some reports to the night shift operators. It has worked really good, but I want to know if we have any way to reestablish the connection if is interrupted without hitting "view report" button again?


Tuesday, April 14, 2020 - 10:40:48 AM - Eduardo Pivaral Back To Top (85363)

Hi Ann,

The only solution for this is to apply report paging, there is no javascript workarounds for incremental loads of the data.

other workaround you can use if your report permits (if it has hierarchy records) is to apply drill down, more info on how to do it here:

https://docs.microsoft.com/en-us/sql/reporting-services/report-design/drilldown-action-report-builder-and-ssrs?view=sql-server-ver15


Monday, April 13, 2020 - 11:30:36 PM - Ann Back To Top (85358)

Hi, I have been reading your posts and this one is very helpful. I have another question I have been looking for a solution for a while without success.

I have a report that needs to display over 2000 rows on one page. The query run very fast (takes less than 1 s) but the report page takes over 10 seconds. I have read numerous posts suggesting pigenating the report such as setting up the Interactive Height to 0 in properties, etc. But my client do not like to navigate the report page by page (does not like to click on page numbers to navigate to the next page). My clients like to display all 2000 rows on ONE PAGE. Unfortunately, SSRS uses html and xml to render the page, making rending 2000 rows on one page too long. I was wondering if you have a get-around to load and display the rows as the page scrolls down, like that in most comtempory browsers? So it does not have to load all 2000 rows all at once.  SSRS is so out of date in this sense. 


Friday, May 24, 2019 - 1:10:24 PM - Timothy Bullard Back To Top (80197)

Greetings

Thanks for the link. Let me clarify the questions

I put a link from a webpage button that includes the rc;toolbae=false command in the link

Works fine the first time it is clicked

Once the report is autorefreshed  the url is changes and does not include the rc:toolbar=false command

Tim


Thursday, May 9, 2019 - 12:40:53 PM - Eduardo Pivaral Back To Top (80007)

†Hi Tim,

It seems there is a workaround for this modifying the .css stylesheet directly on the report viewer code, you can check this article: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cd28fa61-cee8-4a29-be49-8c61144bd6f3/the-rctoolbar-command-is-no-longer-recognized-in-2016?forum=sqlreportingservices

regards,


Thursday, May 9, 2019 - 12:17:54 PM - Timothy Bullard Back To Top (80006)

Greetings

I have a report that autorefreshes I run it with the URL command rc:Toolbar=false. Works the first time you run the repoprt.

The autorefresh URL does not include this command so the toolbar comes back

Is there a solution to this

Thanks

Tim



download





Recommended Reading

Working With Multi-Select Parameters for SSRS Reports

SQL Server Reporting Services Using Multi-value Parameters

Resolving the Maximum Request Length Exceeded Exception in SQL Server Reporting Services

Multi-detail reports using sub reports in SQL Server Reporting Services

Multiple Row Grouping Levels in SSRS Report














get free sql tips
agree to terms