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

SAP Knowledge Base Article - Public

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

Symptom

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.

Environment

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.

Resolution

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

Keywords

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

Product

SAP Crystal Reports, developer version for Microsoft Visual Studio