Filter having values with Apostrophe not working

Technical queries
#1

Hello Helical Insight,

I am using Helical Insight 4.0. Some of my filters have value with an apostrophe, such as, “Children’s Welfare”, “Parent’s Games” etc. When I select one of these filters, it gives an SQL error. Is there a way to handle this and allow values with apostrophe in filter?

Thank You,
Netta.

#2

Hello Netta,

A. Apostrophe is not supported directly. Apostrophe is a character used by SQLQuery to separate one value from another. In order to handle this, we need to replace the apostrophe with some other character.
Follow the below given steps to implement this.

  1. Create a adhoc report
  2. Add the column with apostrophe values also as a column of the report.
  3. Click on “More” option in the column dropdown.
    image
    The DB function modal will appear. Click on Advanced.
    image
  4. Here, we are replacing apostrophe with hyphen(-) by using database function.
    image
  5. Add that column as report filter by clicking on “Use as Filter” and then remove the column from the column list in report pane
    image
  6. Generate and Save the report
  7. Add the report in Dashboard then add filter also.
  8. Generally, the filter data is fetched from adhoc query so it causes problem with hyphen. In order to handle this, we should create a .efwd file and place in the below path. An .efwd file can be created using notepad++ :

….\hi\hi-repository
In the .efwd file, we can create the query that we want to use. The below sample query can be used as reference:

<EFWD>
<DataSources>
<Connection id="1" type="global.jdbc">
			<globalId>2</globalId>  //should be taken from Data source page
</Connection>
</DataSources>
	
<DataMaps>
<DataMap id="1" connection="1" type="sql">
<Name>sample_query1</Name>
<Query>
<![CDATA[
                  select name , replace(name,"'","-") as value from emp_name
                ]]>
</Query>
			
			
</DataMap>  		
</DataMaps>
</EFWD>
  1. The above query results 2 columns - name and value. Here name will have apostrophe and value will have hyphen in place of apostrophe.

In filter should provide display column as ‘name’ (having apostrophe)
Internally passing column as ‘Value’ ( not having apostrophe)

  1. Open the dashboard in edit mode then click on filter edit
    image

  2. Now report works fine with filter values which is having apostrophe also
    image

Thank You,
Helical Insight.