prehlb Posted January 29, 2007 Share Posted January 29, 2007 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 More sharing options...
scottward Posted January 29, 2007 Share Posted January 29, 2007 > 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 recordsLIMIT $P{NumberOfLabelsToSkip}UNION ALLSELECT real records Make the labels print blank when null. Scotty Link to comment Share on other sites More sharing options...
prehlb Posted January 31, 2007 Author Share Posted January 31, 2007 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_extfrom _staticarray where number < $P{Skip}UNION ALLselect 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 More sharing options...
jamespcarter1 Posted June 11, 2007 Share Posted June 11, 2007 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 Link to comment Share on other sites More sharing options...
jamespcarter1 Posted June 11, 2007 Share Posted June 11, 2007 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 Link to comment Share on other sites More sharing options...
prehlb Posted June 11, 2007 Author Share Posted June 11, 2007 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now