Adding Filter with Database Function applied on Column

Database Support
#1

Hello Helical,

I am using Helical Insight 4.0. I want to add the below mentioned filter to the report:
( STR_TO_DATE(MSR.DataRecorded.Date,"%Y/%m/%d" >= '2020-12-01')

Is there a way to do this?

Thanks,
Vonope.

#2

Hello Vonope,

You can use the in-built DB functions for creating such a column and then use it as a filter. You can delete the column (it may not be part of the report visualization) once the function is applied and you add the resultant column as a filter.

Below are the steps:

  1. Drag the DataRecorded.Data column to the report area.

  2. Go to “More” option.
    image

  3. Click on “Advanced”. You will be taken to the Functions screen.

  4. Click on typeconversion in Data Type and select “TODATE” in Functions by double clicking. You will get the screen below
    image

  5. Now, go to datetime in Data Type and drag the “Format” function in the column placeholder of the TODATE function already applied.

Your screen will look like this:
image

  1. Now drag the “DataRecorded_Data” column from the Available Columns section into the placeholder for timestamp. Change the date format to ‘%Y-%m-%d’ as shown below and click on Save.
    image

  2. Select the “Use as Filter” option to bring this converted date into the filters section.
    image

  3. Click on the pencil icon to edit the filter. Select “greater than or equal to” option in the Condition section
    image

  4. You can either use the Auto mode to enable datepicker and select the default date or use the Custom option and feed in the date. Save the filter settings and generate the report.
    image

The SQL generated will have the below in the where clause.

(CAST(date_format(DataRecorded.Data,'%Y-%m-%d') AS DATE) >= '2020-12-01')

Thanks,
Helical Insight.