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.
Then we will add a new dataset with the following T-SQL, this will generate a simple random number.
SELECT RAND() as [RandNumber];
Then we add the field to our report, you can use just a drag and drop for this, since it is a scalar value.
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.
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).
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).
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:
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.
Save the report and navigate to Preview mode. You should start seeing the chart being populated, here is the first execution.
After a few executions you should see something like this.
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.
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.
After more than 20 executions, just the last 20 items are maintained and displayed.
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.
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.
