[SOLVED] SSRS - nvarchar custom formatting

SSRS - nvarchar custom formatting

I have a nvarchar cell with some number as is : 12345678, and I would format it like this : 12-345-678. But I'm unable to find the right expression... Should I do this in TSQL before inserting it in the report ? I'm using SSRS 2008

Solution

You can do this in either T-SQL or an SSRS expression.

T-SQL:

declare @value nvarchar(8);
select @value = '12345678';

select formattedValue = left(@value, 2)
  + '-' + substring(@value, 3, 3)
  + '-' + right(@value, 3);

SSRS expression:

=Left(Fields!value.Value, 2)
  & "-" & Mid(Fields!value.Value, 3, 3)
  & "-" & Right(Fields!value.Value, 3)

This assumes a fixed length text.

It's really up to you which is better - I suppose one consideration would be to keep the formatting at the presentation layer, i.e. SSRS, so that's probably the way I would go. But nothing stopping you using either option.