Printing Multiple Labels for a Single Record

[Bob L]

I had a solution to a this thread that was different than the ones suggested. thread767-1439697 The other options seemed to involve having access to the database in more than a read manner. I am in an environment where I have to develop reports in a copy of the production database then can only run the reports against the actual database so making an alias table is not an option. I work with Homecare and Hospice agencies and one of them wanted to be able to print a full page of labels for a single patient that contained the patient name, date of birth and medical record number These could be used to adhere to lab specimen containers. I created a standard report that has a grouping based on my record selection (the patient medical record number) that I wanted displayed in my labels. I then added 10 detail sections. In each sections I added a blank text field that I sized to 1 inch height as a gauge to the height of the sections. I then created a subreport that contained the tables required for the 3 items on my labels. The subreport is linked by the medical record number. This sub report was then sized to 2.69 inches and placed in one third of one of the detail sections. I hen saved the subreport as a report and made 28 more copies and gave them each a unique name (subreport2, subreport3...). I then added each one and sized them each to 2.69 inches and linked them on the medical record number and formatted them to not have borders and suppressed all sections except the detail.(saving the subreport for some reason adds and additional unsuppressed report header and footer. A little tedious but it works. So in the end these 30 subreports are arranged in the 10 detail sections to be like an Avery 5160, 3 wide by 10 row label sheet. Then you simply setup the other variables. Top and Bottom margins .5 inch, Left / Right .188 inch. Finally I started with the subreports in the vertical center of each detail sections. I then printed a sample and compared it the a sample Avery sheet and adjusted the placement of the subreports vertical position in each detail section to align to the labels. I also adjusted the width and horizontal placement as well. Hope this helps someone that is not a DB expert. the whole alias table thing was a big deep for me. Thank you

Bob Lafler

 

[kenhamady]

>> so making an alias table is not an option.
Glad you found a solution that works, but the statement above doesn't sound right to me. "Making an alias table" is always on option because it is simply adding a table you are using to the report a second time (which will generate an alias in the report). It doesn't change anything in the DB or require any special database permissions. Also, instead of an 'alias' you could use any table in the DB that had 30+ permanent records in it. I just finished writing an article about inflation tables using a spreadsheet, a quick and dirty method, but the concept is the same.

http://kenhamady.com/cru/archives/2806

Next week I post Part 2 which will talk about finding an existing table in the DB to use, which is usually a better option. That could be an alias of a table you are already using or any other table. You might find these helpful.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[Bob L]

Hi Ken,

Sorry I did not connect what was meant by alias table. I do this all the time. The datasource I write against has a table with personnel, patient, doctors names. I have to write reports that pull in each of these types of entities so I will add alias' of this table for each type I am pulling into the report because they link off other fields in the other tables in the report. I assume I would have to add 29 alias tables, one for each label we want in the report...correct?

 

[kenhamady]

No, you just pull in 29 records from another table and don't join it. That will create 29 duplicates of every record. You want 29 records are numbered in order so you can use those numbers to do your "x of y".

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[Charliy]

Why are you using subreports? I've done a similar report to print a sheet of labels for s single patient. I just created what one label would look like, then copied and pasted it several times.

 

[Bob L]

Hi Charliy, I am not certain what you mean by copied and pasted it. If I'm not mistaken you mean adding 10 detail sections like I have done but instead of adding 30 subreports you added the name, dob and medical record number fields 3 times per 10 sections(thats if you used the same fields I did for my report). I could have done it the way you are referring to but it is a lot more work to align the elements of each label. My way all the elements are in the subreports. I am then aligning the 30 subreports instead of the name, dob and medical record number times 30. I make a subreport that has everything aligned in it the way I want and make 30 copies of it. This also has given me the flexibility to do a selective suppression of each subreport. I can enter a number in a parameter field and it will suppress that number of the subreports from printing. If I only need 10 labels I can enter 20 in the parameter and only the first 10 subreports will display / print. I have another post with my next question on this report. I am attempting to take this suppression technique to another level in which I can select a range like 10 - 12 and it suppress all the subreports but the ones that would print in the 10 to 12 label positions. This way I can maximize labels per sheet that I use. If I average 5 labels per patient, I can print 5 from the one side and then 5 from the other by turning the page around but then I have a sheet with 20 labels in the middle of the page and no easy way to print to them. The logic for the suppression on this one has eluded me. My other post has the details of what I have tried. The suppression formula to eliminate a certain number of the subreports is under Format Subreport then the Suppress X-2 {?numberparameter} >= 29 for the 2nd subreport, >= 28 for the 3rd, >= 27 for the 4th and so on. This works nicely to be able to specify the number of labels you want to print.