Changes to ReportDocument.RecordSelectionFormula no longer apply

[Former Member]

Hi,

I have found that since upgrading to SP15, setting ReportDocument.RecordSelectionFormula to anything else (e.g. appending additional filter criteria) resets the RecordSelectionFormula property to blank.

Same goes for ReportDocument.DataDefinition.RecordSelectionFormula property, whereas the read only (and undocumented) ReportDocument.DataDefinition.RecordSelectionFormulaRaw property returns what the ReportDocument.RecordSelectionFormula property was actually set to.

The ability to update the RecordSelectionFormula is critical to the way our reports are run so this needs addressed urgently. In the meantime I have rolled back to SP14 where it works fine.

Can this please be investigated and a hot fix issued?

Thanks,

Gareth

[former_member11696 · Accepted solution]

Works for me using the engine...

rpt.RecordSelectionFormula = @"{Customer.Country} = 'USA'";

What code are you using for ReportClientDocument?

Is the formula valid?

Here's how I get it:

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

CrystalDecisions.ReportAppServer.DataDefModel.ISCRFilter myRecordSelectionWithComments; // = new CrystalDecisions.ReportAppServer.DataDefModel.;

myRecordSelectionWithComments = rptClientDoc.DataDefController.DataDefinition.RecordFilter;

if (myRecordSelectionWithComments.FreeEditingText != null)

{

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

    btnRecordSelectionForm.Text = myRecordSelectionWithComments.FreeEditingText.ToString();

}

else

    btnRecordSelectionForm.Text = "No Record Selection formula";

And set it using Engine:

rpt.RecordSelectionFormula = @"{Customer.Country} = 'USA'";

Don

[Former Member · Accepted solution]

I'm using the Engine version in VB.Net & Framework v3.5 and simply calling:

rpt.RecordSelectionFormula = StringExpression

e.g.

rpt.RecordSelectionFormula = "not ({JobStatus.ExcludeFromReporting})"

[Former Member · Accepted solution]

Any update on this Don?

[Former Member · Accepted solution]

Don is on vacation until next year...

I'd start with creating the needed formula in the CR designer and testing it there. If it works there, then cut the formula out of the formula editor and hard code it in your code. No vars, no nothing. Does that work?

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow me on Twitter

Got Enhancement ideas? Use the SAP Idea Place

[former_member11696 · Accepted solution]

Hi Gareth,

Again it works for me so I suspect there is a synax error in the formula string you are try to set it to.

There was an update to our formula code where it no longers allow reserved characters:

"@", "?", "{", "}"

So as Ludek suggested, try the formula text you are setting in code in CR Designer and confirm it works there first. Could be some hidden control code embedded in your app that is now getting interpretted...

It could also be something else is causing the report to fail, check all of your formulae.

Attach one of your reports before the change and the formula text you are settign it to at runtime and I'll do some more testing.

Don

[Former Member · Accepted solution]

Hi Don,

After much testing I think I have now got to the bottom of this problem.

It has nothing to do with reserved characters, which were introduced in SP12 and I was using SP14 which appears to work. FYI, according to the documentation it is just the CR Designer that has been updated to prevent these reserved characters as it would appear that the Crystal Engine still allows them. This is just as well because we have lots of user created reports that make use of formula fields that start with a "?" to define a dynamic SQL Select statement as a string which we then search for and pick up to allow parameter lookups with dynamic data (using a custom parameter screen). Is there a particular reason for these characters (or that one in particular) to be reserved? In all the years we have been using Crystal Reports (since v7 or so) it has never been a problem? I might have to raise a separate issue on that one...

Anyway, it would appear that the ReportDocument.RecordSelectionFormula property is not evaluated instantly when the property set call returns and instead must get evaluated in the background or at a later stage in processing the report. This means that when code like the following is run more than once it fails:

ReportDocument.RecordSelectionFormula = ReportDocument.RecordSelectionFormula + Expr

For example (BTW, this is not actually what I am doing which is significantly more complicated but the end result is the same):

ReportDocument.RecordSelectionFormula = "{Table.Field1} = 'A'"
ReportDocument.RecordSelectionFormula = "(" + ReportDocument.RecordSelectionFormula + ") And {Table.Field2} = 'B'"
ReportDocument.RecordSelectionFormula = "(" + ReportDocument.RecordSelectionFormula + ") And {Table.Field3} = 'C'"

Which Results in ReportDocument.RecordSelectionFormula actually getting set to "() And {Table.Field3} = 'C'" instead of "(({Table.Field1} = 'A') And {Table.Field2} = 'B') And {Table.Field3} = 'C'" which was ultimately what was causing the report not to work.

The solution is therefore not to access the ReportDocument.RecordSelectionFormula property to read any existing formula other than just after loading the report and before changing it. Alternatively, accessing the ReportDocument.DataDefinition.RecordSelectionFormulaRaw property always seems to return the currently set formula regardless of the evaluation state of the ReportDocument.RecordSelectionFormula property.

I have updated our code to work in a different way and the problem has been resolved.

Thanks for your time,

Gareth

[Former Member]

Thanks very much for your investigative work, Gareth.

On one report I was doing the following :

string[] aryIDs = IDs.Split(',');

for (int c = 0; c < aryIDs.Length; c++) {

    rep.RecordSelectionFormula += "{HIREITEMS.ID}=" + aryIDs[c] +

        ((c + 1) < aryIDs.Length ? " Or " : "");

}

As you said, this fails in recent versions of Crystal Reports! It just makes rep.RecordSelectionFormula = ""

I have now changed this to :

string[] aryIDs = IDs.Split(',');

string rsf = "";

for (int c = 0; c < aryIDs.Length; c++) {

    rsf += "{HIREITEMS.ID}=" + aryIDs[c] +

        ((c + 1) < aryIDs.Length ? " Or " : "");

}

rep.RecordSelectionFormula = rsf;

...which works perfectly.

No idea why they have to break things like this!

Thanks again

[former_member11696]

Thanks Guys for the update...

The reason we reserved those characters is because they are specific when the engine evaluates the report at runtime. CR Designer is a design tool so you can save errors in the report without it breaking.

The SDK on the other hand "assumes" the report is 100% valid, meaning no reserved characters are used, our parsing code has to load all formulae and object properties to evaluate them in the formatting engine.

So if you use something that is not allowed it can cause problems, I highly recommend you validate your report output data to confirm they are working properly.

The change was likely done for performance reasons, which is why a new API was created to get formula with comments:

myRecordSelectionWithComments.FreeEditingText

And there has always been an "Order" when processing a report, things can change that may look like it was broken but some change now forces you to do it in the right order.

Another example is when connecting to a Stored Procedure with Parameters, the parameter collection must be updated before the connection is made, where as if using a CR Parameter then the connection can be made first....

Also be aware that CR for .NET relies on the the Windows Framework heavily... if MS changes things it can affect how you code when using CR Assemblies...

Anyways, glad you guys figured it out...

Don