Pagination question - retrieve only records for each page

I want to provide paged access to data. Rather than displaying the
entire contents of a report or query to an end user, I want to show
only a subset of records per web page, with controls for moving from
page to page. I have a large result set and only want to selectively
retrieve only those records that should be shown for the particular
page. I know how to do this in ASP.NET but I'm new to Report Server.
Can anyone point me in the right direction?
ThanksThis is default Reporting Services behavior.
RS will give you the functions to move from page to page.
You have to design the report to use parameters, and then add formatting to
the report to make it break over several pages.
If you have the SQL Server 2005 setup available, install the Report Designer
on your workstation and play around with it. (Or download a trial version of
SS 2005) There are lots of good articles to get you going. A good place to
start is with William Pearson's articles about Reporting Services at
http://www.databasejournal.com/article.php/1459531 (scroll down to find
them).
Kaisa M. Lindahl Lervik
"fparc" <fparc@.aol.com> wrote in message
news:1165461353.254431.13270@.j72g2000cwa.googlegroups.com...
>I want to provide paged access to data. Rather than displaying the
> entire contents of a report or query to an end user, I want to show
> only a subset of records per web page, with controls for moving from
> page to page. I have a large result set and only want to selectively
> retrieve only those records that should be shown for the particular
> page. I know how to do this in ASP.NET but I'm new to Report Server.
> Can anyone point me in the right direction?
> Thanks
>|||Thanks for the link. What a great resource. I was thinking of
pagination in terms of record retrieval instead of page breaks. I want
to query 50 records at a time so I don't tax the database. After
looking at it a bit I think I will try to use the TimeStamp field as
the index. So I put a text box that holds the Last timestamp of the
group of TOP 50. I make the textbox a 'Jump to URL' and pass the last
timestamp on the end of the URL to open the new report page. One
problem; I can't get the parameterize URL to work in Report Server. I'm
using the wrox Professional SQL Server 2005 Reporting Services book but
the URL format they give is wrong(a pre-release book no doubt). So now
I need to find a good simple test to see if I can get this working
first. Know of any good tutorials on passing params in URL for Report
Server?|||I got the parameterize URL to work in Report Server. I was wrong about
the wrox Professional SQL Server 2005 Reporting Services book being
wrong. I was using "http://server/SQLReports/Pages/Folder.aspx?"
instead of "http://server/ReportServer?/folder/file" in my Jump URL.
Now I have 12 parameters including the TimeStamp the user can choose
from. By choosing the Allow Blank Value property from the Report
Parameters Dialog Box, the user can fill in all or none of the
parameter text boxes for various searches - except the Timestamp which
defaults to =Now.
My SQL pulls TOP 50 records. When the user clicks on the 'Jump to URL'
text box the timestamp param of the 50th record is passed in the URL to
pull the next 50.
Now I have a type text column filled with XML in which there are 2
things we need to pull - an email and/or an event_code. My next step is
to change the table column from text to the XML type to speed up the
searches on that column. Here's what I've found so far.
http://www.developer.com/db/article.php/3531196
http://www.15seconds.com/issue/050803.htm
http://davidhayden.com/blog/dave/archive/2006/04/11/2909.aspx
http://www.ftponline.com/vsm/2005_06/magazine/features/rjennings/

Paged results from SQL Query?

I have been searching this topic on and for quite some time and can't seem to find a decent answer. Is it feasible to do your paging strictly from a SQL query? Pass the query the pagesize, what page to return and what to sort by?

Hi,

If you are talking about datagrid customer paging, here is a solution I read a few weeks ago.

It is through a SP with dynamic sql statement. Following that article, a user gave another approach without dynamanic sql for a known table. Here is the web link for that article at eggheadcafe.com.

"Custom DataGrid Paging at the Server" by Dr.Bromberg

http://www.eggheadcafe.com/articles/20060109.asp

FYI

