Cannot sort a Cross tab report using the "by translation" option.


The following error message is displayed when running the report: 

An error occurred while running the report.

The following appears in the logs:

errormsg 15 SELECT comp_sector,comp_type, COUNT(*) AS CrossCount FROM vreportcompany WITH (NOLOCK) LEFT OUTER JOIN Custom_Captions comp_sector_OrderByCapt ON comp_sector_OrderByCapt.Capt_Family = 'comp_sector' AND comp_sector_OrderByCapt.Capt_Code = comp_sector LEFT OUTER JOIN Custom_Captions comp_type_OrderByCapt ON comp_type_OrderByCapt.Capt_Family = 'comp_type' AND comp_type_OrderByCapt.Capt_Code = comp_type GROUP BY comp_sector,comp_type,comp_sector_OrderByCapt.Capt_UK,comp_type_OrderByCapt.Capt_Order ORDER BY COALESCE(CAST(comp_sector_OrderByCapt.Capt_UK as NVARCHAR) , comp_sector),COALESCE(comp_type_OrderByCapt.Capt_Order, comp_type)
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator


The cause of this issue is that for a field to be sorted on it must be an nVarchar but in this case it is an nText field.


To resolve this issue the nText fields need to be converted to nVarchar fields.

There is a great article on the community describing how you would do this: