Summary:

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

Symptoms:

The following error message is displayed when running the report: 

An error occurred while running the report.
SQLError

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

Cause:

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.

Resolution:

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:

https://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2014/01/31/converting-vanilla-ntext-fields-from-an-old-install-of-crm-to-nvarchar-max.aspx