SQL Clone
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
pardeshkumar
pardeshkumar
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 152
I am trying in Ssrs 2008 but when i used Ceiling(Rownumber(Nothing)/10) it displayed only one row in the report am i doing anything wrong(i have 12 rows)
pnsmanoj
pnsmanoj
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 26
I am trying the same expression as suggested int he forum to restrict number of records per page to 20. But it does not work. Still it displays 55 records per page.
Lori Johnson
Lori Johnson
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 6
I am using the following as you suggested successfully.

Floor((RowNumber(Nothing) - 1) / Parameters!RowsPerPage.Value)

However, adding this grouping broke the interactive sort that we had in place. The sort currently only sorts those records within each page.

Any suggestions?
kpsr_kpsr 63487
kpsr_kpsr 63487
SSC-Addicted
SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)

Group: General Forum Members
Points: 467 Visits: 473
Hey it's great to restrict the number of rows per page

I have one problem.i need to restrict the number of pages for report.

I have 100 pages report but my client want only first 20pages, for some reports he want last 20 pages.
how can do that???????????w00t



thanks in advance
sank
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46353 Visits: 14925
Sank,

You may want to create a new thread for this question as it is a bit different than the main question on this page.


I don't know how to do what you are being asked to do with reporting services. Will the client only ever want the first 20 or last 20 pages? What determined what is first and what is last? Is there a way of summarizing the data that reduces the # of pages yet still provides the necessary information? Honestly, I can't really imagine a case where a 100 page report is useful so I'm of the opinion that the client needs to rethink what they really want and need in this case.



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
only4mithunc
only4mithunc
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1814 Visits: 2803
Hi friends,

I have tried the expression as mentioned above

= Ceiling(Rownumber(nothing)/10)) this worked perfectly in SSRS 2005.

But can some please let me know how to do it in SSRS 2008? There when I right lick the table, it is showing column group and row group separately.

Thanks & Regards,
MC
only4mithunc
only4mithunc
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1814 Visits: 2803
Hi All,

I have tried many options in SSRS 2008 to do this but no luck.. :-(

Can some one help me on this please...

Thanks & Regards,
MC
Peter Brinkhaus
Peter Brinkhaus
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3223 Visits: 7404
You can add a row group with a break on the RowNumber expression. However, you have to remove the breaking expression from the Sorting options (the group expression is automatically added to the sorting option).

Peter
only4mithunc
only4mithunc
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1814 Visits: 2803
Hi Peter Thanks for your reply.

It didn't work.

This is what I have tried:

1) In my tablix selected the detail row
2) Right Click --> RowGroup--> Group Properties
3) In General tab, for Group Expression I have give as

=Ceiling(Rownumber(Nothing)/10)

After this when I checked nothing happened, all the records are coming as usual.

4)Then in the Page Break tab I checked the check box " Between Each Instance of the Group "

But after this records came 1 in one page like 100 pages !! as I have total 100 records.

Is the steps what I tried is correct? If not could you please correct me...

Thanks & Regards,
MC
Peter Brinkhaus
Peter Brinkhaus
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3223 Visits: 7404
only4mithunc (12/8/2010)
Hi Peter Thanks for your reply.

It didn't work.

This is what I have tried:

1) In my tablix selected the detail row
2) Right Click --> RowGroup--> Group Properties
3) In General tab, for Group Expression I have give as

=Ceiling(Rownumber(Nothing)/10)

After this when I checked nothing happened, all the records are coming as usual.

4)Then in the Page Break tab I checked the check box " Between Each Instance of the Group "

But after this records came 1 in one page like 100 pages !! as I have total 100 records.

Is the steps what I tried is correct? If not could you please correct me...

The problem you describe have been discussed earlier in this thread. Don't set the group expression on the Group Properties of the detail row. You really have to add a new row group to the detail row. Do the following:

1) add a parent group to the detail row (without header and footer).
2) set the group expression of the parent group to =Ceiling(Rownumber(Nothing)/10)
3) select the just added row group and remove the sorting expression in the Group Properties (on the Sorting tab)
4) in the Page Break tab check the check box " Between Each Instance of the Group

That should do.

Edit: added step to set page break
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