Pagination without datagrid

I'm working on a website where we're using .Net web services to feed data to a Flash front-end. The site will have a comments section and we want to display 15 or so comments per page with 'back' and 'next' functionalitiy. We'd also like to show the number of pages of comments and highlight the page they're on. The standard stuff that datagrids do so well.

How can this be accomplished without a datagrid?

There's a good page that explains a number of ways to do this using classic ASP. Some of the solutions they implement I can most likely use with .Net.

http://www.aspfaq.com/show.asp?id=2120

But I wanted to ask the community. How to paginate without a recordset? Sql Server 2000 back-end, ASP.NET 1.x

Thanks.Why don't you want to use a datagrid?|||We aren't using a DataGrid because our 'front-end' isn't an aspx page it's a Flash application that we're feeding the data to via a webservice.

Correct me if I'm wrong but I don't think a DataGrid is an option with this situation.|||

I see. Sorry, I missed that part. Well, if you are using web services to a flash front end, I would assume that your data isn't going to change very often. I would see about getting your data into a dataset, formatting the output into a cache object with a sqldependancy.

Beyond that, I would have to know a lot more about how your web service interacts with your flash front end.

Pagination with SP

I'd like to write an SP with parameters that returns specified groups rows
in ordered groups of arbitrary size. Kind of a "sliding" TOP. Like, show me
the first 40 items in a list, show me the 4th group of 40 items in a list,
show me the Nth group of X items, etc. I see this kind of thing all the time
and would prefer not to have to deal with the pagination in VB or ASP, or
get all the rows and then filter out what I don't want to display.
So, I'd like to pass in to the SP parameters for the size of each grouping
and an ordinal for the group I wanted.
CREATE PROCEDURE pagination_sp
@.ordinal int,
@.groupsize int
AS
SELECT LastName, FirstName
FROM People
ORDER BY LastName, FirstName
Thanks for any tips on how to approach this.Don
http://www.aspfaq.com/show.asp?id=2120
"Don Miller" <nospam@.nospam.com> wrote in message
news:%23oKvuTlSFHA.2784@.TK2MSFTNGP12.phx.gbl...
> I'd like to write an SP with parameters that returns specified groups rows
> in ordered groups of arbitrary size. Kind of a "sliding" TOP. Like, show
me
> the first 40 items in a list, show me the 4th group of 40 items in a list,
> show me the Nth group of X items, etc. I see this kind of thing all the
time
> and would prefer not to have to deal with the pagination in VB or ASP, or
> get all the rows and then filter out what I don't want to display.
> So, I'd like to pass in to the SP parameters for the size of each grouping
> and an ordinal for the group I wanted.
> CREATE PROCEDURE pagination_sp
> @.ordinal int,
> @.groupsize int
> AS
> SELECT LastName, FirstName
> FROM People
> ORDER BY LastName, FirstName
> Thanks for any tips on how to approach this.
>

Pagination recommendations

I am a PHP programmer for a small startup. We are storing person records and our MS SQL Server 2000 database has grown to the point where we wish to paginate the data before returning it to my PHP scripts.

I was wondering if anyone has any recommendations on an optimal way to manage this given the following requirements.

- Data must return only X number of rows at a time (user configurable).
- Must be able to search by several diffent criteria (name, date, birthday, location, ...)

Also, I was wondering if it is possible to return the total number of existant rows of data as the first row of a MSSQL procedure.Originally posted by shauns
Also, I was wondering if it is possible to return the total number of existant rows of data as the first row of a MSSQL procedure.

What does that mean?

Did you do a search of this site? Either here or sql team (or both) I know we've bantered this one about a couple of times...|||Here's one link...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33472&SearchTerms=paging

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/

Pagination Question

