none
SSRS report not returing data that has apostrophe in the parameter

    Question

  • Hi There,

    I have a report created in BIDS 2008 R2. I am passing parameters to oracle stored procedure and the data is the then returned as the ref cursor which i am capturing in BIDS and then displaying data on SSRS report. Report is working fine for all parameters except for the parameters that has apostrophe in it. in the same report, data rows are missing for parameters with apostrophe, but all other rows are displayed. If I execute this oracle procedure in BIDS' query designer, correct data is displayed including data with apostrophe in parameter value. I tried a few things but nothing works, how do i overcome this so that all the data is displayed.

    Thanks in advance.

    MJ

    Tuesday, November 20, 2012 7:17 PM

Answers

  • Well there is a lot of tweaking that needs to be done with SSRS reports. For this one to get working, i had to update the oracle procedure. What happens is, if there is an apostrophe in the input parameter, SSRS automatically makes it double apostrophe when passing the input parameter. so when oracle procedure runs, it is not able to return the data for the parameter with apostrophe because it has no data for double apostrophe. So in oracle when i added a replace function like this: 
    REPLACE (SUBSTR ,'''''', ''''), it changed double apostrophe to single apostrophe and then the data was returned properly.
    • Marked as answer by MiniJ Wednesday, November 21, 2012 4:48 PM
    Wednesday, November 21, 2012 4:48 PM

All replies

  • Hello MJ,

      May be you may have to replace the Single quotes with double single quotes before passing to the stored procedure.

     


    Best Regards Sorna

    Wednesday, November 21, 2012 11:09 AM
  • Well there is a lot of tweaking that needs to be done with SSRS reports. For this one to get working, i had to update the oracle procedure. What happens is, if there is an apostrophe in the input parameter, SSRS automatically makes it double apostrophe when passing the input parameter. so when oracle procedure runs, it is not able to return the data for the parameter with apostrophe because it has no data for double apostrophe. So in oracle when i added a replace function like this: 
    REPLACE (SUBSTR ,'''''', ''''), it changed double apostrophe to single apostrophe and then the data was returned properly.
    • Marked as answer by MiniJ Wednesday, November 21, 2012 4:48 PM
    Wednesday, November 21, 2012 4:48 PM
  • Hi MiniJ,

    I am glad to hear that you have got the issue resolved. Thanks for your useful sharing. I believe more community members can benefit from this thread.

    Regards,


    Mike Yin
    TechNet Community Support

    Monday, November 26, 2012 10:26 AM
  • Thanks Mike :)
    Monday, November 26, 2012 2:46 PM