Link to home
Start Free TrialLog in
Avatar of Jeremy Campbell
Jeremy CampbellFlag for United States of America

asked on

How to create Variables from Several If Statements in Crystal 2008

Here is the formula I'm working with that I would like to convert to several variables. I am using this formula in several different other formulas and everytime I need to tweak it I have to remember to tweak it in every other formula.. I assume there is a way to declare a variable for each if statement so that I can use the variable name in my other formulas and only have to come back to the declaration formula to make changes to all my if statements?

//1
if             {BAQReportResult.SE-CalcIssQty} + 1 >= {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.SE-CalcIssQty} - 1 <= {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.SE-CalcIssQty} <> 0
        then "1"
//1a
else if     {BAQReportResult.JobMtl.RequiredQty} = 0
        and {BAQReportResult.SE-CalcIssQty} > 0
        then "1a"
//2
else if     {BAQReportResult.SE-CalcIssQty} = 0
        and {BAQReportResult.PORel.XRelQty} >= {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.PORel.XRelQty} <> 0
        //and {BAQReportResult.PORel1.XRelQty} = 0
        then "2"
//2a
else if     {BAQReportResult.SE-CalcIssQty} = 0
        and {BAQReportResult.PORel.XRelQty} < {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.PORel.XRelQty} <> 0
        and {BAQReportResult.PORel1.XRelQty} = 0
        then "2a"
//3
else if     {BAQReportResult.SE-CalcIssQty} = 0
        and {BAQReportResult.PORel.XRelQty} = 0
        and {BAQReportResult.PORel1.XRelQty} >= {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.PORel1.XRelQty} <> 0
        then "3"
//3a
else if     {BAQReportResult.SE-CalcIssQty} = 0
        and {BAQReportResult.PORel.XRelQty} = 0
        and {BAQReportResult.PORel1.XRelQty} < {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.PORel1.XRelQty} <> 0
        then "3a"
//4
//else if     {BAQReportResult.SE-CalcIssQty} = 0
//        and {BAQReportResult.PORel.XRelQty} > 0
//        and {BAQReportResult.PORel1.XRelQty} > 0
//        then "4"
//5
else if     {BAQReportResult.SE-CalcIssQty} > 0
        and {BAQReportResult.SE-CalcIssQty} <> {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.PORel.XRelQty} = 0
        and {BAQReportResult.PORel1.XRelQty} > 0
        then "5"
//6
else if     {BAQReportResult.SE-CalcIssQty} > 0
        and {BAQReportResult.SE-CalcIssQty} <> {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.PORel.XRelQty} > 0
        and {BAQReportResult.PORel1.XRelQty} = 0
        then "6"
//6a
else if     {BAQReportResult.SE-CalcIssQty} > 0
        and {BAQReportResult.SE-CalcIssQty} <> {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.PORel.XRelQty} > {BAQReportResult.SE-RemainingQty}
        then "6a"
//7
else if     {BAQReportResult.SE-CalcIssQty} > 0
        and {BAQReportResult.PORel.XRelQty} > 0
        and {BAQReportResult.PORel1.XRelQty} > 0
        then "7"
//8
else if     {BAQReportResult.SE-CalcIssQty} > 0
        and {BAQReportResult.SE-CalcIssQty} <> 0
        and {BAQReportResult.PORel.XRelQty} = 0
        and {BAQReportResult.PORel1.XRelQty} = 0
        then "8"
//9
else if     {BAQReportResult.SE-CalcIssQty} = 0
        and {BAQReportResult.PORel.XRelQty} = 0
        and {BAQReportResult.PORel1.XRelQty} = 0
        then "9"
else "0"


Here is what I tried as a declaration statement and tested, which did not work..

Global BooleanVar one;
one := not({BAQReportResult.SE-CalcIssQty} + 1 >= {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.SE-CalcIssQty} - 1 <= {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.SE-CalcIssQty} <> 0)


I then changed the first "if" in the above formula to read:

Global Booleanvar one;
//1
if         one
        then "1"


It was not working though like this..

