Sort on four- or five-character employee serial number 2

[Rooski]

My SQL server database contains employee serial numbers stored as a character (string) field although all serial numbers are numeric. When making sales, employees are allowed to enter their serial number with a leading zero or without a leading zero. Most employees use only one or the other format exclusively. Some employees, however, use both formats. When the leading zero is present, the serial number contains five characters (e.g. 01234). When the leading zero is not present, the serial number contains four characters (e.g. 1234).

I have successfully created a cross tab (Crystal 8.5) that displays units sold of three different product types by employee serial number in descending order by total units sold using the TopN function. The problem is that those employees who have recorded units sold using both serial number formats show up in different parts of the report due to the sort order.

What I want to do is treat the 01234 serial number the same as the 1234 serial number for sorting purposes. I would settle for simply showing the sales results for 01234 and 1234 as two separate lines one on top of the other. Retention of the zero is not critical.

There are approximately 1,200 total serial numbers of which maybe 10 percent are affected by the dual format. Any suggestions other than doing away with the person who made the decision to allow both methods of serial number entry?

Many thanks in advance...

Rooski

 

[synapsevampire]

Use a formula in lieu of the table.serial field as in:

val({table.serial})

Now it's a numeric and will sort accordingly.

If you prefer, you can still use the original {table.serial} for display purposes.

-k

 

[Rooski]

Thank you for the prompt reply. Your recommendation worked. But I'm not sure how I can display the original serial number when the cross tab row is occupied by the Val(table.serial).

Thanks, Rooski

 

[synapsevampire]

Can't recall if CR 8.5 has it, but in the Cross-Tab expert click the Group Options for the field and select Options->Customize Group Field Name->Use a formula as Group Name and place your field in there.

-k

 

[Rooski]

You're hunch on where the Group Name Formula field was located in V8.5 was correct. I created the group name formula as {table.serial_num} for the row field Val(table.serial_num) that was in the cross tab. The ensuing display of the serial numbers was interesting.

For those serial numbers that existed in only one format or the other (e.g. 01234 or 1234), the display of the serial number was consist based on the format.

However, for those serial numbers that existed as both 01234 and 1234, the display was not consistent.

For example, one set of actual serial numbers was 03146 (10 units sold) and 3146 (2 units sold). The cross tab displayed the serial number as 3146 (12 units sold). Another set of actual serial numbers was 02954 (9 units sold) and 2954 (3 units sold). The cross tab displayed this serial number as 02954 (12 units sold); just the opposite of the previous case. In conclusion, the format of the serial number that is displayed when both serial numbers were used by an employee seems arbitrary. But the total units sold is always correct. Perhaps the display of the serial number depends on the format of the serial number associated with the last record encountered by Crystal. Just speculation.

I also found another way to display both serial number formats by adding table.serial_num to the cross tab row that already contains Val(table.serial_num).

Thanks again for your help.

 

[synapsevampire]

You could create a formula to resolve this:

if len(totext({table.serial_num},0,"")) = 4 then
"0"+totext({table.serial_num},0,"")
else
totext({table.serial_num},0,"")

This assumes that they're either 4 or 5 in length, you may need to adjust it.

Use that as the display to ensure that they are all the same format.

-k

 

[Rooski]

Thanks again for the reply. I tried your formula and it worked. Your help on all these issues is greatly appreciated.