none
Conditional page breaks in SSRS

    Question

  •  

     

    Hi,

     

    I am having trouble setting conditional page breaks to my reports.

     

    i.e... I am having a report where I need to allow user the option to set page break between a group or not.

     

    Based upon the option selected by the user, I need to add page break to the report. I tried with all possibilities inside Sort and Group dialog box, but could not figure out how to toggle the option at runtime.

     

    Does anyone know how to implement page breaks on runtime? Help me plzzzz!! I really need this to be done…Thanx in advance for any help..

     

    - Rayz

    Saturday, June 2, 2007 6:47 AM

Answers

  • OK, I think I have succeeded in doing this...

    • create a boolean parameter, something like PageBreak --
      I would give it a default value, but it doesn't appear to be necessary if you don't want it.
    • create a group  with "page break at end", as you normally would. Don't put anything in its header and footer lines.
    • here's the trick: make this group's grouip expression an IIF() containing your "real" group expression,
      something like the following example:
    =IIF(Parameters!PageBreak.Value,Fields!MyField.Value,"")
    • if this group has actual headers and footers that you want to display, create a *second* group,
      INSIDE the one described above, on the "real" group expression.  This one does *not* have "page break at end".

    ... seems to work great... waddya think??

     

    >L<

     

     

    Sunday, June 3, 2007 4:24 PM
  • OK -- you can do this, it will work <s>. What you're missing (I think) is that you have to put the conditionals on the outer rim of the "real" groups.  IOW, your group order is this:

     

    Group1pre

    Group1

    Group2pre

    Group2

     

    ... and it is quite possible that it would have worked something like this:

     

    Group1pre

    Group2pre

    Group1

    Group2

     

    ... although I didn't try it that way. 

     

    What I did try successfully, and what appears to follow the "rules of engagement" as I understand them (my understanding being without inside knowledge, just observation, and is admittedly imperfect!), is this:

     

    GroupsPre

    Group1

    Group2

     

    ... using the following expression as my conditional break on GroupsPre:

     

    Code Snippet


    =IIF(Parameters!PageBreak.Value="NONE","",

          IIF(Parameters!PageBreak.Value="GROUP1",
              Fields!Group1.Value,

              Fields!Group2.Value)
         )

     

     

    I want to say one other thing, based on your sample data -- which may be compounded in something you're doing in your "real" data set:

     

    I am not sure what effect you are after on the inner group break, but you may have to dynamically sort your data to get the effect you are after when the break is on "GROUP2" (your inner group as expressed in the report).  It is certainly possible to do this, of course, and I'm not even sure that is an issue for you.

     

    [LSN Editing next morning: instead of dynamic orderng you can probably concatenate the value in the last part of the expression as csi_hugh shows in the next post]

     

    Regards,

     

    >L<

    Tuesday, September 25, 2007 5:18 AM

