InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 23:32:02 GMT20http://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxHi,I also faced the same problem.I am using SSRS 2008.I had created the row group,Which I export to word document.Now every row details is printed one after the other,but problem is in between the row, details is splitted on two pages due page break,thus row details printed on next page.I just want to print every 4 rows on one Word page.I had try this by using Group on expression of row group,=Ceiling(RowNumber(Nothing)/4)But it dispalys only 1,5,9,13 ...... So on.My requirement is to print every 4 records on each word document.Please help me!Thanks in Advance!Mon, 21 Jan 2013 01:42:55 GMTavdhut.khttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxHi,I also faced the same problem.I am using SSRS 2008.I had created the row group,Which I export to word document.Now every row details is printed one after the other,but problem is in between the row, details is splitted on two pages due page break,thus row details printed on next page.I just want to print every 4 rows on one Word page.I had try this by using Group on expression of row group,=Ceiling(RowNumber(Nothing)/4)But it dispalys only 1,5,9,13 ...... So on.My requirement is to print every 4 records on each word document.Please help me!Thanks in Advance!Mon, 21 Jan 2013 01:41:37 GMTavdhut.khttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxI got it to work, the issue I was having is that i had two groups. Not exactly sure how I did that but I found an article that pointed my to the table properties. I'm going to walk through the changes on another report to make sure I got it.Thu, 22 Sep 2011 07:20:26 GMTgyounghttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspx[quote][b]only4mithunc (9/22/2011)[/b][hr]Deepak,Yes it is possible, check the check box for repeating header in each page.[/quote]I have done that setting but still i'm not gettting the header in all the pagesThu, 22 Sep 2011 07:08:54 GMTdeepak.ahttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxHi gyoung, Please try the below expression.=Ceiling(Rownumber(Nothing)/10)Deepak,Yes it is possible, check the check box for repeating header in each page.Thu, 22 Sep 2011 06:43:09 GMTonly4mithunchttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxHi All,Is it possible the repeat the header in each page ?Thanks & RegardsDeepakThu, 22 Sep 2011 00:15:30 GMTdeepak.ahttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxI'm lost, I'm using SSRS2005 and trying to limit the report to 15 rows per page. I have added a group and pasted '=Floor((RowNumber(Nothing) - 1) / 10)' in the expression but it is throwing an error. Can you assist? Or point me in the right direction.RowNumber() with scope parameter is not valid.Wed, 21 Sep 2011 20:27:31 GMTgyounghttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxPlease have a look on the article I posted on this.This gives you the details on how to control the number of rows per pagehttp://gauravsqlserver.blogspot.com/2011/03/how-to-fix-number-of-rows-per-page-in.htmlMon, 14 Mar 2011 22:03:46 GMTGaurav.Vinodkumarhttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxI postd an artcile in my blog on controling the number of rows per page in SSRS http://gauravsqlserver.blogspot.com/2011/03/how-to-fix-number-of-rows-per-page-in.htmlThanks,GauravSat, 12 Mar 2011 12:25:30 GMTGaurav.Vinodkumarhttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxgood solution it works Group on =Ceiling(RowNumber(nothing)/3)Set the group page break propertyFri, 24 Dec 2010 03:32:33 GMTazher.azizhttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxThanks Daniel,Why I asked was , when I make "Keep With Group " = Before/None , the column header was not repeasting to all the pages, it was comming only in the first page, but when I made it "After" , the header was comming in each pages... Is this is the it works? Or do you think I made some mistakes so where?Thu, 09 Dec 2010 20:01:41 GMTonly4mithunchttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxI believe the Keep with group is supposed to keep groups of data together on a page when possible. For example if you had a group that was order information and a detail section with the details of the order such as products and price. Keep with group is supposed to keep the group and the detail together on a page to make reading the report easier.Thu, 09 Dec 2010 14:00:31 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxThanks Daniel,When I tried the option you have mentioned it worked, with making "Keep With Group " Option = After Can I know what is the significance of "Keep With Group" property?Thu, 09 Dec 2010 09:30:09 GMTonly4mithunchttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxRight below the main design window pane is a small pane called the grouping pane. On the right side of the grouping pane is a small drop down arrow, click it and choose advanced. This will show all the row groupings in a graphical format. Choose the appropriate row group and then set its property to RepeatOnNewPage.Thu, 09 Dec 2010 08:49:24 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxThats correct :-) Thanks Peter, one more question, in the tablix property I have checked the "Repeat row header , Repeat column header " but the Column header is coming only in the firs page...Should I do the settings some where else.. ?Thu, 09 Dec 2010 06:23:33 GMTonly4mithunchttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxWhy not just remove the column? Right click on the column header, select 'Delete Columns', select 'Delete columns only'.PeterWed, 08 Dec 2010 23:21:09 GMTPeter Brinkhaushttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxThank you so much Peter,Yes you are correct I have done it like that, leaving the "=" sign there, now I have deleted the entire row then it worked!!!!One more small question, now at the left most side of the report I can see that Grouping column, and in each page it will show 1-10 as I have 10 pages. As I don't want to show this column I have selected that column and visibility property is made to false, now it is not showing but when I export to excel, in each page I can see that blank column.Can we do something (rather than making the column width very very small ? ) so that this blank column wont come in the report out put as well as while exporting to excel?Wed, 08 Dec 2010 19:09:06 GMTonly4mithunchttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspx[quote][b]only4mithunc (12/8/2010)[/b][hr]Hi Peter,In 2005 I'm able to do this as mentioned earlier, but the problem is with 2008 .I have followed the steps you have mentioned.The sorting tab also was showing the same expression = Ceiling(Rownumber(Nothing)/10) ,then I have removed it. And completed the steps given by you.But I'm getting an error as below:"The Value expression for the tablix ‘Tablix7’ contains an error: [BC30654] 'Return' statement in a Function, Get, or Operator must return a value."[/quote]I guess you did not remove the sort expression but just cleared the expression value (leaving the '=' behind). On the Sorting tab select the entire row for the sort expression (by clicking the 'Sort by' text) and then click the Delete button.PeterWed, 08 Dec 2010 10:46:52 GMTPeter Brinkhaushttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxHi Peter,In 2005 I'm able to do this as mentioned earlier, but the problem is with 2008 .I have followed the steps you have mentioned.The sorting tab also was showing the same expression = Ceiling(Rownumber(Nothing)/10) ,then I have removed it. And completed the steps given by you.But I'm getting an error as below:"The Value expression for the tablix ‘Tablix7’ contains an error: [BC30654] 'Return' statement in a Function, Get, or Operator must return a value."Wed, 08 Dec 2010 09:02:41 GMTonly4mithunchttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspx[quote][b]only4mithunc (12/8/2010)[/b][hr]Hi Peter Thanks for your reply.It didn't work.This is what I have tried:1) In my tablix selected the detail row2) Right Click --> RowGroup--> Group Properties3) 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... [/quote]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 breakWed, 08 Dec 2010 07:21:59 GMTPeter Brinkhaushttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxHi Peter Thanks for your reply.It didn't work.This is what I have tried:1) In my tablix selected the detail row2) Right Click --> RowGroup--> Group Properties3) 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... Wed, 08 Dec 2010 06:56:19 GMTonly4mithunchttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxYou 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).PeterWed, 08 Dec 2010 03:24:28 GMTPeter Brinkhaushttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxHi All,I have tried many options in SSRS 2008 to do this but no luck.. :-(Can some one help me on this please...Tue, 07 Dec 2010 22:45:00 GMTonly4mithunchttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxHi 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.Mon, 06 Dec 2010 10:03:03 GMTonly4mithunchttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxSank,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.Tue, 24 Aug 2010 05:59:39 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxHey it's great to restrict the number of rows per pageI 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 sankTue, 24 Aug 2010 04:39:31 GMTkpsr_kpsr 63487http://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxI 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?Tue, 08 Jun 2010 16:38:11 GMTLori Johnsonhttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxI 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.Mon, 31 May 2010 01:23:18 GMTpnsmanojhttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxI 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)Fri, 30 Oct 2009 14:24:54 GMTpardeshkumarhttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxI 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 = ?Thu, 19 Feb 2009 11:49:47 GMTrxlevinehttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxAwesome! 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 listThu, 25 Sep 2008 11:40:57 GMTJoe Hanson-472266http://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxThanks Sir.It worked...Wed, 28 May 2008 14:16:22 GMTGunjanhttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxI 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.PeterWed, 28 May 2008 14:00:05 GMTPeter Brinkhaushttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxThis 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??Tue, 27 May 2008 10:52:14 GMTGunjanhttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspx[quote][b]Peter Brinkhaus (4/27/2008)[/b][hr]Add a group to your report and use the following expression to group on:[code]=Floor((RowNumber(Nothing) - 1) / 10)[/code]Also set the 'Page break at and' property for the group.Peter[/quote][b]Thank you, Peter! [/b] After more research we'd come to a similar conclusion using[code]=Ceiling((RowNumber(Nothing)) / 10)[/code]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.MattMon, 28 Apr 2008 11:56:52 GMTMatt Brown-315969http://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxExcellent Peter, I should have thought of that, too! It's a simple solution.Sun, 27 Apr 2008 06:37:39 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxAdd a group to your report and use the following expression to group on:[code]=Floor((RowNumber(Nothing) - 1) / 10)[/code]Also set the 'Page break at and' property for the group.PeterSun, 27 Apr 2008 02:33:12 GMTPeter Brinkhaushttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxI think you need to do it with an SP as I cannot find a way to break after n records.Fri, 25 Apr 2008 10:12:24 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic490774-147-1.aspxThis 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!Fri, 25 Apr 2008 09:45:45 GMTMatt Brown-315969