Padding Numbers with Leading Zeroes

In some contexts a number needs to be padded in order to look right or to work in the desired context. For example, in the Shelby v5 software, the Company Number and Fund Number values are always displayed as three-digit or four-digit values, with padded zeroes for one- and two-digit vlaues. Thus, company "1" is displayed as either "001" or "0001."

Another example from Shelby v5 is when the NameCounter is used to create the path for an individual picture. Pictures are stored with a file name that includes seven digits for the NameCounter value, so the number is padded with however many zeroes are needed to make seven digits.

To show you how each of these situations can be handled, I will do some sample queries on the Shelby v5 table called CNHst. This table contains contribution history, but that is incidental to this exercise. I am using this table because it contains both a CoNu (company number) column and a NameCounter column.

To start with, here is a basic query to pull the values we eventually want to pad with leading zeroes. An example result set appears underneath.

select

CoNu,

NameCounter

from

Shelby.CNHst

The original values are numeric, and therefore they will always show up with only the digits in the number itself. We cannot "pad" numeric values, so the first step is to change the numeric values into character values, using the CAST() function.

select

CoNu = cast(CoNu as varchar(4)),

NameCounter = cast(NameCounter as varchar(7))

from

Shelby.CNHst

The only visible change is that the values are now aligned to the left of each cell instead of aligned to the right. However, now that the values have been changed into character values, we can add zeroes to the left by a simple concatenation of a literal text string of zeroes.

select

CoNu = '000' + cast(CoNu as varchar(4)),

NameCounter = '000000' + cast(NameCounter as varchar(7))

from

Shelby.CNHst

To make each row the same fixed length of four digits (for CoNu) and seven digits (for NameCounter), we need to cut off the extra zeroes. The best way to do this in T-SQL is to use the RIGHT() function to pick up the string of characters starting with the rightmost character and counting left the desired number of digits.

select

CoNu = right('000' + cast(CoNu as varchar(4)), 4),

NameCounter = right('000000' + cast(NameCounter as varchar(7)), 7)

from

Shelby.CNHst

Now we have uniform output with each number reflected as desired with a fixed number of digits and padded zeroes as needed.

Reporting Services and check boxes

Most of the reports I make are created using a slimmed-down version of Active Reports, which has been integrated into Shelby Systems v.5 software as part of the ShelbyQUERY tool. Active Reports has a "checkbox" control object, which displays a checkmark in a box when the value of the field is anything other than "false," or an empty box when the value is "false." This can come in handy for reports with lots of Boolean (true/false) values.

Interestingly, even though SQL Server Reporting Services is much superior to Active Reports in many respects, it is lacking in a "checkbox" object. This seems to me to be a significant oversight, but it not completely insurmountable.

Although there is no "checkbox" object in Reporting Services, you can emulate one using a TextBox, if you are willing to have an "X" instead of an actual check mark in the box. Just do the following.

Create a new text box, put an "X" in it, and change the following properties to the listed values:

• BorderStyle: Solid
  
• Color: = IIf (Fields!MyField.Value, "Black", "White")
  
• Size: .175in, .175in (this fits a 10pt font; make it larger for a bigger font)
  

With these setting the box will have an "X" in it for true values and be empty for false values. I haven't tried it yet, but I suspect it might even be possible to use a font like Wingdings and actually print a checkmark in the box instead of just an X.