Formatting a SSN in Reporting Services

I've got a report of peoples' SSN's and Names. The SSN is stored as a string
(nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
IOW, with hyphens.
In my report, the expression to display the data looks like:
=Fields!SSN.Value
When I try to format it like this...
=Format(Fields!SSN.Value,"###-##-####")
...I see only puond signs and hyphens, with no numbers.
So I convert the string to a decimal like this...
=Format(cdec(Fields!SSN.Value),"###-##-####")
...and it displays correctly in my report. But to my mind, this isn't an
elegant solution. Isn't there some way I could format the string, itself,
without having to convert it to a decimal?
TIA,
__BirmAn alternative is to use string manipulation functions. E.g.
=Left(Fields!SSN.Value, 3) & "-" & Mid(Fields!SSN.Value, 4, 2) & " - "
Right(Fields!SSN.Value, 4)
More information about these functions is available on MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctLeft.asp
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctMid.asp
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctRight.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
"Birmbear" <Birmbear@.discussions.microsoft.com> wrote in message
news:9A6DD1E3-ABDB-488B-8B56-055C8CFCC6F1@.microsoft.com...
> I've got a report of peoples' SSN's and Names. The SSN is stored as a
> string
> (nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
> IOW, with hyphens.
> In my report, the expression to display the data looks like:
> =Fields!SSN.Value
> When I try to format it like this...
> =Format(Fields!SSN.Value,"###-##-####")
> ...I see only puond signs and hyphens, with no numbers.
>
> So I convert the string to a decimal like this...
> =Format(cdec(Fields!SSN.Value),"###-##-####")
> ...and it displays correctly in my report. But to my mind, this isn't an
> elegant solution. Isn't there some way I could format the string, itself,
> without having to convert it to a decimal?
> TIA,
> __Birm
>|||You could use the substring function in your query if it is a shared
dataset, that way you only have to do it once.
"Birmbear" <Birmbear@.discussions.microsoft.com> wrote in message
news:9A6DD1E3-ABDB-488B-8B56-055C8CFCC6F1@.microsoft.com...
> I've got a report of peoples' SSN's and Names. The SSN is stored as a
> string
> (nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
> IOW, with hyphens.
> In my report, the expression to display the data looks like:
> =Fields!SSN.Value
> When I try to format it like this...
> =Format(Fields!SSN.Value,"###-##-####")
> ...I see only puond signs and hyphens, with no numbers.
>
> So I convert the string to a decimal like this...
> =Format(cdec(Fields!SSN.Value),"###-##-####")
> ...and it displays correctly in my report. But to my mind, this isn't an
> elegant solution. Isn't there some way I could format the string, itself,
> without having to convert it to a decimal?
> TIA,
> __Birm
>|||Robert,
That worked like a champ. (That slapping sound you hear BTW, is my palm
hitting my forehead.) I tried a similar method, using Substring in stead of
the Mid() function, and -- here's my biggest mistake -- using a plus sign as
the concatenator instead of using the ampersand. To much C# in my immediate
past, I guess. <g>
Thanks a lot for the pointer. I really appreciate your taking the time.
__Birm
"Robert Bruckner [MSFT]" wrote:
> An alternative is to use string manipulation functions. E.g.
> =Left(Fields!SSN.Value, 3) & "-" & Mid(Fields!SSN.Value, 4, 2) & " - "
> Right(Fields!SSN.Value, 4)
> More information about these functions is available on MSDN:
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctLeft.asp
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctMid.asp
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctRight.asp
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Birmbear" <Birmbear@.discussions.microsoft.com> wrote in message
> news:9A6DD1E3-ABDB-488B-8B56-055C8CFCC6F1@.microsoft.com...
> > I've got a report of peoples' SSN's and Names. The SSN is stored as a
> > string
> > (nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
> > IOW, with hyphens.
> >
> > In my report, the expression to display the data looks like:
> >
> > =Fields!SSN.Value
> >
> > When I try to format it like this...
> >
> > =Format(Fields!SSN.Value,"###-##-####")
> >
> > ...I see only puond signs and hyphens, with no numbers.
> >
> >
> > So I convert the string to a decimal like this...
> >
> > =Format(cdec(Fields!SSN.Value),"###-##-####")
> >
> > ...and it displays correctly in my report. But to my mind, this isn't an
> > elegant solution. Isn't there some way I could format the string, itself,
> > without having to convert it to a decimal?
> >
> > TIA,
> >
> > __Birm
> >
>
>|||MPF,
Yup...and that would be the elegant solution, wouldn't it? Hmmm...it's
not a shared dataset, but still...mebbe I'm going to do exactly that. Just
for yucks and education if nothing else.
Thanks!
__Birm
"MPF" wrote:
> You could use the substring function in your query if it is a shared
> dataset, that way you only have to do it once.
>
> "Birmbear" <Birmbear@.discussions.microsoft.com> wrote in message
> news:9A6DD1E3-ABDB-488B-8B56-055C8CFCC6F1@.microsoft.com...
> > I've got a report of peoples' SSN's and Names. The SSN is stored as a
> > string
> > (nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
> > IOW, with hyphens.
> >
> > In my report, the expression to display the data looks like:
> >
> > =Fields!SSN.Value
> >
> > When I try to format it like this...
> >
> > =Format(Fields!SSN.Value,"###-##-####")
> >
> > ...I see only puond signs and hyphens, with no numbers.
> >
> >
> > So I convert the string to a decimal like this...
> >
> > =Format(cdec(Fields!SSN.Value),"###-##-####")
> >
> > ...and it displays correctly in my report. But to my mind, this isn't an
> > elegant solution. Isn't there some way I could format the string, itself,
> > without having to convert it to a decimal?
> >
> > TIA,
> >
> > __Birm
> >
>
>