Skip to Content

Changing RecordSelection formula at runtime in CR for VS .NET

In RDC-times we used to change this command to change the value of a formula from the ERP-app:

Private oCrystalReport As CRAXDRT.Report

oCrystalReport.FormulaFields.GetItemByName(sFormulaName).Text = FormatCRField(iFormat, vntValue)

Now in VS.Net I use this:

Private _crystalMainReport AsNewReportDocument

_crystalMainReport.DataDefinition.FormulaFields(name).Text = FormatParameterField(format, value, rtfFormat)

Let’s say in CR-Designer I have the formula TestFormula:

  • with Crystal Syntax: initialized to ““aTestString”” (in the report) there is no problem when changing the formula at runtime to “NewString” with RDC or RAS. The Report comes out correctly.
  • with Basic Syntax: initialized to “formula=”aTestString”” (in the report), RDC has also no problem changing it to “NewString” (without ”formula=”) – the report is ok;
    but RAS will fail while printing the report, as the CR Designer won’t accept this formula (error in formula).

Is this normal? What is the rule of thumb with RAS?

Should I test for the formula syntax each time I set it with:

DirectCast(_crystalMainReport.ReportClientDocument.DataDefinition.FormulaFields(oldFormulaFieldIndex), CrystalDecisions.ReportAppServer.DataDefModel.FormulaField).Syntax = CrystalDecisions.ReportAppServer.DataDefModel.CrFormulaSyntaxEnum.crFormulaSyntaxBasic

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Oct 18, 2016 at 02:20 PM

    Hi Patrick,

    Bizarre, I did answer this but I don't see it....

    My mistake, I was testing the Record selection formula for a case and got it mixed up with this post...

    There is a way to check Formula:

    btnCount.Text = rptClientDoc.DataDefController.DataDefinition.FormulaFields.Count.ToString();

    // Get back all the formulafields in the report
    CrystalDecisions.ReportAppServer.DataDefModel.Fields boFields;
    CrystalDecisions.ReportAppServer.DataDefModel.Field boField;
    CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject boFieldObject;
    boFields = rptClientDoc.DataDefController.DataDefinition.FormulaFields;

    foreach (FormulaField resultField in rptClientDoc.DataDefController.DataDefinition.FormulaFields)
    {
    String FormulaMessage;
    textBox1 = resultField.LongName.ToString();
    btnReportObjects.Text += textBox1;
    btnReportObjects.AppendText(" : Used: " + resultField.UseCount.ToString() + " times'End' ");
    ++flcnt;
    btnCount.Text = flcnt.ToString();

    try
    {
    FormulaMessage = "\n" + rptClientDoc.DataDefController.FormulaFieldController.Check(resultField);
    }
    catch (Exception ex)
    {
    btnReportObjects.Text += "\n" + ex.Message + "\n";
    btnReportObjects.AppendText("");
    FormulaMessage = "";
    resultField.Text = "1234";
    boFields[flcnt].Name.ToString();

    This is all in my Parameter Test app by the way...

    And yes you are doing it correctly.

    Don

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 14, 2016 at 02:54 PM

    Hi Patrick,

    Unfortunately there is no .Check() method when setting the record selection formula.

    All I can suggest is to validate the format and syntax in CR Designer first.

    Also, you can get the Record Selection formula this way, both as what you see in CR Designer and what the QueryBuilder will generate when sending it off to the DB Client:

    // Record selection formula with comments included can only be retrieve via RAS

    CrystalDecisions.ReportAppServer.DataDefModel.ISCRFilter myRecordSelectionWithComments;

    myRecordSelectionWithComments = rptClientDoc.DataDefController.DataDefinition.RecordFilter;

    if (myRecordSelectionWithComments.FreeEditingText != null)

    {

    //myRecordSelectionWithComments.FreeEditingText = rptClientDoc.DataDefController.RecordFilterController.GetFormulaText();

    btnRecordSelectionForm.Text = "With Comments: \n" + myRecordSelectionWithComments.FreeEditingText.ToString();

    btnRecordSelectionForm.AppendText("\n\n");

    btnRecordSelectionForm.AppendText("\nWithout Comments:\n" + rpt.RecordSelectionFormula.ToString());

    btnRecordSelectionForm.AppendText("\n");

    }

    else

    btnRecordSelectionForm.Text = "No Record Selection formula";

    Noted in KBA - 1810170 - reportObject.RecordSelectionFormula returns no comments within the formula text

    Don

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 14, 2016 at 03:15 PM

    Thanks Don for your effort.

    Because the values for the formulas come from the ERP and are set on the fly, I can't test them in designer. So I must test the syntax-type for Crystal or Basic and then, appropriately set the value with a transformation.

    1) Was my syntax-type-test correct - or is there a better way? what about performance?

    2) Is there anywhere a built-in function which translates a formula from one syntax to another?

    3) Were you aware that the internal formula processing changed between RDC (CR XI) and CR VS2010 ?

    Patrick

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 19, 2016 at 11:38 AM

    I took the decision to change the syntax-type of those formulas I set from my program to Crystal-syntax, because I know what I will write in the formula.

    This works well for the main report, but not for a formula in a subreport:

    If checkMainReport Then
                ' MainReport
                With _crystalMainReport.ReportClientDocument.DataDefinition
                    Dim formulaFieldIndex = .FormulaFields.Find(formulaName, CrFieldDisplayNameTypeEnum.crFieldDisplayNameName, CeLocale.ceLocaleUserDefault)
                    If formulaFieldIndex >= 0 Then
                        If DirectCast(.FormulaFields(formulaFieldIndex), FormulaField).Syntax = CrFormulaSyntaxEnum.crFormulaSyntaxBasic Then
                            DirectCast(.FormulaFields(formulaFieldIndex), FormulaField).Syntax = CrFormulaSyntaxEnum.crFormulaSyntaxCrystal
                        End If
                    End If
                End With
            Else
                ' SubReport
                If _crystalSubReportClientDoc IsNot Nothing Then
                    With _crystalSubReportClientDoc.DataDefController.DataDefinition
                        Dim formulaFieldIndex = .FormulaFields.Find(formulaName, CrFieldDisplayNameTypeEnum.crFieldDisplayNameName, CeLocale.ceLocaleUserDefault)
                        If formulaFieldIndex >= 0 Then
                            If DirectCast(.FormulaFields(formulaFieldIndex), FormulaField).Syntax = CrFormulaSyntaxEnum.crFormulaSyntaxBasic Then
                                DirectCast(.FormulaFields(formulaFieldIndex), FormulaField).Syntax = CrFormulaSyntaxEnum.crFormulaSyntaxCrystal
                            End If                        
                        End If
                    End With
                End If
            End If

    The Syntax-Type has been correctly changed for a subreport-formula, but the Check() states out an error in the formula (The remaining text....).

    PS. I use FormulaFieldController.Modify() to set the value of the formula in the subreport.

    Any ideas?

    Patrick

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 19, 2016 at 12:51 PM

    Hi Patrick,

    Copy the formula in debug mode and paste it into CR Designer and see what it is complaining about.

    Look for extra line feeds etc., Word can show you the control characters.

    Put a try/catch around it also and catch the exception, it should list what the error is.

    Don

    Add comment
    10|10000 characters needed characters exceeded