All replies

  • OK, I think I have succeeded in doing this...

    • create a boolean parameter, something like PageBreak --
      I would give it a default value, but it doesn't appear to be necessary if you don't want it.
    • create a group  with "page break at end", as you normally would. Don't put anything in its header and footer lines.
    • here's the trick: make this group's grouip expression an IIF() containing your "real" group expression,
      something like the following example:
    =IIF(Parameters!PageBreak.Value,Fields!MyField.Value,"")
    • if this group has actual headers and footers that you want to display, create a *second* group,
      INSIDE the one described above, on the "real" group expression.  This one does *not* have "page break at end".

    ... seems to work great... waddya think??

     

    >L<

     

     

    Sunday, June 3, 2007 4:24 PM
  •  

    I tried it out ..but no luck for me

     

    Wherever i apply page break..it seems it wont take the condition that i've specified..and pagebreaks appears permanently..even if i dont put pagebreak no..the page break comes..

     

    Thanks for your suggestion

     

    -Rayz

    Tuesday, June 5, 2007 6:16 AM
  • Well, let's see...

     

    >>it seems it wont take the condition that i've specified..and pagebreaks appears permanently

     

    What is the condition you've specified?

     

    Also, to make sure I can repro, is this an RDL or RDLC?

     

    >L<

     

     

    Tuesday, June 5, 2007 9:29 AM
  • Perhaps you should extend the syntax that Lisa has shown with the IIF() function.  If you set the group expression as previously mentioned and add a field reference that is global to the report instead of the empty field ( "" ) then perhaps this evaluate correctly.

    =IIF(Parameters!PageBreak.Value, Fields!MyField.Value, Fields!MyMostGlobalField.Value)

    This way you will get a field reference to evaluate the grouping to.  You may additionally want to hide this group with a "Visibility" expression on the group, though you may still get a page break before your report footer if one exists.

    Hope this helps.

    -Paul R.
    Tuesday, June 5, 2007 10:20 AM
  • Hi Paul,

     

    It actually does work fine with the "" -- and in the past I've done this with other "global/invariant" expressions -- such as True or 1. Is there some reason why this is a bad idea?

     

    BTW another way I do this, without an IIF(), is to group on a variable and increment the variable in code when I want to force a page break -- I haven't tried that in RS and it can be a little more difficult to manage in general.  So I didn't try it in response to this query.  I will try it if you think it's a better idea.


    Regrads,

     

    >L<

    Tuesday, June 5, 2007 11:35 AM
  • Thanx a bunch Lisa....

    ur initial suggestion worked !!

    Monday, June 11, 2007 12:45 PM
  • Thanks for confirming!

     

    >L<

    Monday, June 11, 2007 2:34 PM
  • Hi,

    I have five tables in a report which has to be repeated on every sheet of excel based on a variable

    Can i also use the same condition?

    Kindly help me out.

     

    Thanks in advance

    Nalini

    Monday, September 3, 2007 7:40 AM
  • "Every sheet of Excel" is basically "every explicitly-requested page of the report".  By that I mean you get a sheet for each page break you explicitly asked for with a page break condition, rather than the report just deciding that it has to page break based on some constraints of the host format (for example, physical page size).

     

    So, "Excel" shouldn't be handled specifically when trying to repeat tables -- it should work the way you want, if you get the explicit page break requests correct.

     

    I will assume you understand this (I almost wrote "that we're on the same page" <g>) and ignore the "Excel" part of the question...

     

    So, you want to have five tables that you want to see on each page.  Can you tell me some more about your layout, what each of the tables represent from the point of view of datasets?  Are they related to each other or completely distinct, data-wise?  Is there additional data in the report?  are the tables positioned adjacent to each other vertically, or horizontally, or what?

     

    >L<

     

    Monday, September 3, 2007 2:00 PM
  • Lisa,  I tried your approach and found two problems that I still can't figure out.

    1) I had to do the Page break before the sections rather than after section in order for it to work.

    2) I need to be able to choose the group level to page break on OR choose that no page break occurs at all.  In this pursuit, I can't find a solution.  Even if the Parameter!PageBreak criteria is not met, the top level group always page breaks.  I need the ability to eliminate the page break as one of my options.

     

    Any ideas?

    Monday, September 24, 2007 4:47 PM
  • Well, it definitely does work and should not require page break before -- in fact using page break before might be why you're saying "top level always breaks".

     

    I am not on a machine where I have any of these examples, and, clearly, we need to take this from the top.

     

    Can I have a clear(er) statement of exactly what you've done so far in this report and also a separate statement of what the requirements are?  This would optimally include some simplified SELECT or other way that I can repro exactly what your group breaks look like, there may be some collision that we have to account for that wasn't in the original suggestion (or maybe the collision *can't* be accounted for in your situation -- right now I honestly have no idea). 

     

    I'm leaving for the day now, so no rush on your response <s>

     

    >L<

    Monday, September 24, 2007 5:09 PM
  • I just created a stripped down sample report, and the issue with using page break after went away, but the other issue remains.  I have a parameter named PageBreak with three option values:  NONE, GROUP1, and GROUP2.  When i choose NONE, it should always evaluate the Group1pre and Group2pre as ="" and therefore never cause a page break during the report.  Unfortunately, it still causes the GROUP1 page breaks even if I choose NONE.

     

    I created four group levels sequenced as follows (as seen in the Groups tab of Table Properties) and a detail row with the Detail values:

     

    table1_Group1pre, page break at end checked, group expression =IIF(Parameters!PageBreak.Value="GROUP1",Fields!Group1.Value,"")

     

    table1_Group1, no page breaks, group expression =Fields!Group1.Value

     

    table1_Group2pre, page break at end checked, group expression =IIF(Parameters!PageBreak.Value="GROUP2",Fields!Group2.Value,"")

     

    table1_Group2, no page breaks, group expression =Fields!Group2.Value

     

    Here is the data set I created for testing.

     

    select 'ABC' as Group1, '123' as Group2, 'slfje' as Data
    union all
    select 'ABC' as Group1, '123' as Group2, 'redfg' as Data
    union all
    select 'ABC' as Group1, '123' as Group2, 'wqsde' as Data
    union all
    select 'ABC' as Group1, '456' as Group2, 'rdrgd' as Data
    union all
    select 'ABC' as Group1, '456' as Group2, 'f5e4s6' as Data
    union all
    select 'ABC' as Group1, '789' as Group2, 'rhfth' as Data
    union all
    select 'ABC' as Group1, '789' as Group2, 'effgfg' as Data
    union all
    select 'DEF' as Group1, '987' as Group2, 'kuhjg' as Data
    union all
    select 'DEF' as Group1, '987' as Group2, 'efjklrg' as Data
    union all
    select 'DEF' as Group1, '987' as Group2, 'euurio' as Data
    union all
    select 'DEF' as Group1, '654' as Group2, 'wesdf' as Data
    union all
    select 'GHI' as Group1, '789' as Group2, 'yghfg' as Data
    union all
    select 'GHI' as Group1, '789' as Group2, '56215hh' as Data
    union all
    select 'GHI' as Group1, '456' as Group2, 'hbvfg' as Data
    union all
    select 'GHI' as Group1, '456' as Group2, 't456e4w' as Data
    union all
    select 'GHI' as Group1, '123' as Group2, 'ouuyf' as Data
    union all
    select 'GHI' as Group1, '123' as Group2, '5f4g8r9' as Data
    union all
    select 'GHI' as Group1, '123' as Group2, 'zzzzzzzz' as Data

    Monday, September 24, 2007 7:19 PM
  • Thank you for taking such care to provide a reproducible sample.  I will check this out and write back, whether I can do this or not, and I have no idea at the moment <s>.  I do know that it's a chancey thing, and it's often tricky to get the conditional expressions right, so I usually take a while to get it to work.

     

    Anyway, I'll give this a shot and report back...

     

    >L<

    Tuesday, September 25, 2007 4:32 AM
  • OK -- you can do this, it will work <s>. What you're missing (I think) is that you have to put the conditionals on the outer rim of the "real" groups.  IOW, your group order is this:

     

    Group1pre

    Group1

    Group2pre

    Group2

     

    ... and it is quite possible that it would have worked something like this:

     

    Group1pre

    Group2pre

    Group1

    Group2

     

    ... although I didn't try it that way. 

     

    What I did try successfully, and what appears to follow the "rules of engagement" as I understand them (my understanding being without inside knowledge, just observation, and is admittedly imperfect!), is this:

     

    GroupsPre

    Group1

    Group2

     

    ... using the following expression as my conditional break on GroupsPre:

     

    Code Snippet


    =IIF(Parameters!PageBreak.Value="NONE","",

          IIF(Parameters!PageBreak.Value="GROUP1",
              Fields!Group1.Value,

              Fields!Group2.Value)
         )

     

     

    I want to say one other thing, based on your sample data -- which may be compounded in something you're doing in your "real" data set:

     

    I am not sure what effect you are after on the inner group break, but you may have to dynamically sort your data to get the effect you are after when the break is on "GROUP2" (your inner group as expressed in the report).  It is certainly possible to do this, of course, and I'm not even sure that is an issue for you.

     

    [LSN Editing next morning: instead of dynamic orderng you can probably concatenate the value in the last part of the expression as csi_hugh shows in the next post]

     

    Regards,

     

    >L<

    Tuesday, September 25, 2007 5:18 AM
  • You ROCK!!!  That is just what I needed!  I only had to make one minor modification (for anyone following this thread) as follows.  Thank you Lisa.  If I had started the thread, I would be checking your post as the answer, but I don't have that option.

     

    Code Snippet

    =IIF(Parameters!PageBreak.Value="NONE","",

    IIF(Parameters!PageBreak.Value="GROUP1",

    Fields!Group1.Value,

    Fields!Group1.Value+Fields!Group2.Value)

    )

     

     

    Tuesday, September 25, 2007 12:41 PM
  •  

    >>Fields!Group1.Value+Fields!Group2.Value

     

    Yes, that makes perfect sense  and (for the benefit of other readers) would probably take care of the ordering problem that I mentioned in my last post <s>.  (I was clearly too tired when I looked at this last night!)

     

    Good luck!

    >L<

    Tuesday, September 25, 2007 3:12 PM
  • I am trying to implement this inside of a list control, and am not getting the desired result.  In the case when I am grouping on the "" (also tried with an invariant, a calculated static field in the dataset, and a report parameter, all to the same effect) I only see the first element in the list.  Actually this makes sense to me, being a SQL guy, because to me grouping means rolling up everything inside the group into a single element.  I am trying to allow users to turn on and off the pagebreaks between subreports. (which are inside the list control)  There is no existing grouing, as there is only a single column in the list's dataset.

     

    Any help would be much apreciated, as I promised this feature to my boss thinking (stupidly wthout checking) that I could just set the pagebreak after property to an expression.

     

    thanks,

     

    -u

     

    Monday, October 29, 2007 10:08 PM
  • It doesn't really make sense to do this with a list, I don't think.  I am not in a position to test this right now, and I guess it's possible that it could work, but...

     

    Let's say it is actually some difference between lists as tables.  Can't you use a table with one column and no header/footer instead?  You can put your subreports into rows in the detail band (right?)

     

    If it is a difference between lists and tables, then this would fix it.  If it doesn't fix it then it has nothing to do with lists and something to do with the way you've expressed the groupings compared to the other examples we've talked about in the thread and we go back to first principles <s>.

     

    >L<

    Tuesday, October 30, 2007 12:10 AM
  • Hi Lisa,

        Before I ask my question I just want to say thank you for posting solutions on here. I used your SplitLongField() function on one of my reports and it worked great! I wasn't sure how I would have gotten around that problem without it.

    I'm certain your solution on this thread works, but for some reason I cannot get it to work on my report. My situation is a little bit different from the original post. I need a conditional page break based on a bit value that exists in my data set. I do not want the user to have to pass in a parameter to control the page break, I want the page break to be controlled by the data in the report.

    The bit value in my data set is derived from a case statement. Its always equates to either True or False.

    I followed all of the steps you listed in your original solution, except the condition for my group is IIF(Fields!PageBreak.Value=True, Fields!MyField.Value, "").

    I would think that this would work regardless of whether the value of the condition is coming from user input or from the report data set. But, it gives a page break even when the condition is false.

    Without going into too much detail. here is basically how the report is structured:

    Group1 Header: (grouped on MyField, repeat header, page break at end)
    Group2 Header: (grouped on MyField)
    Table Detail
    Group3 Footer: (grouped on IIF(Fields!PageBreak.Value=True, Fields!MyField.Value, "") )
    Group1 Footer: (grouped on MyField, repeat header,  page break at end) {Contains a Subreport}

    Basically, I'm trying to have a page break before a subreport, only if there is data in the subreport. The PageBreak field in the dataset tells whether or not there is data in the subreport.

    Any ideas?
    Saturday, December 13, 2008 4:16 AM
  • Lisa, you give some great explanations...  I've been researching here and elsewhere (including Spacefold.com).

    I am having a similar, yet slightly different, issue with page breaks. 

    I have three groups (Group_1, Group_2, Group_3) and have followed the steps above in your 6/3/07 post to this thread.  This works great except for one thing... when I choose to page break on Group_3, each Group_2 and Group_1 footer appears on it's own page.

    I got around this in Crystal Reports by using a conditional page break and the NEXT function to see where in the group I was; however, SSRS doesn't offer a similar function.  It does offer a PREVIOUS function, but I get an error message that an aggregate cannot be used in a group.

    As always, any ideas??

    P.S.  It looks correct when rendered in html; I'm basically trying to duplicate that output in the print layout...
    Wednesday, January 14, 2009 8:24 PM
  • Lisa,

    I'm having Page break issue, kinda similar to this issue.
    I have two tables and I want to put page break between those two tables, I also have a conditional visibility expression on the first table. I tried many workarounds but nothing work as what I needed yet.
    Rectangles works close to what I need, the Page break works in PDF but not in Excel. Please help.

    Thanks in advance,
    Vince

    lf
    Tuesday, February 3, 2009 9:28 PM
    • Proposed as answer by Mohit Nayyar Thursday, May 14, 2009 3:29 AM
    Thursday, May 14, 2009 3:29 AM
  • Hi,

    I am also facing the same problem, in my case i have multiple subreports (every report different dataset attacted) on the main page and i need every sub report start from the new page. To seprate them i use rectangle inbetween and use the Insert Page Break After rectangle.
    I need to show the subreports on the basis of a reprot parameter. If parameter says don't show the report it work as i use the express to hide the report and it's fine but left the blak page but if i use the same condition for rectangle (to hide), page break don't work in normal condition and report start from the end of the previous page. I don't know why this is happening.

    Please suggest.

    Regards,
    Himanshu


    himanshu
    Wednesday, September 2, 2009 12:43 PM
  •  

    Having a conditional page break and the right totals at the end of each group is more tricky


    Lets suppose you have a report with 2 groups and 1 detail.

    You also have a pagebreak parameter defining in which group page break will occur. O= no break, 1 = break on group1, 2= break on group 2


    Your report without page breaks would look like this:

    ----------------------------

    Header

    Group1Header

    Group2Header

    Detail

    Group2Footer(SUM)

    Group1Footer(SUM)

    Footer(Grand Total)

    ---------------------------

     

    To make the custom page breaks you have to have twice each group plus one for grand total.

    Your report now will look like this

    ----------------------------

    Header

    Group1Header (controls page break, contains group1 headers)

    Group2Header (controls page break, contains group2 headers)

    Group1_2_Header (contains group1 headers)

    Group2_2_Header (contains group2 headers)

    Detail (contains detail and measure)

    Group2_2_Footer(SUM of group2)

    Group1_2_Footer(SUM of group1)

    Group_Grand_total_Footer(report grand total)

    ---------------------------

     

    For Group1Header and Group2Header you set the page break value after the end of each group and set the right group values

    Group1Header : =Iif(Parameters!pagebreak.Value>0,Fields!Group1.Value,"")

    Group2Header : =Iif(Parameters!pagebreak.Value>1,Fields!Group2.Value,"") 

     

    Below is the code to control visibility of each part of the report

     

    Group1Header : =Parameters!pagebreak.Value < 1 'hidden when no page break occurs

    Group2Header : =Parameters!pagebreak.Value < 2 'hidden when no page break occurs

    Group1_2_Header : =Parameters!pagebreak.Value >0 'hidden when page break for group 1 occurs

    Group2_2_Header : =Parameters!pagebreak.Value >1 'hidden when page break for group 2 occurs

    Group2_2_Footer(SUM) : = NOT(

    Iif(Parameters!pagebreak.Value>1,RowNumber("Group2"),RowNumber("Group2_2")) = 

    Iif(Parameters!pagebreak.Value>1,CountRows("Group2"),CountRows("Group2_2"))

    )

    Group1_2_Footer(SUM) : = NOT(

    Iif(Parameters!pagebreak.Value>0,RowNumber("Group1"),RowNumber("Group1_2")) = 

    Iif(Parameters!pagebreak.Value>0,CountRows("Group1"),CountRows("Group1_2"))

    )

    Group_Grand_total_Footer(SUM) : = NOT(

    CountRows("table1") = RowNumber("table1")

    )

     

    The code below is to display the right totals SUM for measure, for each group

     

    Group2_2_Footer(SUM) : =Iif(Parameters!pagebreak.Value>1,SUM(Fields!measure.Value,"Group2"),SUM(Fields!measure.Value))

    Group1_2_Footer(SUM) : =Iif(Parameters!pagebreak.Value>0,SUM(Fields!measure.Value,"Group1"),SUM(Fields!measure.Value)) 

    Group_Grand_total_Footer(SUM) : =Sum(Fields!measure.Value,"table1")

     

     

     

     

    • Proposed as answer by Niktrs Wednesday, January 5, 2011 9:19 AM
    Wednesday, January 5, 2011 9:18 AM
  • Hi

     

    I was trying to get a page break after certain number of rows. In my table i have 

     

    5 rows with valaue 'E' and 36 with value 'P' like 

    Format      rownumber

    E                 1  

    E                2

    E                3

    E                4

    E                5  <----- need pagebreak after this record

    P                6

    P                7

    .....

     

    after E rows i need a page break. How do i do that? Where should i write the IFF condition and apply pagebreak based on that condition.

    i am a starter and is very confused with this.

    Monday, February 21, 2011 4:34 PM
  • I was not getting this approach to work and figured I would append this since it was the top search result and referenced by so many other results. In the end, turns out I wasn't following directions...

    Wrong...having an actual field in any group cell that was automatically generated for your group, because a) I was in a hurry to test this and b) I didn't fully interpret the following as 'being in the header:

    Right:

    Delete that column OR  remove the generated field value OR put the expression in place when you create the group:

    The lesson: I shouldn't be in such a hurry to test preview that I don't slow down to create the items step-by-step with the instructions. Thanks to Lisa for a 5 year-old solution that still works.

    Sunday, March 11, 2012 10:42 PM
  • Hi

    You can try this

    TablixMember Property

    PageBreak

      BreakLocation = Between

      Disable =  Not Fields!MyCondition     => MyCondition is a booelan

    It works great.

    I could not Send a printscreen to show you what I mean.



    Friday, March 22, 2019 8:48 PM