Jump to content
Changes to the Jaspersoft community edition download ×

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


prehlb

Recommended Posts

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

Link to comment
Share on other sites

  • Replies 5
  • Created
  • Last Reply

Top Posters In This Topic

> 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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 4 months later...

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...