Any Ideas.. Hopefully my formulas make some since... :(

Thanks for the help!


Avatar of Jeremy Campbell
Jeremy Campbell
Flag of United States of America image

ASKER

Oh, I just realized I pasted one of my resulting formulas up above (the really long one) The variables would not be including the "Then" for any of the if statements.. The "Then" portion of the formula would be different for the other formulas I'm creating..
Avatar of Mike McCracken
Mike McCracken

Try adding WhilePrintingRecords; as the first line of the formulas.

Crystal doesn't save values in variables except in the last pass through the report.
Crystal also evaluates a formula as soon as it can.

WhilePrintingRecords;
Global BooleanVar one;
one := not({BAQReportResult.SE-CalcIssQty} + 1 >= {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.SE-CalcIssQty} - 1 <= {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.SE-CalcIssQty} <> 0)
""


WhilePrintingRecords;
Global Booleanvar one;
//1
if         one
        then "1"


mlmcc
So I get an error that says it will not let me group on a non-recurring field now.. I guess I cannot group based on variables?

That's unfortunate.. I guess I could leave my grouping formula the way it is and then use the variables on the other formulas? At least I will only have to update my variables in two formulas instead of 5 or so..

Can you verify this is the case or am I doing something incorrect?
I guess I am doing something incorrect in the format of how I'm declaring multiple variables..
None of my totals are coming out currently..

Here is my formula declaring all of the variables:

Whileprintingrecords;
Global BooleanVar One;
Global BooleanVar OneA;
Global BooleanVar Two;
Global BooleanVar TwoA;
Global BooleanVar Three;
Global BooleanVar ThreeA;
Global BooleanVar Four;
Global BooleanVar Five;
Global BooleanVar Six;
Global BooleanVar SixA;
Global BooleanVar Seven;
Global BooleanVar Eight;
Global BooleanVar Nine;
One := {BAQReportResult.SE-CalcIssQty} + 1 >= {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.SE-CalcIssQty} - 1 <= {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.SE-CalcIssQty} <> 0;
OneA := {BAQReportResult.JobMtl.RequiredQty} = 0
        and {BAQReportResult.SE-CalcIssQty} > 0;
Two := {BAQReportResult.SE-CalcIssQty} = 0
        and {BAQReportResult.PORel.XRelQty} >= {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.PORel.XRelQty} <> 0;
TwoA := {BAQReportResult.SE-CalcIssQty} = 0
        and {BAQReportResult.PORel.XRelQty} < {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.PORel.XRelQty} <> 0
        and {BAQReportResult.PORel1.XRelQty} = 0;
Three := {BAQReportResult.SE-CalcIssQty} = 0
        and {BAQReportResult.PORel.XRelQty} = 0
        and {BAQReportResult.PORel1.XRelQty} >= {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.PORel1.XRelQty} <> 0;
ThreeA := {BAQReportResult.SE-CalcIssQty} = 0
        and {BAQReportResult.PORel.XRelQty} = 0
        and {BAQReportResult.PORel1.XRelQty} < {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.PORel1.XRelQty} <> 0;
//Four := {BAQReportResult.SE-CalcIssQty} = 0
//        and {BAQReportResult.PORel.XRelQty} > 0
//        and {BAQReportResult.PORel1.XRelQty} > 0;
Five := {BAQReportResult.SE-CalcIssQty} > 0
        and {BAQReportResult.SE-CalcIssQty} <> {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.PORel.XRelQty} = 0
        and {BAQReportResult.PORel1.XRelQty} > 0;
Six := {BAQReportResult.SE-CalcIssQty} > 0
        and {BAQReportResult.SE-CalcIssQty} <> {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.PORel.XRelQty} > 0
        and {BAQReportResult.PORel1.XRelQty} = 0;
SixA := {BAQReportResult.SE-CalcIssQty} > 0
        and {BAQReportResult.SE-CalcIssQty} <> {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.PORel.XRelQty} > {BAQReportResult.SE-RemainingQty};
//Seven := {BAQReportResult.SE-CalcIssQty} > 0
//        and {BAQReportResult.PORel.XRelQty} > 0
//       and {BAQReportResult.PORel1.XRelQty} > 0;
Eight := {BAQReportResult.SE-CalcIssQty} > 0
        and {BAQReportResult.SE-CalcIssQty} <> 0
        and {BAQReportResult.PORel.XRelQty} = 0
        and {BAQReportResult.PORel1.XRelQty} = 0;
Nine := {BAQReportResult.SE-CalcIssQty} = 0
        and {BAQReportResult.PORel.XRelQty} = 0
        and {BAQReportResult.PORel1.XRelQty} = 0;
""


Then here is my Total Formula (This is the one that is displaying zeros now for all my different scenarios. I made a new one and put it side by side with my existing formula to make sure the numbers matched):

WhilePrintingRecords;
Global BooleanVar One;
Global BooleanVar OneA;
Global BooleanVar Two;
Global BooleanVar TwoA;
Global BooleanVar Three;
Global BooleanVar ThreeA;
Global BooleanVar Four;
Global BooleanVar Five;
Global BooleanVar Six;
Global BooleanVar SixA;
Global BooleanVar Seven;
Global BooleanVar Eight;
Global BooleanVar Nine;
//1
if      One
        then {BAQReportResult.JobMtl.TotalCost}
//1a
else if OneA
        then {BAQReportResult.JobMtl.TotalCost}
//2
else if  Two
        then {BAQReportResult.PORel.RelQty}/{BAQReportResult.PORel.XRelQty}*{BAQReportResult.PODetail.DocUnitCost}*{BAQReportResult.PORel.RelQty}
//2a
else if TwoA
        then {BAQReportResult.PORel.RelQty}/{BAQReportResult.PORel.XRelQty}*{BAQReportResult.PODetail.DocUnitCost}*{BAQReportResult.PORel.RelQty}
//3
else if Three
        then {BAQReportResult.PORel1.RelQty}/{BAQReportResult.PORel1.XRelQty}*{BAQReportResult.PODetail1.DocUnitCost}*{BAQReportResult.JobMtl.RequiredQty}
//3a
else if ThreeA
        then {BAQReportResult.PORel1.RelQty}/{BAQReportResult.PORel1.XRelQty}*{BAQReportResult.PODetail1.DocUnitCost}*{BAQReportResult.PORel1.XRelQty}
//4
//else if  Four
//        then 4
//5
else if Five
        then {BAQReportResult.PORel1.RelQty}/{BAQReportResult.PORel1.XRelQty}*{BAQReportResult.PODetail1.DocUnitCost}*{BAQReportResult.SE-RemainingQty} +
                {BAQReportResult.JobMtl.TotalCost}
//6
else if Six
        then {BAQReportResult.PORel.RelQty}/{BAQReportResult.PORel.XRelQty}*{BAQReportResult.PODetail.DocUnitCost}*{BAQReportResult.SE-RemainingQty} +
                {BAQReportResult.JobMtl.TotalCost}
//6a
else if SixA
        then {BAQReportResult.PORel.RelQty}/{BAQReportResult.PORel.XRelQty}*{BAQReportResult.PODetail.DocUnitCost}*{BAQReportResult.SE-RemainingQty} +
                {BAQReportResult.JobMtl.TotalCost}
//7
//else if  Seven
//        then 7
//8
else if Eight
        then {BAQReportResult.JobMtl.TotalCost}
//9
else if Nine
        then {BAQReportResult.JobMtl.TotalCost}
else 0
You can't group on a formula with WhilePrintingRecords

You could have 2 formulas, one with and one without.  Group on the one wthout.

mlmcc
Where do you have the formulas?

mlmcc
I assumed once I got the error that I wasn't going to be able to group on the formula, which is fine.. I will just leave that one as is and just use the variables in my other formulas (one of them is posted above to figure out totals)..

The total formula is placed in the Group Header 4.  
Where is the other formula?

mlmcc
The formula for the variable is not placed anywhere at the moment.. Does this need to be placed in the report?
Yeah it seems when I place that variable declaration in the report it starts to work.. I didn't realize that needed to physically be placed in there.. Also, it seems it has to be placed in a specific group otherwise it gets the wrong results or no results at all..

Sorry, like I said before, I'm just starting to learn about variables here..
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the help, it works now that its in the report:)
Make sure your Variable Declaration Formula is in your report..