Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Services SSRS Auto Refresh Report


By:   |   Updated: 2019-02-25   |   Comments (3)   |   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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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 09, 2019 - 12:40:53 PM - Eduardo Pivaral Back To Top

 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 09, 2019 - 12:17:54 PM - Timothy Bullard Back To Top

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


Learn more about SQL Server tools