Sunday, May 10, 2009

Conditional Page break in SSRS

Conditional page breaks in SSRS are always something developer needs to work on in order to get it done. This is especially true when you need to render the same report in PDF.

Recently I need to implement the same and did that with a quick trick. Here is the sample SELECT I am using to demonstrate the same.




select 'Record 1' as record, 0 as PageBreak, 1 as grp
UNION ALL
select 'Record 2' as record, 0 as PageBreak, 1 as grp
UNION ALL
select 'Record 3' as record, 0 as PageBreak, 1 as grp
UNION ALL
select 'Record 4' as record, 1 as PageBreak, 2 as grp
UNION ALL
select 'Record 5' as record, 0 as PageBreak, 3 as grp
UNION ALL
select 'Record 6' as record, 0 as PageBreak, 3 as grp
UNION ALL
select 'Record 7' as record, 1 as PageBreak, 4 as grp
UNION ALL
select 'Record 8' as record, 0 as PageBreak, 5 as grp



"Record" column is just to show unique records. The most important column here is "PageBreak", which is setting the conditional page break with "1". That means whenever there is "1" in "pagebreak" column you need a page break otherwise keep on displaying the records on same page.

The other very important column is "grp". Well, you really don't need this column and still will be able to implement conditional page break functionality. But then SSRS will club all the records which requires page break into 1 grouping set, probably in the end with this sample data. So, in order to maintain the sequence of records we need to add a grouping column which will create a new group whenever it finds a different value in "pagebreak" column.

Now, lets move on to SSRS report. These steps will work in SSRS 2008. I am not adding standard steps of creating a data source and data set. I know you are all smart guys...

Simple enough, please follow these steps.

  1. Add a table with 2 columns, record and pagebreak.
  2. Now run the report and you will see all the records are coming in same page.
  3. Next, add a parent group to this row, parent to "Details" group in "Row Groups". Please check "Image 1".
  4. In the group expression select the "grp" column and the sorting, select "grp" in Sort by. Please check "Image 2".
  5. MOST Important, select "Between each instance of a group" of the newly create group under "Page Break" tab. Please check "Image 3".

Yep, that's it, run the report now. And you will see 3 records on page 1, 1 record on page 2, 2 records on page 3 and 1 record on page 4 and page 5.


image

Image 1


image

Image 2


image

Image 3

There are also other ways of doing the same, but I find this one very easy to implement.

Don't forget to hide new group by just minimizing the width and you are good to go. Try to render the same in PDF, and it works like a charm....


- Mohit Nayyar

12 comments:

sarika said...

hey,
i am facing problems in setting page break after group.
I have 5 groups in my report and i want to display all data for one group i.e. acccounting period on one page.
i tried example given by yu.Page break are perfect in it.But when i set same properties in my report page break on accounting page is not working can u please help?
Sarika

Mohit Nayyar said...

The point is to create separate groups in SQL and then using that field as group and use page break along with it. So, try to create separate groups for accounting etc. and then use this technique.

Else, send me more details about the report and sample data to my email.

Himanshu Kansal said...

Hi,

I am also facing the same problem, in my case i have multiple subreports 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

shankar said...

Hi,

Im having a problem in setting a page break in rdlc report thats working in local mode(processing mode local). i have a table with 4 groups .i need to set page break after every 20 rows . that 20 rows should include group header rows and group footer rows ....im using visual studio 2008 and microsoft reportviewer control. any idea.


regds,
Shankar.C

Gangadhar said...

Hi Mohit,
I tried implementing ur technique..
I need to option to user to select page break.so i have taken a parameter.I have created a parent group as PageBreakByJob.I have given =IIF(Parameters!PageBreak.Value,Fields!Job.Value,"") this expression in that group on property..but still nt wrking..Plz suggest me

Xier said...

Hi Mohit,
I am working on sales picking list report in ax 2012, I need to set the page break for each warehouse,there are two groups in the standard wmspicklist_orderpick report, I have changed the details_group's properties, add a group expression group on: inventlocationId, so the rport print out with the right page break on warehouse, but for each of the warehouse only one line is showed. Can you take a look to help me? thanks in advance!
Rosa

Anonymous said...

Hey,

I am facing a problem with ssrs report. Initially there was a gap requested by user between two tables.
When the gap was inserted and then exported to pdf as well as excel it is not showing the accurate result.

The expected result should be like the report when exported should come in a single page in both the cases excel as well as pdf. If the report when exported to pdf is coming in a single page then in excel it is not coming , if we make some changes and export then it is showing result in excel as single page and in pdf it is coming in 3 pagez......... please advice how should i go....

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...

hi..my problem is that i did not set any page break in my tablix, but still am getting page break..

Can you please help.

Many Thanks

Shaheen

lee woo said...

A dream is your creative vision for your life in the future. You must break out of your current comfort zone and become comfortable with the unfamiliar and the unknown. See the link below for more info.


#break
www.ufgop.org

Silvia Jacinto said...

There are things in our life that is not eternal thus we stand still and never show weaknesses. This only means that we are strong enough to face everything either it is big or small. Visit my site for more good vibes and inspirational thoughts. Good day!

n8fan.net

www.n8fan.net

sarah lee said...

I really enjoyed reading your article. I found this as an informative and interesting post, so i think it is very useful and knowledgeable. I would like to thank you for the effort you have made in writing this article.


edupdf.org