cannot fold date column error when using custom ODBC via data connector

[xsgao-git]

We are connecting Power BI Desktop via a custom data connector to a ODBC DSN, the ODBC DSN is based on a custom ODBC driver which connect to Cognos BI server. This solution works well. But we have one issue. When I drag a data column to an table, I got this error:

[Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression.

I turned on trace within PBI Desktop and I fund following message:

"ErrorMessage":"Data Type of column Date with searchable property UNSEARCHABLE should be SEARCHABLE or ALL_EXCEPT_LIKE. You can override the supported data types from ODBC driver using SQLGetTypeInfo."

So I fired ODBCTest, tested my DSN vs another SQL Server ODBC based DSN. I found that they return same information to SQLGetTypeInfo call.

Here is that SQL Server ODBC DSN returned:

"TYPE_NAME" "DATA_TYPE" "COLUMN_SIZE" "LITERAL_PREFIX" "LITERAL_SUFFIX" "CREATE_PARAMS" "NULLABLE" "CASE_SENSITIVE" "SEARCHABLE" "UNSIGNED_ATTRIBUTE" "FIXED_PREC_SCALE" "AUTO_UNIQUE_VALUE" "LOCAL_TYPE_NAME" "MINIMUM_SCALE" "MAXIMUM_SCALE" "SQL_DATA_TYPE" "SQL_DATETIME_SUB" "NUM_PREC_RADIX" "INTERVAL_PRECISION" "USERTYPE" "date" 91 10 "'" "'" <Null> 1 0 3 <Null> 0 <Null> "date" <Null> 0 9 1 <Null> <Null> 0 "datetime" 93 23 "'" "'" <Null> 1 0 3 <Null> 0 <Null> "datetime" 3 3 9 3 <Null> <Null> 12

And here is my ODBC DSN returned:

"TYPE_NAME" "DATA_TYPE" "COLUMN_SIZE" "LITERAL_PREFIX" "LITERAL_SUFFIX" "CREATE_PARAMS" "NULLABLE" "CASE_SENSITIVE" "SEARCHABLE" "UNSIGNED_ATTRIBUTE" "FIXED_PREC_SCALE" "AUTO_UNIQUE_VALUE" "DATE" 91 10 "'" "'" <Null> 1 0 3 <Null> 0 <Null> "DATE" 0 0 9 1 <Null> <Null> 0 "TIME" 92 8 "'" "'" <Null> 1 0 3 <Null> 0 <Null> "TIME" 0 0 9 2 <Null> <Null> 0 "TIMESTAMP" 93 19 "'" "'" <Null> 1 0 3 <Null> 0 <Null> "TIMESTAMP" 0 0 9 3 <Null> <Null> 0

Any idea why query folding failed on my date column, and what changes I need to make on custom data connector/ODBC driver? msmdsrv.14816.2018-06-29T16-04-16-027077.log