I have a report that groups information by employee. When the report is first
rendered on the server (i.e. HTML format) the data flows over to a 2nd page
for this person. The 2nd page does not have the header defined so there is no
way to tell which employee this is for. One if the solutions is to length the
page, but this will only work to solved this one issue. It is not a good long
term solution.
Question: How do I either a) Make the HTML page length as long as is needed
for a particular page or b) Get the header to really appear on every page?Hope this tidbit helps
a) Make the HTML page length as long as is needed
for a particular page
if your report only has one group then throw your fields inside the list
control and put this formula in the group expression
=Ceiling(Rownumber(Nothing)/20) '20 for example number of pages'
b) Get the header to really appear on every page?
Use the Table Object in which case you can select the option to repeat the
header on every page.
--
Message posted via http://www.sqlmonster.com|||a) My report has multiple groups (I think - Newbie). Information is paginated
by employee. For each employee information is grouped by year.
b) Can you give me some more information about the table object please?
Patrick
"Paul Mauriello via SQLMonster.com" wrote:
> Hope this tidbit helps
>
> a) Make the HTML page length as long as is needed
> for a particular page
> if your report only has one group then throw your fields inside the list
> control and put this formula in the group expression
> =Ceiling(Rownumber(Nothing)/20) '20 for example number of pages'
> b) Get the header to really appear on every page?
> Use the Table Object in which case you can select the option to repeat the
> header on every page.
> --
> Message posted via http://www.sqlmonster.com
>

Pagination of data

Hi
I am developing a vb2005/sql server 2005 winform app which involves
displaying records in a list, one page at a time. The total number of
records is large. I am wondering if there is a way either in vb/ado or sql
server that automatically pages a certain number of records at a time and
when user scrolls down (or up) pages the next set of records? I guess I can
possibly program it manually but it may be complicated specially when the
records in the next/previous set are different due to the different sort
orders. Ideally I am looking for giving a select statement to include all
records as data source and then expect system to handle any pagination and
bringing only one page of record from server at any one time.
Thanks
RegardsTake a look at this article.
http://www.aspfaq.com/show.asp?id=2120
David Portas
SQL Server MVP
--|||I am doing it for a winform app and asp may not be relevant but I will have
a look.
Thanks
Regards
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IZidne6mqKZJLJreRVn-ow@.giganews.com...
> Take a look at this article.
> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas
> SQL Server MVP
> --
>|||Of the various solutions given, most of them are not ASP-specific. Mostly
they use TSQL.
David Portas
SQL Server MVP
--|||Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>|||Check out:
http://www.aspfaq.com/show.asp?id=2120
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Phil G." <Phil@.nospam.com> wrote in message
news:de9hlf$9fl$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>

Pagination of data

Hi
I am developing a vb2005/sql server 2005 winform app which involves
displaying records in a list, one page at a time. The total number of
records is large. I am wondering if there is a way either in vb/ado or sql
server that automatically pages a certain number of records at a time and
when user scrolls down (or up) pages the next set of records? I guess I can
possibly program it manually but it may be complicated specially when the
records in the next/previous set are different due to the different sort
orders. Ideally I am looking for giving a select statement to include all
records as data source and then expect system to handle any pagination and
bringing only one page of record from server at any one time.
Thanks
RegardsTake a look at this article.
http://www.aspfaq.com/show.asp?id=2120
--
David Portas
SQL Server MVP
--|||I am doing it for a winform app and asp may not be relevant but I will have
a look.
Thanks
Regards
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IZidne6mqKZJLJreRVn-ow@.giganews.com...
> Take a look at this article.
> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas
> SQL Server MVP
> --
>|||Of the various solutions given, most of them are not ASP-specific. Mostly
they use TSQL.
--
David Portas
SQL Server MVP
--|||Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>|||Check out:
http://www.aspfaq.com/show.asp?id=2120
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Phil G." <Phil@.nospam.com> wrote in message
news:de9hlf$9fl$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>

Pagination of data

