Search code examples
crystal-reportsglobal-variablesformula

Display Object In Crystal Report If One Or More Records Contains Specific Data

I have a Crystal Report (CR Developer v11.5.12.1838) in which I only want to display certain totals in the Report Footer if one or more of the detail records contains a specific piece of information. To this end, I've created three formulas in the report:

  • InitDisplayLIATotals - Placed (and suppressed) in the Report Header section
Global BooleanVar LIA := False;
  • SetDisplayLIATotals - Placed in a (suppressed) Group Header (the only group header in the report) NOTE: I've also tried placing it in the Detail section
Global BooleanVar LIA;

WhilePrintingRecords;

If LIA = False Then
    If Not IsNull({mydata.liamp}) And Not (Trim({mydata.liamp}) = "") Then
        LIA := True;
  • DisplayLIATotals - Placed in the Report Footer and used in the suppression formula for the totals "label" object.
Global BooleanVar LIA;

LIA;

The intention is that the formulae should ONLY return True if one or more records in the current data set has a non-NULL value in the mydata.liamp field (regardless of record position). If all the records in the data set have a NULL value in this field, it should return False.

However, I can't seem to get it working correctly. I put the formula as a printable field in the Report Footer to confirm, and it always prints False, even when I know it should return True. When I place the formula in the Detail section (and make it visible), I see it return True on the first record, but all subsequent records show it as False, resulting in a False value in the Report Footer.

I've not mastered the art of writing formulae in Crystal Reports so I'm sure I'm just missing/overlooking something here, but I'm not sure what that would be. Could someone help me figure out why I can't reliably get it to return the correct value?

Solution

  • Thanks to a suggestion from @heringer in the question's comments, I was able to find the solution through some trial and error. The key is apparently in the WhilePrintingRecords; directive used by the Crystal Reports Formula Editor. For the report to properly evaluate the condition all the way throughout the report so that it returns the "correct" value for the Report Footer section, all three of the formulas require this directive set.

    I found this explanation in the SAP Community forums:

    WhileReadingRecords

    Forces the program to evaluate the formula while it is reading database record data.

    If you try to include a group in this formula, you will get an error message.

    WhilePrintingRecords

    Forces the program to evaluate the formula while it is printing database record data.

    Not terribly descriptive there, but my understanding of it is that the WhilePrintingRecords; directive causes the formula to wait to evaluate each data element until the report is actually being generated (printer, display, export, etc.), so all of the data has been loaded into each of the fields of the report.

    I don't think it would go amiss if someone who is more familiar with Crystal Reports could provide a better or more detailed explanation of these directives and the "multi-pass engine".

    EDIT - I found a question that might help to better explain the use of these directives:

    why use beforereadingrecords / whilereadingrecords / whileprintingrecords in crystal reports?

    The end result is the following:

    • InitDisplayLIATotals - Placed (suppressed) in the Report Header section
    WhilePrintingRecords;
Global BooleanVar LIA := False;
    • SetDisplayLIATotals - Placed (suppressed) in a Group Header (or Detail section)
    WhilePrintingRecords;
Global BooleanVar LIA;

If LIA = False Then
    If Not IsNull({mydata.liamp}) And Not (Trim({mydata.liamp}) = "") Then
        LIA := True;
    • DisplayLIATotals - Placed in the Report Footer and/or used in a suppression formula.
    WhilePrintingRecords;
Global BooleanVar LIA;

LIA;

    I hope this is helpful to anyone else who might be struggling with getting this technique to work.

    SIDE NOTE

    I actually was able to "work around" the problem to a certain extent by making use of a Running Total Field. I created a COUNT summary of the mydata.liamp field, which apparently only counts non-NULL values. Using this summary field in the suppression formula DID work because of the data in my testing data set, but it's an imperfect solution.

    The "problem" is that the field may contain empty string values which (I assume) would be included in the count. For my use case scenario, I wanted to be certain only values that were non-NULL and not empty count towards switching this value. The "three-formula trick" (a TechRepublic "QuickTip" article for reference) allows me to test for both possibilities.