Rayz


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





Re: Conditional page breaks in SSRS

Lisa Nicholls


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<







Re: Conditional page breaks in SSRS

Rayz

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







Re: Conditional page breaks in SSRS

Lisa Nicholls

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<






Re: Conditional page breaks in SSRS

Xclaim22

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.




Re: Conditional page breaks in SSRS

Lisa Nicholls

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<






Re: Conditional page breaks in SSRS

Rayz

Thanx a bunch Lisa....

ur initial suggestion worked !!






Re: Conditional page breaks in SSRS

Lisa Nicholls

Thanks for confirming!

>L<






Re: Conditional page breaks in SSRS

NalUppal

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






Re: Conditional page breaks in SSRS

Lisa Nicholls

"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<






Re: Conditional page breaks in SSRS

csi_hugh

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





Re: Conditional page breaks in SSRS

Lisa Nicholls

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<






Re: Conditional page breaks in SSRS

csi_hugh

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





Re: Conditional page breaks in SSRS

Lisa Nicholls

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<






Re: Conditional page breaks in SSRS

Lisa Nicholls

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<