Hi
I am developing a vb2005/sql server 2005 winform app which involves
displaying records in a list, one page at a time. The total number of
records is large. I am wondering if there is a way either in vb/ado or sql
server that automatically pages a certain number of records at a time and
when user scrolls down (or up) pages the next set of records? I guess I can
possibly program it manually but it may be complicated specially when the
records in the next/previous set are different due to the different sort
orders. Ideally I am looking for giving a select statement to include all
records as data source and then expect system to handle any pagination and
bringing only one page of record from server at any one time.
Thanks
Regards
Take a look at this article.
http://www.aspfaq.com/show.asp?id=2120
David Portas
SQL Server MVP
|||I am doing it for a winform app and asp may not be relevant but I will have
a look.
Thanks
Regards
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IZidne6mqKZJLJreRVn-ow@.giganews.com...
> Take a look at this article.
> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas
> SQL Server MVP
> --
>
|||Of the various solutions given, most of them are not ASP-specific. Mostly
they use TSQL.
David Portas
SQL Server MVP
|||Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>
|||Check out:
http://www.aspfaq.com/show.asp?id=2120
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Phil G." <Phil@.nospam.com> wrote in message
news:de9hlf$9fl$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>

Pagination Issue when Rending to PDF

I am having a pagination issue with a report when I render to PDF. The
report is a patient form that is based on a single record that is returned
from a stored procedure. Most of the records are output fine, but some
records are not paginating properly and leave 3/4 page of blank space. The
field that follows on the next page is short and should definitely fit on
that page. The report renders fine in HTML, but the requirement is a PDF
format.
The body of the report is contained in a table. Each field of the report is
contained in its own row. If the field is blank, the entire row is hidden so
that only populated data is output in the report.
In troubleshooting this, I have removed the logic that hides the rows, but
the pagination is still not correct.
I have also made sure that the data is clean and doesn't have whitespace.
Any suggestions as to how to fix this problem?
TIAOn Dec 18, 8:47 pm, JC <J...@.discussions.microsoft.com> wrote:
> I am having a pagination issue with a report when I render to PDF. The
> report is a patient form that is based on a single record that is returned
> from a stored procedure. Most of the records are output fine, but some
> records are not paginating properly and leave 3/4 page of blank space. The
> field that follows on the next page is short and should definitely fit on
> that page. The report renders fine in HTML, but the requirement is a PDF
> format.
> The body of the report is contained in a table. Each field of the report is
> contained in its own row. If the field is blank, the entire row is hidden so
> that only populated data is output in the report.
> In troubleshooting this, I have removed the logic that hides the rows, but
> the pagination is still not correct.
> I have also made sure that the data is clean and doesn't have whitespace.
> Any suggestions as to how to fix this problem?
> TIA
You will want to check the width of the report. Normally, if it is
wider than 6.5" in design view, it will wrap once exported to PDF.
Also, if you are using a table control, you will want to put it into a
rectangle control, this should shrink the extra space. Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks for the quick reply. The report is slightly wider than 6.5" in design
mode to incorporate the header (which renders fine). The fields before and
after the blank space are very short (like 1 word and a label).
I did try putting the table in a table control, but that didn't do anything.|||On Dec 18, 9:43 pm, JC <J...@.discussions.microsoft.com> wrote:
> Thanks for the quick reply. The report is slightly wider than 6.5" in design
> mode to incorporate the header (which renders fine). The fields before and
> after the blank space are very short (like 1 word and a label).
> I did try putting the table in a table control, but that didn't do anything.
You're welcome. Did you mean you put it into a table control or a
rectangle control?
Enrique Martinez
Sr. Software Consultant|||Sorry. I meant that I put the table control inside the rectangle control. I
just removed all code that controls the visibility property for each row, and
the pagination problem went away.
The problem is that the customer only wants rows of the table to appear that
have data. Here's the code on the visiblity property of the tablerow:
=iif(Fields!FirstSignature.Value = string.empty, True, False)
It does work as it is supposed to, but it seems that the space is still
being allocated for the row.
BTW, it was initially written in VS 2003, but I have tried with VS 2005 with
the same results.|||On Dec 18, 11:05 pm, JC <J...@.discussions.microsoft.com> wrote:
> Sorry. I meant that I put the table control inside the rectangle control. I
> just removed all code that controls the visibility property for each row, and
> the pagination problem went away.
> The problem is that the customer only wants rows of the table to appear that
> have data. Here's the code on the visiblity property of the tablerow:
> =iif(Fields!FirstSignature.Value = string.empty, True, False)
> It does work as it is supposed to, but it seems that the space is still
> being allocated for the row.
> BTW, it was initially written in VS 2003, but I have tried with VS 2005 with
> the same results.
You might try =IIF(Fields!FirstSignature.Value = Nothing, True, False)
or =IIF(IsNothing(Fields!FirstSignature.Value), True, False)
Either should do what you want. Make sure you set the property for
the row and not the individual cells/textboxes.|||Thanks, Toolman.
I will try that too. Is using the table + row format to hide empty fields
the best practice to accomplish this? I was also going to try to set the
height to 0 if it was going to be hidden. It just seems that SSRS is taking
the empty rows into consideration when making the pagination decision.|||On Dec 19, 2:32 pm, JC <J...@.discussions.microsoft.com> wrote:
> Thanks, Toolman.
> I will try that too. Is using the table + row format to hide empty fields
> the best practice to accomplish this? I was also going to try to set the
> height to 0 if it was going to be hidden. It just seems that SSRS is taking
> the empty rows into consideration when making the pagination decision.
I believe that it is. If for no other reason than the convenience of
not having to enter the expression for each textbox/cell. Also, my
experience has been that using a table rather than a collection of
boxes just works better in general. I know that trying to hide the
entire row by hiding all the cells doesn't seem to close up the white
space.

