Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reporting Services - Limiting Records Per Page


Reporting Services - Limiting Records Per Page

Author
Message
Matt Brown-315969
Matt Brown-315969
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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!
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11022 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Peter Brinkhaus
Peter Brinkhaus
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1677 Visits: 7082
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
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11022 Visits: 14858
Excellent Peter, I should have thought of that, too! It's a simple solution.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Matt Brown-315969
Matt Brown-315969
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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
Gunjan
Gunjan
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 309
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.
Peter Brinkhaus
Peter Brinkhaus
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1677 Visits: 7082
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
Gunjan
Gunjan
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 309
Thanks Sir.
It worked...

Thanks.

Gunjan.
Joe Hanson-472266
Joe Hanson-472266
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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
rxlevine
rxlevine
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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 = ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search