If there is not what you are looking for, please post back. ( I don't know whether I inderstand your question correctly about "paging strictly from a SQL query?" )

Regards,

Limno

|||

If you are using 2005 you can, a new ROW_NUMBER() function has been added so you could potentially use it to get rows say from 10 to 20. I have to say I have never used it, but after a quick google:

SELECT Description, Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row>=1 AND Row<=10

I dont know how flexible it is I have to say, but hope that helps!

W

|||That was pretty much exactly what I was looking for. I want to avoid pulling 1000's of results when the user may only be looking at 20 or 30 of them. The other discussions I have read involve building a sorted temporary table with an autonumber column then pulling the page of data from there using < and > on the numbered column. I am concerned that this approach would be bad for performance. The link you have given seems like a very good solution!|||It would be even better if someone could find out how to parameterize the ORDER BY clause, so you can enable sorting on a multi-column table. I've been trying to figure it out for a couple of days, but the CASE syntax breaks down if the columns have different data types...

Paged Result Sets

What is the recommended mechanism for selecting paged results from SQL.

Presently I pass various params including the request Max Items Per Page and the requested page.

The I execute the query as a count with the search params.

Then comes the paging logic, which validates the page number against the request page and number of hits etc.

Then a temp table and record variables are created for the results.

Then I run the query again with a cursor and select the appropriate Items into the temp table based on the paging values (First Item and Last Item).

Then I return the temp table & some additional return params with the Total Hits etc.

The Stored procedure is accessed via an ADO.Net client and the system.data.IDBReader populates a .Net strongly typed collection and is for read only display.

Thanks for any input,

Martin.

hi martin,

i think this is a front end issue not SQL's

vb.net objects like grids, detailsview and formview

supports paging builtin to them without the need of relying

to Sql server.

you can read the data into the dataset and present it with vb using

objects that support paging.

regards,

joey

|||

Hi Joey,

I am more than capable with ADO.Net. This is not a front end issue.

I have say 200,000 records and I want 15 items, page 30 in a result set displaying 15 items per page. I do not wish to return 450 items to a dataset or to a datareader that has to do multiple round trips to the server to get top the records I require.

I want the SPROC to return the 15 items that I am requesting. Do you understand what I am saying taking this in context with what I have said above?

I can presently do this. Though, I am looking for some one of you SQL Pro's to tell how I should be doing it.

|||

edited

hi martin,

sorry just clarifying. Anyway try this

use northwind

create proc pagemynorthwindorders(@.page int)
as
declare @.pagesize int
select @.pagesize=15

select IDENTITY(int, 1,1) AS ID_Num ,str(orderId)as orderid
into #dummyorders from orders
select * from orders where orderid in
(
select orderid from #dummyorders orders
where id_num between (@.page-1)*@.pagesize and (@.page)*@.pagesize
)

exec pagemynorthwindorders 1

regards,

joey

|||

Hello again!

Looking at this this means that I am selecting all - say using Top 1000 - one thousand records into a temp table?

into #dummyorders I am unfamiliar with this syntax.

Inner queries and stuff! Is this more effeective than the Cursor approach? Is it possible you could break out the SPROC above a little with some comments.

It is much appreciated.

|||

no problem

use northwind

create proc pagemynorthwindorders(@.page int)
as
declare @.pagesize int
select @.pagesize=15 -- in case you want to change your paging size

-- create a temp table with its own identity column starting from 1

-- since order id has its own identity i need translate it to a string using str

-- there can only be one identity column in the table

-- i need only the pk (orderid) since it can identify the records i need

-- the #dummyorders record returns my own id_num and the orderid

-- id_num shall be used for paging , orderid is to identify what records

belong to the page

select IDENTITY(int, 1,1) AS ID_Num ,str(orderId)as orderid
into #dummyorders from orders

-- this get the records from orderid that exist in the required page

select * from orders where orderid in
(
select orderid from #dummyorders orders
where id_num between (@.page-1)*@.pagesize and (@.page)*@.pagesize
)

--(@.page-1)*@.pagesize+1 and (@.page)*@.pagesize

let say you want page 2 it gets the record between

-- (2-1)*15+1 and (2*15)

-- between 16 and 30 which is actually page 2

--at the end of the procedure #dummyorders destroys itself

exec pagemynorthwindorders 1

|||

final query, just improve it to suite you needs

use northwind

alter proc pagemynorthwindorders(@.page int)
as
declare @.pagesize int
select @.pagesize=15
select IDENTITY(int, 1,1) AS ID_Num ,str(orderId)as orderid
into #dummyorders from orders
select * from orders where orderid in
(
select orderid from #dummyorders orders
where id_num between (@.page-1)*@.pagesize+1 and (@.page)*@.pagesize
)

go

exec pagemynorthwindorders 2

|||

This is a very common question and there are actually many good answers.

If you are using SQL2005 there are two really cool features you could use:

- the tops now accept a variable: select top @.variable

- the ROW_NUMBER function, that creates an additional column with the ID of the row

Try this:

declare @.ipagesize as int,

@.ipage as int

set @.ipagesize = 5

set @.ipage = 0

select top ( @.ipagesize ) *

from

(

select ROW_NUMBER () over ( order by orderID ) as row_order , *

from orders

)

ordered_set

where ordered_set.row_order > @.ipagesize * @.ipage

|||

Thanks people,

Both are cleaner than my current solution.

Though, is there a way to get an estimate of the total hits from the same statement or must I use an additional count?

Also, I know there is a knew Table or .Table statement for SQL 2005 which I thought may have popped up in the answers. The Top Param non variable limitation was an anoying so that is also valuable info.

|||new!