Pagination issue in SQL SERVER reporting services 2005

Issue: Page breaks in the following kind of reports are ignored :
Reports having matrix controls
Sub-reports having conditional visibility
Scenario:
What happens is that if a letter has 3 pages then in report viewer it is
showing all the 3 pages in 1 page itself. But it would actually show 3 pages
in print preview. So this problem is only when the report is viewed on the
screen and not with the print preview. So while printing it takes 3 pages
which is fine. But the display on report viewer control is the issue.
Investigation:
The following are a couple of approaches tried :
1. Use the expression =Int((RowNumber(Nothing)-1)/25)
2. Place your matrix inside a â'listâ' control and add a grouping with
the following expression =Ceiling(RowNumber(Nothing)/20)
3. Installed SQL Server 2005 service pack 2 and checked if this can
solve the problem. But it did not help.
Can someone tell me how to fix this pagination issue ?I see nobody has responded to this, so I'm going to throw in my six-penny
worth - but I'm relatively new to Reporting Services, so please don't take
this as gospel. I have observed similar behaviour myself, and this is using a
list control rather than a matrix. I came to the conclusion that it was just
part of the behaviour of the report viewer. In fact if you are trying to lay
out a report to a specific design the report viewer can be a very frustrating
tool. In my case it didn't matter since we had already taken the decision
that all reports were going to be in PDF format, and this displays everything
just fine. I would recommend that you do the same.
John
"selva" wrote:
> Issue: Page breaks in the following kind of reports are ignored :
> Reports having matrix controls
> Sub-reports having conditional visibility
> Scenario:
> What happens is that if a letter has 3 pages then in report viewer it is
> showing all the 3 pages in 1 page itself. But it would actually show 3 pages
> in print preview. So this problem is only when the report is viewed on the
> screen and not with the print preview. So while printing it takes 3 pages
> which is fine. But the display on report viewer control is the issue.
> Investigation:
> The following are a couple of approaches tried :
> 1. Use the expression =Int((RowNumber(Nothing)-1)/25)
> 2. Place your matrix inside a â'listâ' control and add a grouping with
> the following expression =Ceiling(RowNumber(Nothing)/20)
> 3. Installed SQL Server 2005 service pack 2 and checked if this can
> solve the problem. But it did not help.
>
> Can someone tell me how to fix this pagination issue ?

Pagination Issue

