reporting-servicesreportbuilderssrs-2019

Combining Remove and Replace Function in MS SQL Report Builder

A field gives me the following string:

#$TEXTA#$TEXTB#$TEXTC

I want to remove the #$-chars and seperate the values by a new line. In addition I want to replace special characters (&). In the end it should look like this:

TEXTA
TEXTB
TEXTC

So I use this code:

=Replace(Replace(Fields!My_Field.Value,"&","&"),"#$",Environment.NewLine)

The string starts with #$, so this code makes a new line in the beginning, which I want to avoid by using this code:

=Right(First(Fields!My_Field.Value, "DATASET"), Len(First(Fields!My_Field.Value, "DATASET"))-2)

I want to remove the first two chars before the replace function starts. My problem is, that I dont know how to combine these two functions to work properly.

Solution

  • Without testing this....

    I think you should just be able to replace your reference to the field in your first expression, with your second expression, like this.

    =Replace(
        Replace(
               Right(First(Fields!My_Field.Value, "DATASET"), Len(First(Fields!My_Field.Value, "DATASET"))-2)
               ,"&"
               ,"&"
        ),
        "#$",
        Environment.NewLine
        )