SAP Knowledge Base Article - Public

2525150 - SQLExpressions registers incorrect data type when adding to record selection formula


When our custom code that uses Crystal APIs Introduce SQLExpresssions, there data type is inherently seen as string regardless of the datatype specified. This gets corrected only when we open the template in designer and open the SQLExpression field and save it back.


Crystal Reports for Visual Studio SP 19 and above

Reproducing the Issue

Please provide step-by-step instructions on how to reproduce your issue:

1. //Add formula using AddByName and then fetch it and explicitly set type                 boReportClientDocument.DataDefController.FormulaFieldController.AddByName("ClaimPaid_1X", @"XXXCLAIMFINVIEW.PAID_1X", CrFormulaSyntaxEnum.crFormulaSyntaxSQL);

FormulaField ff = (FormulaField)boReportClientDocument.DataDefController.DataDefinition.FormulaFields.FindField("{%ClaimPaid_1X}", CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula);

ff.Type = CrFieldValueTypeEnum.crFieldValueTypeNumberField;                 boReportClientDocument.DataDefController.FormulaFieldController.Modify(ff, ff);

2. Use a record selection formula comparing with another numeric field:

var recordSelectionFormula = @"({%ClaimPaid_1X} = 750.0)";

3. Run the report you will encounter an error "A string is required here"

However, if you later open that updated template in designer and lookup the new SQLExpression field (use a DB field selector there), and save the template, the problem would vanish.


This issue has been escalated to R&D for a fix and set for SP 22.

As a work around add the ToNumber() function to convert the SQLExpression to a number


CR for VS, SQLExpresssions, FormulaFieldController, CrFormulaSyntaxEnum.crFormulaSyntaxSQL, SP 22 , KBA , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , Problem


SAP Crystal Reports, developer version for Microsoft Visual Studio