I have a report which contains a list box, which in turn contains a
subreport. The list box is set up to group on one of the data set's columns.
There is only one row per group column value. I have not forced new pages
on the beginning and ending of the list box, nor have I specified to keep the
group together. The report was created and previewed using the report
designer, then was deployed. When rendered, the text box group and the sub
report works correctly. The issue is pagination.
When I first render the report, several groups are printed on a single
"Page". When I press the preview button again (in report designer) or export
the report to PDF (in report server), each group and subreport appears on one
page (which is what I want), but a new blank page now appears between groups.
How can I make this report paginate correctly to show 1 group per page and
no blank pages?
--
Jay P. Meredith
Senior Software Engineer
Columbia Helicopters, Inc.
PO Box 3500
Portland, OR 97208Jay,
Go into the layout tab of the report, then go up to the Report -> Report
Properties menu. Check your paper size & margins. Also, verify the width
and height of your list box. It could be that RS has re-sized your report to
fit data on it (or just to fit your list box without warning you - it's
really bad about that) and that is causing the extra pages to print. Also,
look at your padding values and border values for the list box and play with
them just to make sure they aren't causing the extra space to be generated
when the report is generated.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Jay Meredith" wrote:
> I have a report which contains a list box, which in turn contains a
> subreport. The list box is set up to group on one of the data set's columns.
> There is only one row per group column value. I have not forced new pages
> on the beginning and ending of the list box, nor have I specified to keep the
> group together. The report was created and previewed using the report
> designer, then was deployed. When rendered, the text box group and the sub
> report works correctly. The issue is pagination.
> When I first render the report, several groups are printed on a single
> "Page". When I press the preview button again (in report designer) or export
> the report to PDF (in report server), each group and subreport appears on one
> page (which is what I want), but a new blank page now appears between groups.
> How can I make this report paginate correctly to show 1 group per page and
> no blank pages?
> --
> Jay P. Meredith
> Senior Software Engineer
> Columbia Helicopters, Inc.
> PO Box 3500
> Portland, OR 97208|||Messing with the margins and object sizes did the trick. I also have a
better understanding about SRS's preview pane in the Report Designer. The
first time you preview a report, the preview is in HTML mode, which does not
support pagination, therefore pagination is not shown. If you click the
preview button a second time, it switches to paged mode, which reflects how
the report looks when exported to a format capable of pagination, such as PDF.
Thanks much for your help.
--
Jay P. Meredith
Senior Software Engineer
Columbia Helicopters, Inc.
PO Box 3500
Portland, OR 97208
"Catadmin" wrote:
> Jay,
> Go into the layout tab of the report, then go up to the Report -> Report
> Properties menu. Check your paper size & margins. Also, verify the width
> and height of your list box. It could be that RS has re-sized your report to
> fit data on it (or just to fit your list box without warning you - it's
> really bad about that) and that is causing the extra pages to print. Also,
> look at your padding values and border values for the list box and play with
> them just to make sure they aren't causing the extra space to be generated
> when the report is generated.
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
> @.=)
>
> "Jay Meredith" wrote:
> > I have a report which contains a list box, which in turn contains a
> > subreport. The list box is set up to group on one of the data set's columns.
> > There is only one row per group column value. I have not forced new pages
> > on the beginning and ending of the list box, nor have I specified to keep the
> > group together. The report was created and previewed using the report
> > designer, then was deployed. When rendered, the text box group and the sub
> > report works correctly. The issue is pagination.
> > When I first render the report, several groups are printed on a single
> > "Page". When I press the preview button again (in report designer) or export
> > the report to PDF (in report server), each group and subreport appears on one
> > page (which is what I want), but a new blank page now appears between groups.
> > How can I make this report paginate correctly to show 1 group per page and
> > no blank pages?
> >
> > --
> > Jay P. Meredith
> > Senior Software Engineer
> > Columbia Helicopters, Inc.
> > PO Box 3500
> > Portland, OR 97208

Pagination is not working

Hi,

I have a problem with paging in my report. My report is quite simple, I have a table with two grouping levels. Paging is not appearing besides there is a lot of rows displayed.

When I change the report to use only one grouping level, the paging works fine. Is paging being calculated on the first grouping level ? I have about 10 to 20 rows at the first level but about 1500 rows on the second one.

Any help will be appreciated.

Hi,

