Dynamic Query Item Used for Sorting

Question

I'm using Cognos Framework Manager and I'm creating a Data Item for a dynamic sort. I'm creating the Data Item using a CASE WHEN, here's my sample code:

CASE #prompt('SortOrder', 'string')#
WHEN 'Date' THEN <Date Column>
WHEN 'ID' THEN <String Column>
END

I'm getting this error QE-DEF-0405 Incompatible data types in case statement. Although I can cast the date column into a string wouldn't that make sort go wrong for the 'date' option? Should I cast the date column in a different way, cast the whole case, or am I barking at the wrong tree? In line with my question, should there be a general rule when creating dynamic columns via CASE with multiple column data types?

Answer 1

Column in Framework Manager should have datatype. Only one datatype. So you need to cast your date column to correctly sortable string. E.g. 'yyyy-mm-dd' format.

Answer 2

You are using the two different types of data format, so in prompt function use token instead of string (#prompt('sortorder','token')#)