SSRS - IIF/Switch/Replace multiple conditions in a field

Question

Have a nvarchar column in ms sql that, based on user input, may or may not have a carriage return and a line break together (CHR(13)&CHR(10)), just a carriage return (CHR(13)), just a line break (CHR(10)). In using that column in ssrs, I need to find if any of the above exist and replace them with the string \x0D\x0A, \x0D, or \x0A respectively.

I'm getting lost in this expression, any help is appreciated.

=SWITCH
(
(InStr(Fields!Info.Value, CHR(13)&CHR(10) > 0, REPLACE(Fields!Info.Value, CHR(13)&CHR(10)), "\x0D\x0A")),
(InStr(Fields!Info.Value, CHR(13) > 0, REPLACE(Fields!Info.Value, CHR(13)), "\x0D")),
(InStr(Fields!Info.Value, CHR(10) > 0, REPLACE(Fields!Info.Value, CHR(10)), "\x0A"))
)

Error is:

System.Web.Services.Protocols.SoapException: The Value expression for the textrun ‘Info.Paragraphs[0].TextRuns[0]’ contains an error: [BC30455] Argument not specified for parameter 'Replacement' of 'Public Function Replace(Expression As String, Find As String, Replacement As String, [Start As Integer = 1], [Count As Integer = -1], [Compare As Microsoft.VisualBasic.CompareMethod = Microsoft.VisualBasic.CompareMethod.Binary]) As String'. at Microsoft.ReportingServices.Library.ReportingService2005Impl.SetReportDefinition(String Report, Byte[] Definition, Guid batchId, Warning[]& Warnings) at Microsoft.ReportingServices.Library.ReportingService2010Impl.SetItemDefinition(String ItemPath, Byte[] Definition, String expectedItemTypeName, Property[] Properties, Warning[]& Warnings) at Microsoft.ReportingServices.WebServer.ReportingService2010.SetItemDefinition(String ItemPath, Byte[] Definition, Property[] Properties, Warning[]& Warnings)

replace(replace(Fields!Info.Value,CHR(13),"\x0D"),CHR(10),"\x0A") ? — KrazzyNefarious

alejandro zuleta alejandro zuleta · Accepted Answer · 2015-11-18T03:33:36

Try this:

=SWITCH(
InStr(Fields!Info.Value, CHR(13) & CHR(10)) > 0,
   REPLACE(Fields!Info.Value,CHR(13) & CHR(10), "\x0D\x0A"),
InStr(Fields!Info.Value,CHR(13)) > 0,
   REPLACE(Fields!Info.Value, CHR(13), "\x0D"),
InStr(Fields!Info.Value, CHR(10)) > 0,
   REPLACE(Fields!Info.Value, CHR(10), "\x0A")
)

Note if you use Switch function it will execute only the first case that is true, so if the first condition is true the second and the third will not be evaluated therefore not executed.

If you want to replace every char you should just use multiple REPLACE nested functions.

=Replace(
  Replace(
    REPLACE(Fields!Info.Value,CHR(13) & CHR(10), "\x0D\x0A"),
  CHR(13),"\x0D"),
CHR(10),"\x0A")

SSRS - IIF/Switch/Replace multiple conditions in a field

1 Answers