Where did you check the paging? Within your Visual Studio or for a deployed report? This can sometimes differ. Paging is not based on specific groups.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

I check both in BIDS and in Browser.

Pagination is supposed to be calculated based on the number of rows returned isn't it ?

Thanks for your answer.

|||

Taken from the MSDN site:

Pagination varies for each rendering format. For example, Adobe Acrobat (PDF), pagination is based on paper size. HTML pagination is not based on physical dimensions. Pages are separated by page breaks that you add to a report, but the actual length may vary from page to page. Check the Product Catalog sample report to view an example of pagination in HTML format. For more information about pagination, see Controlling Report Pagination.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||I don't understand if I can't handle paging in HTML how can I display reports with large amounts of rows ?

Pagination in SSRS 2000

Hi,

I am newbie to SSRS and am currently using RS 2000.

What i came across while developing reports is :

Reports when rendered in Web-format were split in a number of pages if the number of rows in the table used for displaying were more than could be contained in a single page's height.

However, in the Layout section when i gave some (any) expression for the visibility of the table depending on some condition this paging got disabled, i.e. the entire report started coming in one page irrespective of how many ever rows were displayed. They all started getting displayed in a single page with a vertical scroll.

I do not understand what has the table's visibility expression got to do with displaying report in a single page or multiple.

Please comment

Unfortunately, since RS (2000 and 2005) doesn't support conditional page breaks any conditional expression supresses the page breaks as well. Conditional page breaks are on the wish list for the next release.|||

But here i was not giving any conditional page breaks.

Do you mean to say that conditional visibility expression of the Table object suppressed the default page breaks?

Has anyone else come across this?

|||Yes, I know that it doesn't make much sense but this is exactly what's happening.

Pagination Help Please

I have a report that has several tables. Each table is to begin on a new page. When I preview the report with the report viewer everything is on one page. When I go to print preview I get the desired page breaks. How do I get the page breaks without going to print preview? Is there a config file setting? Right now, for each table, I have checked the "Insert a page break before this table" box. I have also tried the "Insert a page break after this table" without success.

Any help would be appreciated.

Thanks

-JW

In general, page breaks explicitly set on Tables should be consistent between renderers.

Do you have conditional visibility set on any of the tables? Page breaks on conditionally visible report items is not supported.

-chris

|||

Thanks Chris, that change made the page breaks work. Now it prints a blank page for each tables without data.

-JW

Pagination for Large data

I want to build a system that will have about 1 million rows in a
table in sql server database.I am using this for a web application and
accessing it via JDBC type 4 driver.But display 20 records at a time
only using pagination(as in google).What will be the best way to go
about this.On 10 Oct 2003 04:04:11 -0700, nik_sharma75@.hotmail.com (Nikhil
Sharma) wrote:

>I want to build a system that will have about 1 million rows in a
>table in sql server database.I am using this for a web application and
>accessing it via JDBC type 4 driver.But display 20 records at a time
>only using pagination(as in google).What will be the best way to go
>about this.

Figure how many anyone is actually ever going to page through.
Obviously no one is going to page through to the end. How many google
pages would you look at before refining your search? Then construct
your query and restrict it with TOP. Cache the results and handle the
paging in your app.

Pagination does not work after Table Visiblity false.? Help : Urgent ? Please

Hi,
You know I was trying to create new report and found that works fine with
Pagining- URL Access.
But I want to hide report when there is not data return. In that case my
pagining does not work.
For hiding my table when no data return i do following steps:-
Table -> Properties-> Visiblity Tag-> Expression ->
IIF(count(Fields!FormName.Value)=0,True,False)
Which hide my table when no data return, but paginition also does not work.
Please Help me. What shall i proceed with these things..
Waiting for Response.
Thanks
Labhesh S
Bangalore

In This.

1. I want to hide my details table which return data , when there is no data returned.

2. I want pagination should work when there is data. But is it not happening. ?

3. Pagination is not happening because i have written code in expression for Hiding the table when there is no data.

But i need Pagination also.

Please help..

Labhesh Shrimali

Bangalore

