Solved

Reporting Services (mailing labels) how to get physical page break on group change instead of column break?

Posted on 2011-03-08
4
1,378 Views
Last Modified: 2012-05-11
I'm generating labels (Avery 5160 : 3 across, 10 down) for a school. Grouping of students is by grade. Each new grade must start printing on a new sheet of labels. We must not start printing a grade's group on the next available column of labels on a partially used sheet. The reason we cannot have more than one grade group on a physical sheet of labels is that the sheets are taken off of the printer and handed directly to the grade's teacher. No one will cut the sheet coming out of the printer to separate grades.

When I put a page break between grade groups the next grade prints on the next available column of the label sheet. How can I force extra breaks to get the next grade to print on the next label sheet in the printer? I understand that Reporting Services treats each column in a multi-column report as a page--so it would be the equivalent of forcing extra page breaks (an extra one or two page breaks depending in which column the last data item for the prior group printed).
Attached are the .rdl file and dataset, and a Word document showing an image of what is rendered vs the desired rendering. I've added a ".xml" extension to allow these files to upload to this site. Whem importing into Reporting Services make sure you remove the ".xml" extension so you have valid file names "MultiColumnPageBreakOnGroup.rdl" and "MultiColumnPageBreakOnGroup.rd
MultiColumnPageBreakOnGroup.doc
MultiColumnPageBreakOnGroup.rdl..xml
MultiColumnPageBreakOnGroup.rdl.xml
0
Comment
Question by:RippleBirder
  • 2
4 Comments
 

Author Comment

by:RippleBirder
ID: 35111595
Looks like I have solved my own question. I added a column to the dataset which returns the number of page breaks (columns) that have to be forced in order for the next group to print on the next physical sheet of paper.

Then I set up an "adjacent group" after the detail section to add a page break if a one or two column shift need to be forced. If no additional page break is needed (due to a break in the actual group) the adjacent group is hidden and therefore, does not break.

A second "adjacent group" after the detail section is required if a two column shift is to be forced. This is hidden to prevent a page break if only one or no additional page breaks are needed.

I'll clean up my solution and post it for others.
0
 

Accepted Solution

by:
RippleBirder earned 0 total points
ID: 35129106
Here is the solution I've arrived at.

Included is a sample dataset and comments embedded to show how I've identified how many additional column(page) breaks are required to print on a new sheet of labels. I'm using a labels sheet of 9 down and 3 across for this particular solution (Width 2.5935in; Height 1.0in). Use appropriate column/row values for other label sheet configurations.

The .rdl file that uses this dataset is attached.


Sample dataset:
select 'North Elementary' as school, 'Grade 1' as class, 'Student1' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
into #temp_my_table
union
select 'North Elementary' as school, 'Grade 1' as class, 'Student2' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 1' as class, 'Student3' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 1' as class, 'Student4' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 1' as class, 'Student5' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 1' as class, 'Student6' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union 
select 'North Elementary' as school, 'Grade 2' as class, 'Student7' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student8' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student9' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student10' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student11' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student12' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student16' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student17' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student18' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student19' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student20' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student21' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student22' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student23' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student24' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student25' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student26' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student27' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student28' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student29' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student30' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student31' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student32' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student33' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student34' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student35' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student36' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student37' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student38' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student39' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student40' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student41' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student42' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student43' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student44' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student45' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student46' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student47' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student48' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student49' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student50' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student51' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student52' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student53' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 2' as class, 'Student54' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 3' as class, 'Student55' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 3' as class, 'Student56' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 4' as class, 'Student57' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed
union
select 'North Elementary' as school, 'Grade 4' as class, 'Student58' as student, ' ' as one_extra_page_break_needed, ' ' as two_extra_page_breaks_needed

/* figure out which column data goes in 1:2:3 */
select (row_number() over (partition by class order by school, class) -1) as groupnum, 
school, class, student, one_extra_page_break_needed, two_extra_page_breaks_needed
into #temp_my_table_w_seq
from #temp_my_table

/* figure out which column data is written to */
/* These labels have 9 rows and 3 columns per page so using the ceiling and % operators */
/* you end up with first column = 0, second column = 1, third column = 2 */
SELECT  groupnum as row, ceiling(groupnum/9) % 3 as last_data_column, 
school, class, student, one_extra_page_break_needed, two_extra_page_breaks_needed
into #temp_numbered_table
FROM #temp_my_table_w_seq

/* find out which column the last data row will be written to */
/* for that particular group--in this case class */
/* you end up with first column = 0, second column = 1, third column = 2 */
select last_data_column as last_column, school, class
into #temp_max_col
from #temp_numbered_table t
where t.row = (select max(row) from #temp_numbered_table t2
where t.school = t2.school
and t.class = t2.class)

/* Data rows end in first column of the sheet. Two additional */
/* page breaks needed to start new sheet */
update t
set 
two_extra_page_breaks_needed = 'Y',
one_extra_page_break_needed = 'Y'
from #temp_numbered_table t
join #temp_max_col c on
t.school = c.school
and t.class = c.class
and last_column = '0'

/* Data rows end in second column of the sheet. One additional */
/* page break needed to start new sheet */
update t
set one_extra_page_break_needed = 'Y'
from #temp_numbered_table t
join #temp_max_col c on
t.school = c.school
and t.class = c.class
and last_column = '1'

/* Data rows end in third column of the sheet. No additional page break needed to start new sheet. */
/* The break on group accomplishes this */

/* Get result data for the report */
select * from #temp_numbered_table

Open in new window

MultiColumnPageBreakOnGroup.rdl.xml
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SCCM Microsoft Report 2 21
MS SQL Bulk load data error 5 34
Sql query 34 22
SQL Date Retrival 7 31
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now