Mailing Labels (5160=3x10) How to skip first X

[prehlb]

Hi everyone,

I've searched the forum but it appears pretty sparse on mailing label solutions so I'm not sure if anyone can help.

 

I have the mailing labels setup and working for Avery 5160 - that's not the hard part. The difficult part is to see if we can "skip" the first X labels to allow our users to reuse a partially used sheet of labels since the 5160 is a sheet of 30 labels.

 

I'm just not sure if I need to create a "dummy" recordset (or subdataset) which is applied first or if I need to create a view on the SQL server side to append empty records at the beginning of the result. I'm even considering a scriptlet but I'm just not sure how to control "skipping" a label unless I use the Print Expression field.

 

Thanks in advance for any thoughts and suggestions.

 

Bill

[scottward]

> create a view on the SQL server side to append

> empty records at the beginning of the result.

 

Not sure if its the BEST way, but this was my first thought as I read your question. Probably don't need a view though. You could just do something like the following (not sure if you use iReport or now).

 

Set your query to:

SELECT null records

LIMIT $P{NumberOfLabelsToSkip}

UNION ALL

SELECT real records

 

Make the labels print blank when null.

 

Scotty

[prehlb]

Scotty,

You had it nailed! MSSQL doesn't know LIMIT command but my lead programmer had already created a table for counting. It's just a table that holds the numbers 0 to 8000 for various counting purposes so we called it _staticarray since it doesn't change. The name of the field is NUMBER (real ingenious).

 

So, my final query format was just about what you said:

 

select 0 as student_id,

'' as first_name,

'' as last_name,

'' as grade_level,

'' as phone_no,

'' as address_1,

'' as address_2,

'' as address_city,

'' as address_state,

'' as address_zip,

'' as address_zip_ext

from _staticarray where number < $P{Skip}

UNION ALL

select

sd.student_id,

sd.first_name,

sd.last_name,

se.grade_level,

sd.phone_no,

sd.address_1,

sd.address_2,

sd.address_city,

sd.address_state,

sd.address_zip,

sd.address_zip_ext

...

 

So I wanted to thank you for the suggestion. It ended up working out nicely. The solution above is also portable so it's not locked into MSSQL.

 

Thank you and have a great day!

 

Bill

[jamespcarter1]

Hey Bill:

 

I'm a real rookie coder / Jasper user. Any way you'd be willing to share the 5160 template? I can't figure it out in IReport.

 

Cheers,

 

James

[jamespcarter1]

Hey Bill:

 

I'm a real rookie coder / Jasper user. Any way you'd be willing to share the 5160 template? I can't figure it out in IReport.

 

Cheers,

 

James

[prehlb]

I believe I can, but I don't want to send it with the query statements still embedded. I'll leave the entire template intact without the clauses. Would that be OK? Email me at prehlb at k12system dot com so I can send you the template.

 

Bill