November 17, 2015 at 7:47 pm
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)
November 18, 2015 at 3:58 am
Just looking at it, i.e. not testing it, I can see two things wrong.
Don't bracket each line of your switch statement. A switch statment requires two parameters per condition, an expression to evaluate and a value to return if it's true. When you bracket the whole line e.g. "(InStr(Fields!Info.Value, CHR(13) > 0, REPLACE(Fields!Info.Value, CHR(13)), "\x0D"))," you are telling SSRS to evaluate everything in the brackets together and therefore only passing one parameter to the switch statement.
You also seem to be adding and extra bracket on the "CHR(13))" which would close the Replace function. You might also want a default to always return the field even if the characters aren't found.
You could update your statement to something like:
=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"),
true, Fields!Info.Value
)
November 19, 2015 at 6:13 pm
Yeah, I got carried away with my brackets. Thank you, it's working now.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply