locked
Please help with paging/page break - SSRS 2008 RRS feed

  • Question

  • We have a SSRS 2008R2 report in CRM 2011 (premises) that appears with all records on a one page – 1 of 1. If it  a few pages for short period dates it’s not a problem but when it’s many pages for a year period that in print layout showing 400 pages it’s contributing into a very slow run.

    We do have some rows conditionally hidden and a collapse/expand drilling and also sub-reports in rectangles inside a group.

    I tried to delete all rows with sub-reports, rectangles, unhide details, put page break after the group (there is only one group in the report) but all records are still in a one page while print layout is showing many pages. When I’m trying to recreate the report from scratch it’s returning paging back but there are so many formatting, cells merging, etc., so it would be very unfortunate to build it again.

    The report has the same InteractiveSize and PageSize as 11in, 8.5in with margins 0.25,0.25,0.25,0.25.

    Here is a link that is suggesting to solve the problem but for some reason it's not working in our case.

    http://jetlounge.net/blogs/teched/archive/2007/12/09/dealing-with-paging-problems-in-ssrs.aspx

    It would be very nice to know:

    1. If paging or page break could help with a slow SSRS report. The ideal solution would be if the report would load a one page at a time with a page navigation.
    2. How we could resolve the issue having all records on a one page – enable paging.
    3. How to put a page break to not have blank areas or a part of a page filled if a group height can be very different as e.g. there could be a text in a ’Notes’  field that can consume a half or the whole page.

    Thanks

    Sunday, May 6, 2012 9:13 PM

Answers

All replies

  • Hi,

    Please have a look at the following 2 articles:

    http://technet.microsoft.com/en-us/library/bb677374.aspx

    http://msdn.microsoft.com/en-us/library/ms156282%28v=sql.90%29.aspx (even though this one is for 2005 but has a good explanation).

    I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.


    Ashish Mahajan, CRM Developer, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

    Monday, May 7, 2012 8:02 AM
  • Thank you,
    Ashish. I did go through those articles before. Unfortunately, they’re not answering
    the question how to get back those soft breaks. I do have all records on a one
    page and I do have InteractiveSize and PageSize as 11in, 8.5in. So, it’s
    supposed to be with paging but it’s not as it’s still a one page with all
    records on it. The print layout however is showing several pages. <o:p></o:p>

    The second article you’re suggesting is saying exactly what I need about the performance: “These
    page breaks improve the performance of large reports by allowing the report
    server to render and display the first report page while the rest of the pages
    are rendered in the background. This allows a user to begin viewing the initial
    pages of the report while waiting for additional pages to become available.
    ”<o:p></o:p>

    So, I’d need somehow to recover paging on my report.<o:p></o:p>

    Any thoughts how I could do it?<o:p></o:p>

    Thanks



    • Edited by al-dol Monday, May 7, 2012 4:53 PM
    Monday, May 7, 2012 4:52 PM
  • Hi,

    I would suggest try running that report in your reporting server (through IE). I mean deploy that report in your custom folder on your sql reporting server. Open the reporting server on IE. Try running that report there.

    Strip this report, so that it can only output let's say 10 records. Then try gradually increasing output and see where the paging breaks. I think try doing this.

    I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.


    Ashish Mahajan, CRM Developer, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

    Monday, May 7, 2012 11:49 PM
  • Thank you, Ashish, for your response. This is a problem. There is no page breaks no in VS neither in a browser. It's all shown on a one page and I'd need to break it by pages.
    Tuesday, May 8, 2012 6:27 PM
  • Sorry, I haven't responded for so long time. It seems as the problem with no paging is the conditional row visibility with "display can be toggled by this report items". I'm using the expression: =Not Parameters!HideDetails.Value

    When I'm making all those rows visible with "Show" option the paging is coming back.

    Any idea how I could fix it having "display can be toggled" and the paging?

    Thanks

    Thursday, May 17, 2012 4:23 AM
  • Hi,

    That is by design.

    Try grouping instead of hiding. So instead of hiding you expand/collapse the row. So there will be a + sign against the collapsed row.

    The only other I can think of is to show/export this report as PDF.

    I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.


    Ashish Mahajan, CRM Developer, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

    Thursday, May 17, 2012 11:13 PM
  • Thank you Ashish that could be the case. But, the problem is I do have all rows in a one group. The structure is as follows:

    1. Details - hidden and never visible

    2. Group with several rows: the upper one is with plus/minus (always visible) then several other rows under that can be visible or hidden based on +/- and also on the user option on a toolbar expand/collapse (false/true). To keep all correct with +/- I'm also using the =IIF(Parameters!HideDetails.Value,true,false) for the InnitialToggledState.

    Those hidden rows consists of several subreports that are getting a parameter from the group. So, it's a group with several rows and the hidden rows are inside the same group.

    Is there any trick I could use to use the same group as a parent and a chile one?

    Thanks

    Friday, May 18, 2012 5:30 AM
  • Hi,

    Unfortunately you cannot do that.

    I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.


    Ashish Mahajan, CRM Developer, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

    Friday, May 18, 2012 5:48 AM
  • Hi Ashish, thanks for your advice about grouping instead of hiding rows. It returned the paging back. I did trick the grouping by duplicating the child group column with a different name and use it as a parent group. So, I put needed fields in the parent group row and the rest fields into a child group that would be visible/hidden by clicking +/- on the parent group cell or selecting the option on a toolbar (true/false).

    So, it's working and I hope it can improve performance. However, per customer request I'd need to have a soft (if I'm not mistaken with a term) paging where a user could scroll down and see all records through pages along with navigating clicking next to go to a next page.

    I've also missed the fixed repeating on each page first row in a browser and preview. It seems as everything set correctly using Advanced option in a Grouping pane area and in pdf the first row is on each page but in a preview it's not. Please, advise if you know how to fix soft/scrolling paging and a fixed repeating row or I'd need to create a new topic.

    Thanks

    Thursday, May 24, 2012 6:47 AM