Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Reporting Services - Limiting Records Per Page Expand / Collapse
Author
Message
Posted Friday, April 25, 2008 9:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, April 25, 2008 10:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
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
Posted Sunday, April 27, 2008 2:33 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:09 PM
Points: 1,602, Visits: 6,673
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
Posted Sunday, April 27, 2008 6:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
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

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 #491119
Posted Monday, April 28, 2008 11:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, May 27, 2008 10:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 2:37 PM
Points: 78, Visits: 284
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
Posted Wednesday, May 28, 2008 2:00 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:09 PM
Points: 1,602, Visits: 6,673
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
Posted Wednesday, May 28, 2008 2:16 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 2:37 PM
Points: 78, Visits: 284
Thanks Sir.
It worked...


Thanks.

Gunjan.
Post #508023
Posted Thursday, September 25, 2008 11:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, February 19, 2009 11:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse