During the course of a current client project, the need arose to convert a report from an older system to the new implementation. The requirements were simple, print Avery style labels on 8.5" x 11" paper. What was the delivery mechanism of choice? SQL Server Reporting Services. After searching the web for some info on this, I found some answers that were pretty lacking in details so I decided to document the process for future developers.
This project is not able to be accomplished utilizing the end user ‘Report Builder' product. You will need to use the Visual Studio Report Designer. I will assume that if you have come here, you already know how to create a new report in Report Designer.
Before you start working on the report you will need to determine the following specifications.
How many columns of labels do you require?
What is the width and height of a single label?
What are the margins of the label sheet?
Our example will use 3 columns of labels, each label measuring 2" high by 2.5" wide, with .5" margins at the sides and top.
Once you have determined these things, create a blank report in Visual Studio Report Designer.
Go to the Layout tab and open the properties for the report:
Go to the layout tab and set the margins and page size to match the whole label sheet size and margins.
Once you have made these settings changes, you are ready to start adding controls to the report. Go to the toolbox and drag 1 List control for each column. (An lternative method would be to use one list control and set the column number in the properties above, but I have found that doing it this way gives you more control over label spacing.)
Set the controls width and height to match the size of one label on the report. Align the List controls in a single row across the page, taking the spacing on the physical sheet into account.
Next , create the dataset that will supply data to the report. The one critical criteria for your dataset is that it contain a field that will give the rows ordered numbering starting at 1. Check out this article from Microsoft on building a rank field directly into your dataset query: http://support.microsoft.com/kb/186133
Once you have created your dataset,set the dataset name and visibility expression for each list control so that it pulls the correct rows from the data.
The expression will need to be different for each list control. Here is a breakdown of the expression:
=IIF(Fields!Rank.value mod 3=1,true,false)
This expression uses the IIF function to set the visibility for that particular list control to true or false based on the Rank field of my query. The modulus returns the remainder after dividing the rank by the number of columns on the page, in my case, there are 3 columns. The first column will check if it equals 1, the second column will check for 2, the final column will check for 0. If there were four columns, the third column would check for 3 and the last column for 0. So, here are my three expressions, from left to right
=IIF(Fields!Rank.value mod 3=1,false,true)
=IIF(Fields!Rank.value mod 3=2,false,true)
=IIF(Fields!Rank.value mod 3=0,false,true)
***- An alternate method was posted by abbi below -- see the comments
Once this step is finished, you will need to add controls such as text boxes to your list controls and assign fields from your dataset to them. The easiest way to go about this is to design the first list control's content, and then copy the content inside of the list control and paste it into each of the remaining controls. Below is an example of setting the properties for a text box control within a list control.
Once you have finished the layout, preview and print your labels. You may need to tweak your margins or your layout a little bit to get it perfect.
Congratulations! You should now be able to print your labels with SQL Server Reporting Services.
No comments:
Post a Comment