Working on SSRS reports can be tricky enough even when things are going well.
Add in occasional bug like behavior and things can become very tricky extremely quickly.
I recently noticed an issue where SSRS itself was doubling up on escape characters when it came to apostrophes.
Feed it a string like
‘Our DBA’s forever’ and when it passed that as a parameter it would end up like this:
‘Our DBA””s forever’
Somehow SSRS itself was taking the string being passed through and deciding that it should add not one, but three extra apostrophes.
In the end the resolution was pretty simple, but getting there took a bit of research.
I found the answer here, basically you need to use the join command as an expression on the parameter that is being passed through to SQL.
To get to this expression right click on the dataset, then go to properties. Click on Parameters and then select the function button to access the expression screen.
Now when you have saved and rerun the report you should no longer have the issue of multiple escaping of the apostrophes.
If this helped you out please let us know, if you are still having issues please share them.