Reporting Services - Limiting Records Per Page

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » Reporting Services - Limiting Records Per...

39 posts, Page 1 of 4

1 2 3 4 »»»

Reporting Services - Limiting Records Per Page

Rate Topic

Display Mode

Topic Options

Author

Message

Matt Brown-315969

Posted Friday, April 25, 2008 9:45 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 31, 2012 3:02 PM
Points: 5, Visits: 49

This is probably an elementary question, but for the life of me I can't decide the proper direction to go with it.

I simply need to create a report which shows only ten records per page. The incoming selection is unknown - may be only a few records, may be hundreds. Regardless of number of pages, must show only ten records per page.

Is there a table limiting function in SSRS that I can use, or should I approach this with a stored procedure?

Just looking for a kick in the right direction.

Thanks!

Post #490774

Jack Corbett

Posted Friday, April 25, 2008 10:12 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

I think you need to do it with an SP as I cannot find a way to break after n records.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #490807

Peter Brinkhaus

Posted Sunday, April 27, 2008 2:33 AM

SSCommitted

Group: General Forum Members
Last Login: Monday, May 20, 2013 11:28 PM
Points: 1,561, Visits: 6,105

Add a group to your report and use the following expression to group on:

=Floor((RowNumber(Nothing) - 1) / 10)

Also set the 'Page break at and' property for the group.

Peter

Post #491113

Jack Corbett

Posted Sunday, April 27, 2008 6:37 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

Excellent Peter, I should have thought of that, too! It's a simple solution.

Post #491119

Matt Brown-315969

Posted Monday, April 28, 2008 11:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 31, 2012 3:02 PM
Points: 5, Visits: 49

Peter Brinkhaus (4/27/2008)

Add a group to your report and use the following expression to group on:

=Floor((RowNumber(Nothing) - 1) / 10)

Also set the 'Page break at and' property for the group.

Peter

Thank you, Peter! After more research we'd come to a similar conclusion using

=Ceiling((RowNumber(Nothing)) / 10)

It is quite reassuring to know we are going along the same path. We've only been using SSRS in this shop for a bit more than a year, migrating from Crystal, and these forums have saved us from frustration many times in the year.

As an aside I should mention that I love this site and these forums. I've been reading here for several years, but this was the first time I ever had to post a question, and the speed of the responses is incredible. You guys are a godsend and your input is priceless.

Matt

Post #491636

Gunjan

Posted Tuesday, May 27, 2008 10:52 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:29 PM
Points: 78, Visits: 252

This solution shows 1 record at time but the next record will be 11th record. so the data will be like it shows 1st record then 11th then 21st and so forth..
any other solution??

Thanks.

Gunjan.

Post #507093

Peter Brinkhaus

Posted Wednesday, May 28, 2008 2:00 PM

SSCommitted

Group: General Forum Members
Last Login: Monday, May 20, 2013 11:28 PM
Points: 1,561, Visits: 6,105

I guess you added one of the expressions mentioned before to the group expression of a detail row (you can right-click on the detail row, select Edit Group... and then add a Group On expression). This results in the behavior you described. What I tried to suggest was to first add a group (right click the detail row and choose Insert Group rather then Edit Group), add the Group On expression and remove the header and footer.

Peter

Post #507995

Gunjan

Posted Wednesday, May 28, 2008 2:16 PM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:29 PM
Points: 78, Visits: 252

Thanks Sir.
It worked...

Thanks.

Gunjan.

Post #508023

Joe Hanson-472266

Posted Thursday, September 25, 2008 11:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 28, 2011 7:46 AM
Points: 1, Visits: 63

Awesome! I had to create a report with basically 1 record per page.
So I want to see a separte form for each customer.

List properties > Grouping and Sorting properties

Add: =Ceiling((RowNumber(Nothing)) / 1) to Group on: Expression list

Post #576277

rxlevine

Posted Thursday, February 19, 2009 11:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 19, 2009 3:40 PM
Points: 1, Visits: 3

I tried that and I got the following error:

A group expression for the list ‘list1’ uses the RowNumber function with a scope parameter that is not valid. When used in a group expression, the value of the scope parameter of RowNumber must equal the name of the group directly containing the current group.

My understanding is that the GROUP ON EXPRESSION is the definition of the group, so it defines the scope. So where do I add the expression =CInt(Ceiling(RowNumber(Nothing)/25))?

The specific steps I take are Edit Details Group Properties of List Box -> Group ON Expression = ?

Post #660660

« Prev Topic | Next Topic »

39 posts, Page 1 of 4

1 2 3 4 »»»

Permissions