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