As I am working on SSRS reports (.rdl), I need to do pagination within reports. Usually for pagination as a asp.net developer, I add few statements in my procedure with page num and rows logic.
But as my SSRS report is also using a store procedure, do I have to do the same here? But how can i be able to handle the events then? by events i mean to say "Next page" "back page" etc. Is there any inbuilt feature for SSRS which can handle pagination. I am talking about pagination on server side as my report can be a huge and I am selecting all the records at once using procedure. could be like below:
Create procedure myproc
@year int,
@docid varchar(200)
Begin
Declare @commandtext nvarchar(max)
set @commandtext = 'select docid, year, type,
sum(case when statusid = 231 then 1 else 0 end) as granted,
sum(case when statusid = 232 then 1 else 0 end) as revoked,
sum(case when statusid = 235 then 1 else 0 end) as deleted,
sum(case when statusid = 236 then 1 else 0 end) as others
from table1 where 1=1'
if (@year <> 0 and @year is not null)
set @commandtext = @commandtext + 'and year=''' + CONVERT(varchar(10),@year) + ''''
if(@docid is not null and @docid <>'')
set @commandText = @commandtext + 'and documents in (' + @docid + ')'
set @commandText = @commandtext + 'group by docid, year, type '
EXEC (@commandText);
End
