SQLTeam.com | Weblogs | Forums

SSRS automatic scrolling


I have an SSRS report with multiple pages. Is it possible to automate scrolling or auto next page my SSRS report?


Hi, Conrad
When you want to get the preview of the report,you need to scroll and turn the page manually. SSRS does not do that natively.

Using a trick, this is possible to use the auto refresh the report. You can use the counter to show different things. Sub reports or using the Hidden property to show/hide certain parts are an option too.
First, you need to set up a table in a database somewhere:

CREATE TABLE dbo.ReportRefresh(
	RefreshCounter int NULL

insert into dbo.ReportRefresh select 0;

Then you need the following code that return your data in dataset:

declare @maxCount int = 10;
declare @currentCount int = (select ISNULL(RefreshCounter, 0) from dbo.ReportRefresh);
declare @nextCount int = (select (@currentCount % @maxCount)+1);

update dbo.ReportRefresh set RefreshCounter = @nextCount;

select @nextCount Cnt;

This code limits the counter to 10 and will switch back to 1 once 10 has been reached.

Depending on how you decide to implement this system you may want to replace the SELECT statement with one that actually returns your data, possibly in combination with IF or CASE statements. Alternatively, you can just return the count value and work with that in the report itself or combination of both.

Please note that in BIDS preview it will seem as if this system does not work. That is because BIDS uses cached data files. Once the report deployed to the server, it will reload the data set on each Auto Refresh action.