How do handle Parameter value with an apostrophe

[bdonnelly_2]

I have a long complex query that uses several parameters to filter the report. The user can choose multiple values from a parameter list. The problem is that some of the parameter values have an apostrophe in them and cause the report to fail.

An over simplified example would be:

     Select bla, bla, bla where  client in ('PwC', McDonald's') 

I'm using something like  $X{IN, client ,  Client_Params }

It works fine as long as the user has not chosen a parameter value that contains an apostrophe.

How do I get around this problem?

Thanks.

[anish.rai]

Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times.

[prparame]

Hi bdonnelly,

I tried with the same scenario, but couldn't replicate the same on JRS 8.0.4 server connected to Postgres database.

Could you please share your jrxml. Please mention your database as well?

 

Please see the sample JRXML SQL  which I used 

 

<property name="ireport.jasperserver.reportUnit" value="/organizations/organization_1/reports/PDF_test"/>
    <parameter name="Client_Params" class="java.util.List"/>
    <queryString language="SQL">
        <![CDATA[sELECT physician_name,
    physician_id
FROM physician where
$X{IN, physician_name, Client_Params}]]>
    </queryString>

[bdonnelly_2]

Unfortunately I can not share the file, but I have found a workaround. I created an duplicate column where I have stripped out the apostrophe and use that to filter the report but not show it in the report.

[prparame]

Thanks for the confirmation. As mentioned in the comments, I could handle the apostrophe on JRS 804. 

The workaround "created a duplicate column where I have stripped out the apostrophe and used that to filter the report but not show it in the report" will also handle the above scenario.