TYPE_MISMATCH error while trying to apply filter on a custom report in Nx

Products

• CloudHealth

Issue/Introduction

User is seeing below error when trying to apply filter on a custom report created in Nx

"Error Message: TYPE_MISMATCH: line : IN value and list items must be the same type or coercible to a common type. Cannot find common type between date and varchar(10), all types (without duplicates): [date, varchar(10)] in sourceBundle"

Cause

This error occurs because the reporting engine is attempting to compare incompatible data types.
While the report's Date column is a true DATE type, the filtering system is injecting the filter values as strings (VARCHAR).

Resolution

Temporary Workaround: If you are running into this issue, you can resolve it by type casting the Date when creating the flexreport.

CAST(Date AS VARCHAR) AS Date

Example flexreport query:

{
  "sqlStatement": "SELECT SUM(Cost) AS SUM_Cost, CAST(Date AS VARCHAR) AS Date, AccountID AS AccountID, Project AS Project, ResourceID AS ResourceID, Service AS Service, usage_type AS Usage_type, workflow_name AS Workflow_name FROM <datasetName> GROUP BY AccountID, Project, ResourceID, Service, usage_type, workflow_name, Date ORDER BY Date ASC",
  "limit": -1
}

Our engineering team is actively working on a fix for an upcoming release .
If you have additional questions on this topic, please open a support ticket for further assistance.