Mask and trim formula for ssn

I have a number field and stores ssn’s. when pulling the field to a report it displays like this: (00,001,234.00 or 000, 001,234.00).

I tried this formula: [right(totext ({AGMRK_UPD_CLIENT.TAX_ID_NBR}),4)], but the results display as 4.00, I tried changing it from 4 in the formula to 8 and that displays as 1,234.00.

I need a Crystal reports formula, that allow me to only display the true last 4 digits of the ssn (1234) without comas, periods or the last 2 zeros, please and thank you. :blush:

1 Like

Hello @mdcgpw and welcome to B :mrgreen: B

We do our masking in the universe layer. We require our Crystal Reports to be used over a universe unless they have compelling reasons not to.

To show the right four digits of a data field of variable length use the following formula:
REPLICATE(‘X’, (Len(DataColumn) -4)) + RIGHT(DataColumn, 4)

This works for SQL Server in the universe and will work with any type of data. The above will show an X for all of the other digits except for the last four. Not sure what your database is so you may have to use a different key word. I’m not sure if Crystal Reports uses the same syntax or not.

Formatting the report object should take care of removing the comma and the decimals. That is just the default number format that Crystal Reports uses.

Try the formula:
Right(ToText({AGMRK_UPD_CLIENT.TAX_ID_NBR},“0”) ,4)

Use the number formatting features in ToText(). You’ll find these in the Help. Basically, you’ll do something like this:


The 0 tells it to not include any decimal places. The “” tells it to not use any thousands separator.