MUX Reporting - SQL CASE statements in Data Provider queries define String attributes as Large String

Products

• Clarity PPM SaaS
• Clarity FedRAMP
• Clarity PPM On Premise

Issue/Introduction

In the Modern UX (MUX) Reporting module, when a SQL SELECT CASE expression is used against a standard string field within a Data Provider query, the system incorrectly evaluates the computed column and treats the field as a Large String rather than a standard String.

Steps to Reproduce: 

1. In the Modern UX (MUX), navigate to the Reporting module.
2. Create a new Data Provider and ensure the Include Query checkbox is selected.
3. On the Query tab, define a new query using a CASE statement against a standard string attribute. For example:

   SELECT CASE
       WHEN investment_type_key = 'project' THEN 'Project'
       ELSE investment_type_key
   END AS type_key
   FROM dwh_inv_investment

4. Navigate to the Attributes tab to verify the generated attribute properties.

Expected Results: The computed attribute (type_key) should be recognized and displayed as a standard String data type.

Actual Results: The attribute is incorrectly defined as Large String - Plain Text, which can impact downstream report formatting, sorting, and filtering.

Environment

Clarity 16.4.2 

Cause

DE207788

Resolution

Fixed in 

• Clarity 16.4.3

Workaround: To ensure the attribute is recognized as a standard String, you must explicitly wrap the CASE expression in a CAST() function, defining the data type to match the original field's VARCHAR length.

Example Workaround Query: 

SELECT CAST(
    CASE
        WHEN investment_type_key = 'project' THEN 'Project'
        ELSE investment_type_key
    END AS varchar(48)
) AS type_key
FROM dwh_inv_investment