Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Crystal Report: How to evaluate multiple IF statements in one formula?

Background

  • I'm attempting to do some nice looking validation on the details row of my report.
  • I have several formulas named as Assert statements, that return false if they fail a test and true if they pass.

Goal

  • I would like to create an array that stores "rule violations" and then displays them in a field at the end of the row, under a heading called "Broken Rules"

What I've done so far

  • Created an Array and Initialized it as an empty string array in the report header
  • Created a formula to do evaluation of each rule, increment the array, and add the broken rule number (this is repeated code for each rule, nothing fancy). This gets added into a suppressed details section above my details display.
  • Created a formula that is a join of the elements in the rules broken array. This is a formula that is displayed along with my detail fields.
  • Created a formula to set the rules broken array to empty. This goes in a suppressed details section after my details display.

Problem

  • Crystal doesn't seem to allow an "end if" statement that I can find.
  • As such, it appears I can only evaluate one If statement and not multiples within a single formula.
  • This means I can't do multiple ifs, one for each rule.

Sample Code

Creation of the Array (a formula called Init_StringVar_Array_RulesBroken):

//@Init
//This goes into the report header
WhilePrintingRecords;

//initializes the array of broken rules which we'll add to during details
StringVar Array RulesBroken;
"";

Sample of first three rule assessments that increment arrays and add values (this is in a formula called Increment_StringVar_Array_RulesBroken):

//@Increment
//Goes before the details section is displayed

//accesses the shared variable
WhilePrintingRecords;
StringVar Array RulesBroken;

//separate if statement for each assert statement

//01
if not {@Assert_01_IfCrewIsConstructionCrew_CBFlagShouldBeYesOrDirect} then
Redim Preserve RulesBroken[UBound(RulesBroken) + 1]; //extends the array to be able to hold one more item than it does currently
RulesBroken[UBound(RulesBroken)] := "01"; //adds the new string into the array

//02
if not {@Assert_02_IfCrewIsConstructionCrew_AndCBFlagIsDirect_WONumberShouldStartWithC} then
Redim Preserve RulesBroken[UBound(RulesBroken) + 1]; //extends the array to be able to hold one more item than it does currently
RulesBroken[UBound(RulesBroken)] := "02"; //adds the new string into the array

//03
if not {@Assert_03_IfCrewIsDesign_AndCBFlagIsDirect_WONumberShouldStartWithD} then
Redim Preserve RulesBroken[UBound(RulesBroken) + 1]; //extends the array to be able to hold one more item than it does currently
RulesBroken[UBound(RulesBroken)] := "03"; //adds the new string into the array

Any Ideas?

  • Is there an If / then / end if capability in Crystal Reports?
  • If not, is there a workaround for this sort of thing in Crystal Reports? do I need to create multiple formulas for each one and make sure they're placed after the other or something like that?

Thanks in advance for any help!

like image 422
SeanKilleen Avatar asked Apr 19 '12 01:04

SeanKilleen


People also ask

How do you add two formula fields in Crystal report?

To add the formula field, go to the Field Explorer panel. Click on Formula Fields to select it. Right click on Formula Fields, then select New.

What is IIF in Crystal report?

IIF and IsNull are functions in Crystal Reports that are used in formulas to test fields for blanks, empty strings, missing values and NULL, then return some valid output. This is especially helpful when preparing a report, since these values can cause blank lines to be printed.


1 Answers

The simplest way to do this with the code you have is wrapping the if blocks in parentheses and separating them with semicolons:

//01
(
    if not {@Assert_01_IfCrewIsConstructionCrew_CBFlagShouldBeYesOrDirect} then
        Redim Preserve RulesBroken[UBound(RulesBroken) + 1];
        RulesBroken[UBound(RulesBroken)] := "01"
    else ""
);

//02
(
    if not {@Assert_02_IfCrewIsConstructionCrew_AndCBFlagIsDirect_WONumberShouldStartWithC} then
        Redim Preserve RulesBroken[UBound(RulesBroken) + 1];
        RulesBroken[UBound(RulesBroken)] := "02"
    else ""
);

//03
(
    if not {@Assert_03_IfCrewIsDesign_AndCBFlagIsDirect_WONumberShouldStartWithD} then
        Redim Preserve RulesBroken[UBound(RulesBroken) + 1];
        RulesBroken[UBound(RulesBroken)] := "03"
    else ""
);

I added indentation indicating how Crystal interprets the blocks.

like image 66
paulmelnikow Avatar answered Nov 03 '22 19:11

paulmelnikow