none
Unwanted double quotes inserted into Filter by SSDT

    Question

  • Hi,

    I have recently migrated several SSRS reports from SQL Server 2008 R2 to SQL Server 2012.

    When I initial view the filters they appear correct as follows; -

    However when I check the rdl file out for editing and view the same filters they appear as follows; -

    This results in my report filtering out unwanted data. Can anyone tell me how to stop this please?

    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Monday, August 05, 2013 5:42 PM

Answers

  • Hey fellas,

    I can confirm and replicate this issue.

    One of my users came across this exact issue today. We have one environment setup on SQL Server 2008 R2 and another environment setup on SQL Server 2012 (QA)

    This aberrant behaviour only exists on the SQL 2012 Report builder.

    Steps to reproduce:

    1) Create a report that uses a dataset that has at least one text field.

    2) Add a filter on that field

    3) Select the "In" operand instead of "="

    type in a plain text value. eg: abc

    (no quotes)

    Click OK.

    Now look in the Filters again, it will show """abc"""


    Now here comes the interesting part. Delete that and type in "abc" with one set of quotation marks around it and click OK.

    Now open the filters again and it will display: abc (no quotes).

    If you click cancel everything is good. The report still runs and returns the expected results. If you click 'OK' instead, it will re-insert the triple quotes. (Sets expression back to """abc"""). very annoying, particularly if you are dealing with a  list of values and need to maintain the report.

    example:

    Lets say you have a country field and you want to filter it, so for example you have:

    [CountryName]
    (In)

    US, Japan, China, France, Germany, Australia, New Zealand, England, Ireland, Greece

    Now lets say you want to add one country. You have to now add quotes around each item every single time you want to edit the list.

    i.e.

    manually insert quotes so that it looks like:

    "US", "Japan", "China", "France", "Germany", "Australia", "New Zealand", "England", "Ireland", "Greece"

    then add your new item, eg. ,"Brazil"

    And you have to do this each and every time you want to edit that list.

    Monday, August 12, 2013 5:37 AM
  • Hi Jim,

    You have expressed the problem domain and work around very well indeed.

    I do however confirm that this issue occurs in SQL 2012 SSDT as well as you reporting this problem in SQL 2012 Report Builder.

    If you could report this issue as a bug I would very much appreciate it since you have already explained the issue in more detail than me and I am really under the cosh at the moment.

    You can report this issue as a bug on; - https://connect.microsoft.com/

    and of course I will add my vote it this.

    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/


    Monday, August 12, 2013 7:41 AM

All replies

  • Hi Kieran,

    We cannot reproduce this issue on the test environment. It's hard to say the root reason which caused this issue. So in your scenario, we suggest you edit the filter manually.

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, August 06, 2013 6:45 AM
    Moderator
  • Hi Charlie,

    Thanks for getting back to me. I tried editing the filter manually. However SSDT re-inserts the double quotes as described earlier.
    Please find attached my version details; -

    However I noticed the same issue occur on my tablet which has a version of SSDT with a look and feel more orientated to Visual Studio 2010.

    The following work around does not work consistenty. I will continue investigating.
    1) Copy and paste the correct value into notepad.
    2) Delete the expression

    3) Add a new filter.
    4) Paste the value back from notepad.

    I still recommend a longer term fix is investigated by Microsoft. I can send you an anonymised rdl with the original issue if you are interested.

    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/



    Tuesday, August 06, 2013 7:22 AM
  • Hey fellas,

    I can confirm and replicate this issue.

    One of my users came across this exact issue today. We have one environment setup on SQL Server 2008 R2 and another environment setup on SQL Server 2012 (QA)

    This aberrant behaviour only exists on the SQL 2012 Report builder.

    Steps to reproduce:

    1) Create a report that uses a dataset that has at least one text field.

    2) Add a filter on that field

    3) Select the "In" operand instead of "="

    type in a plain text value. eg: abc

    (no quotes)

    Click OK.

    Now look in the Filters again, it will show """abc"""


    Now here comes the interesting part. Delete that and type in "abc" with one set of quotation marks around it and click OK.

    Now open the filters again and it will display: abc (no quotes).

    If you click cancel everything is good. The report still runs and returns the expected results. If you click 'OK' instead, it will re-insert the triple quotes. (Sets expression back to """abc"""). very annoying, particularly if you are dealing with a  list of values and need to maintain the report.

    example:

    Lets say you have a country field and you want to filter it, so for example you have:

    [CountryName]
    (In)

    US, Japan, China, France, Germany, Australia, New Zealand, England, Ireland, Greece

    Now lets say you want to add one country. You have to now add quotes around each item every single time you want to edit the list.

    i.e.

    manually insert quotes so that it looks like:

    "US", "Japan", "China", "France", "Germany", "Australia", "New Zealand", "England", "Ireland", "Greece"

    then add your new item, eg. ,"Brazil"

    And you have to do this each and every time you want to edit that list.

    Monday, August 12, 2013 5:37 AM
  • Hi Jim,

    You have expressed the problem domain and work around very well indeed.

    I do however confirm that this issue occurs in SQL 2012 SSDT as well as you reporting this problem in SQL 2012 Report Builder.

    If you could report this issue as a bug I would very much appreciate it since you have already explained the issue in more detail than me and I am really under the cosh at the moment.

    You can report this issue as a bug on; - https://connect.microsoft.com/

    and of course I will add my vote it this.

    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/


    Monday, August 12, 2013 7:41 AM
  • Hi Jim,

    The report you had an issue with, within SQL 2012. Can you tell me did you recently migrate it from SQL 2008 R2 please?

    Thanks in advance,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Monday, August 12, 2013 1:23 PM
  • Hi Kieran,

    Sorry for the late reply. Not sure under what application I should post the bug on the connect site? I tried searching for "SSRS", "Report builder" and "report". It found no matchign applications for any of those searches. Should I log it just under the SQL Server section?

    And yes, the reports were originally created on SQL2008 R2 SSRS deployment and then copied to a SQL2012 deployment. I'll ask my user if he has tried creating a new report from scratch on 2012 and see if it still has the same problem.

    Cheers,

    - Jim

    Thursday, August 22, 2013 12:32 AM