|||

HTML renderer and Preview (which are soft page break renderers) will ignore page breaks of conditionally hidden items and their children.

PrintPreview , Image and PDF renderers (which are physical page break renderers) will respect these page breaks.

If there is no data for the table, than we will render:

NoRowsMessage property if you defined one

or

table header and footer

What is the case for you? What are trying to achieve by hidden the table?

If you are in case 2 and you don't want the header and footer, you can set their visibility instead of the table.

Thank you,

Nico

Pagination Control

Hi Ppl
I have alot of headache with pagination control on reporting services. Is
there a way to control how many groups/items/rows in one page?
Is there a way to show all items on 1 big page, instead of paging them? I've
try to set the report hieght, but that doesn't help at all.
If pagination must happen, then is there a way to search through all pages
for a perticular string?
Lastly is there a way to stop a group being seperated into 2 pages, (I know
you can have 1 group per page, but thats not what i want). The height of the
group are dynamic, so i can adjust the height group with the report size to
make them not break.See Inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gMaster" <gMaster@.discussions.microsoft.com> wrote in message
news:8EBFCE2B-F794-4550-AB86-FB3F6058A0AA@.microsoft.com...
> Hi Ppl
> I have alot of headache with pagination control on reporting services. Is
> there a way to control how many groups/items/rows in one page?
I have placed 2 examples in the downloads section for reporting services->
RDL on
http:://www.msbicentral.com
They are called PagedTableParameters (This uses a Parameter to allow dynamic
selection of the number of rows which should be placed on a page... nice...
uses Northwind.) and PageBreakSample.RDL (For Beginners, take a look at the
page breaks on each grouping. Uses the Northwind Data Source.)
> Is there a way to show all items on 1 big page, instead of paging them?
I've
> try to set the report hieght, but that doesn't help at all.
Simply do not turn on page breaks for the groups, etc... IN html you will
get one long page. when you export to PDF however you will get pages (
because you will print on to actual paper.)
> If pagination must happen, then is there a way to search through all pages
> for a perticular string?
IN html there is a Find textbox at the top with the parameters after you run
a report, simply type in the text you wish to find...
> Lastly is there a way to stop a group being seperated into 2 pages, (I
know
> you can have 1 group per page, but thats not what i want). The height of
the
> group are dynamic, so i can adjust the height group with the report size
to
> make them not break.
There is a group property called Keeptogether( I tink that is the name) turn
this property on...|||Hi,
I kinda have the same prob. I also want everything on one page. There's no
grouping / page breaking or whatever in my rdl. I Have 1 table with +/- 500
rows, and i want them all on one page. I do use the reportviewer control, so
on top, you can see "page x of x". Perhaps it's a setting there ?
Thanks
"Wayne Snyder" wrote:
> See Inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "gMaster" <gMaster@.discussions.microsoft.com> wrote in message
> news:8EBFCE2B-F794-4550-AB86-FB3F6058A0AA@.microsoft.com...
> > Hi Ppl
> > I have alot of headache with pagination control on reporting services. Is
> > there a way to control how many groups/items/rows in one page?
> I have placed 2 examples in the downloads section for reporting services->
> RDL on
> http:://www.msbicentral.com
> They are called PagedTableParameters (This uses a Parameter to allow dynamic
> selection of the number of rows which should be placed on a page... nice...
> uses Northwind.) and PageBreakSample.RDL (For Beginners, take a look at the
> page breaks on each grouping. Uses the Northwind Data Source.)
>
> > Is there a way to show all items on 1 big page, instead of paging them?
> I've
> > try to set the report hieght, but that doesn't help at all.
> Simply do not turn on page breaks for the groups, etc... IN html you will
> get one long page. when you export to PDF however you will get pages (
> because you will print on to actual paper.)
> >
> > If pagination must happen, then is there a way to search through all pages
> > for a perticular string?
> IN html there is a Find textbox at the top with the parameters after you run
> a report, simply type in the text you wish to find...
> >
> > Lastly is there a way to stop a group being seperated into 2 pages, (I
> know
> > you can have 1 group per page, but thats not what i want). The height of
> the
> > group are dynamic, so i can adjust the height group with the report size
> to
> > make them not break.
> There is a group property called Keeptogether( I tink that is the name) turn
> this property on...
>
>

pagination advice

Hi

I'm still quite new to MSSQL so excuse the trivial questions.
I've already tried searching through the forums on pagination as im sure its a big subject but couldn't find the answers i'm after.

I'm using MSSQL 2000 and asp .net 2.0 and basically need to paginate my results. My database contains roughly 250,000 rows of data, and one query would approximately return 30 results at the most. I would want to have 5 results per page.

I'm starting from scratch so was wondering what technique i could use. I can't use OFFSET as thats MySQL, is there an equivalent?
I've heard mixed opinions on Cursors, but so far thats the only way i can see at the moment! :S

thanksNo cursors, please.

Use a query that assigns an ordinal value to each record in the recordset (you will need a unique column or group of columns by which you can reliably order the results).

Then have your interface submit a request for a result set returning only rows N through M.

Pagination

I have a report which consists of a list item, in which is a rectangle containing a header (as a subreport, but I think that's not relevant) and a subreport. The aforementioned rectangle is specified to include a pagebreak after each occurrence, but that pagebreak is not working as expected.

When I run the report I get 1 subreport on the first page, and exactly two on each subsequent page. Other than the pagination, I am getting very close to what I want. It's too bad that the subreport's pagination is ignored, but I can live with that for now.

Things I've already tried:

The report's pageSize and interactiveSize are both set to landscape

The subreport's report's pageSize and interactiveSize are both set to the actual size that the subreport takes up on the main report (which is ignored anyway)

The list does not have any of the page break options selected.

Any Ideas?

Are you using any visibility condition for subreport or rectangle?

I am asking this becasue page break is not supported with visibility condition.

|||

Thanks for your help.

I have not set any visibility settings away from the default of visible.

|||

Maybe it would help if I explained more of what I'm trying to do...

A single report is run from a single id, but there are several ways of selecting that ID, and some ways should result in batches of reports being generated. (for example, a rep may want to run one report to be mailed to each of their clients). Each report should have just one header at the beginning of it.

My report consists of one big list. In the list there are two subreports, one for the header that should be at the top of each report, and one for the body of the report itself. The list uses a query that returns the IDs that should be included in the batch as its dataset. The ID field is passed as one of the report parameters to the body subreport, with the rest of the parameters for the subreport passed straight through.

I put both of the subreports inside a rectangle (inside the list) to try to force the pagebreak.

<Edit: I removed the bit about table headers in the subreport, because that seems to be working>

|||

I hate to keep bumping my thread, but I need help figuring this out.

Maybe I am making my process too complicated? Is there another way to do what I'm trying to do that will allow the report server to correct the pagination?

Is there more info I could give that would help find a solution?

Pagination

Hi,

I have recently moved into ms sql from mysql and having problems with

finding a query to help paginate my search results. In mysql I would

use:

SELECT * FROM tablename WHERE something = something LIMIT 0,10

what would a similar query be in sql server?

From what I have experienced so far I know this is going to be a big query!

Thanks in advance

Steve209 views and no replies!!

I went out and managed to find a solution on my own.

cheers for all the help|||

can you paste the solution. i will help me too..

cheers

|||Yeah sure,
SELECT TOP $records_per_page * FROM tbl_name WHERE field LIKE '$getID'
AND id NOT IN (SELECT TOP $page id FROM tbl_name)
This is the dymanic version. In a nutshell;
SELECT TOP $records_per_page* FROM answers - selects the first 'n' records from table

AND id NOT IN (SELECT TOP $page id FROM answers) - id has to be a

unique identifier, NOT IN is declaring the offset; where id is not in

the top $page of this table
so
SELECT TOP 10 * FROM tbl_name WHERE field LIKE 'steve'
AND id NOT IN (SELECT TOP 20 id FROM tbl_name)
will return records 11-20 (presuming there are that many records)
alternatives can be found here