Jeremy Campbell
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-CalcIs sQty} + 1 >= {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.SE-CalcIs sQty} - 1 <= {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.SE-CalcIs sQty} <> 0
then "1"
//1a
else if {BAQReportResult.JobMtl.Re quiredQty} = 0
and {BAQReportResult.SE-CalcIs sQty} > 0
then "1a"
//2
else if {BAQReportResult.SE-CalcIs sQty} = 0
and {BAQReportResult.PORel.XRe lQty} >= {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.PORel.XRe lQty} <> 0
//and {BAQReportResult.PORel1.XR elQty} = 0
then "2"
//2a
else if {BAQReportResult.SE-CalcIs sQty} = 0
and {BAQReportResult.PORel.XRe lQty} < {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.PORel.XRe lQty} <> 0
and {BAQReportResult.PORel1.XR elQty} = 0
then "2a"
//3
else if {BAQReportResult.SE-CalcIs sQty} = 0
and {BAQReportResult.PORel.XRe lQty} = 0
and {BAQReportResult.PORel1.XR elQty} >= {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.PORel1.XR elQty} <> 0
then "3"
//3a
else if {BAQReportResult.SE-CalcIs sQty} = 0
and {BAQReportResult.PORel.XRe lQty} = 0
and {BAQReportResult.PORel1.XR elQty} < {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.PORel1.XR elQty} <> 0
then "3a"
//4
//else if {BAQReportResult.SE-CalcIs sQty} = 0
// and {BAQReportResult.PORel.XRe lQty} > 0
// and {BAQReportResult.PORel1.XR elQty} > 0
// then "4"
//5
else if {BAQReportResult.SE-CalcIs sQty} > 0
and {BAQReportResult.SE-CalcIs sQty} <> {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.PORel.XRe lQty} = 0
and {BAQReportResult.PORel1.XR elQty} > 0
then "5"
//6
else if {BAQReportResult.SE-CalcIs sQty} > 0
and {BAQReportResult.SE-CalcIs sQty} <> {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.PORel.XRe lQty} > 0
and {BAQReportResult.PORel1.XR elQty} = 0
then "6"
//6a
else if {BAQReportResult.SE-CalcIs sQty} > 0
and {BAQReportResult.SE-CalcIs sQty} <> {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.PORel.XRe lQty} > {BAQReportResult.SE-Remain ingQty}
then "6a"
//7
else if {BAQReportResult.SE-CalcIs sQty} > 0
and {BAQReportResult.PORel.XRe lQty} > 0
and {BAQReportResult.PORel1.XR elQty} > 0
then "7"
//8
else if {BAQReportResult.SE-CalcIs sQty} > 0
and {BAQReportResult.SE-CalcIs sQty} <> 0
and {BAQReportResult.PORel.XRe lQty} = 0
and {BAQReportResult.PORel1.XR elQty} = 0
then "8"
//9
else if {BAQReportResult.SE-CalcIs sQty} = 0
and {BAQReportResult.PORel.XRe lQty} = 0
and {BAQReportResult.PORel1.XR elQty} = 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-Ca lcIssQty} + 1 >= {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.SE-CalcIs sQty} - 1 <= {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.SE-CalcIs sQty} <> 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!
//1
if {BAQReportResult.SE-CalcIs
and {BAQReportResult.SE-CalcIs
and {BAQReportResult.SE-CalcIs
then "1"
//1a
else if {BAQReportResult.JobMtl.Re
and {BAQReportResult.SE-CalcIs
then "1a"
//2
else if {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel.XRe
//and {BAQReportResult.PORel1.XR
then "2"
//2a
else if {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel1.XR
then "2a"
//3
else if {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel1.XR
and {BAQReportResult.PORel1.XR
then "3"
//3a
else if {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel1.XR
and {BAQReportResult.PORel1.XR
then "3a"
//4
//else if {BAQReportResult.SE-CalcIs
// and {BAQReportResult.PORel.XRe
// and {BAQReportResult.PORel1.XR
// then "4"
//5
else if {BAQReportResult.SE-CalcIs
and {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel1.XR
then "5"
//6
else if {BAQReportResult.SE-CalcIs
and {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel1.XR
then "6"
//6a
else if {BAQReportResult.SE-CalcIs
and {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
then "6a"
//7
else if {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel1.XR
then "7"
//8
else if {BAQReportResult.SE-CalcIs
and {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel1.XR
then "8"
//9
else if {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel1.XR
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-Ca
and {BAQReportResult.SE-CalcIs
and {BAQReportResult.SE-CalcIs
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!
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-Ca lcIssQty} + 1 >= {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.SE-CalcIs sQty} - 1 <= {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.SE-CalcIs sQty} <> 0)
""
WhilePrintingRecords;
Global Booleanvar one;
//1
if one
then "1"
mlmcc
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-Ca
and {BAQReportResult.SE-CalcIs
and {BAQReportResult.SE-CalcIs
""
WhilePrintingRecords;
Global Booleanvar one;
//1
if one
then "1"
mlmcc
ASKER
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?
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?
ASKER
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-CalcIs sQty} + 1 >= {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.SE-CalcIs sQty} - 1 <= {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.SE-CalcIs sQty} <> 0;
OneA := {BAQReportResult.JobMtl.Re quiredQty} = 0
and {BAQReportResult.SE-CalcIs sQty} > 0;
Two := {BAQReportResult.SE-CalcIs sQty} = 0
and {BAQReportResult.PORel.XRe lQty} >= {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.PORel.XRe lQty} <> 0;
TwoA := {BAQReportResult.SE-CalcIs sQty} = 0
and {BAQReportResult.PORel.XRe lQty} < {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.PORel.XRe lQty} <> 0
and {BAQReportResult.PORel1.XR elQty} = 0;
Three := {BAQReportResult.SE-CalcIs sQty} = 0
and {BAQReportResult.PORel.XRe lQty} = 0
and {BAQReportResult.PORel1.XR elQty} >= {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.PORel1.XR elQty} <> 0;
ThreeA := {BAQReportResult.SE-CalcIs sQty} = 0
and {BAQReportResult.PORel.XRe lQty} = 0
and {BAQReportResult.PORel1.XR elQty} < {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.PORel1.XR elQty} <> 0;
//Four := {BAQReportResult.SE-CalcIs sQty} = 0
// and {BAQReportResult.PORel.XRe lQty} > 0
// and {BAQReportResult.PORel1.XR elQty} > 0;
Five := {BAQReportResult.SE-CalcIs sQty} > 0
and {BAQReportResult.SE-CalcIs sQty} <> {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.PORel.XRe lQty} = 0
and {BAQReportResult.PORel1.XR elQty} > 0;
Six := {BAQReportResult.SE-CalcIs sQty} > 0
and {BAQReportResult.SE-CalcIs sQty} <> {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.PORel.XRe lQty} > 0
and {BAQReportResult.PORel1.XR elQty} = 0;
SixA := {BAQReportResult.SE-CalcIs sQty} > 0
and {BAQReportResult.SE-CalcIs sQty} <> {BAQReportResult.JobMtl.Re quiredQty}
and {BAQReportResult.PORel.XRe lQty} > {BAQReportResult.SE-Remain ingQty};
//Seven := {BAQReportResult.SE-CalcIs sQty} > 0
// and {BAQReportResult.PORel.XRe lQty} > 0
// and {BAQReportResult.PORel1.XR elQty} > 0;
Eight := {BAQReportResult.SE-CalcIs sQty} > 0
and {BAQReportResult.SE-CalcIs sQty} <> 0
and {BAQReportResult.PORel.XRe lQty} = 0
and {BAQReportResult.PORel1.XR elQty} = 0;
Nine := {BAQReportResult.SE-CalcIs sQty} = 0
and {BAQReportResult.PORel.XRe lQty} = 0
and {BAQReportResult.PORel1.XR elQty} = 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.To talCost}
//1a
else if OneA
then {BAQReportResult.JobMtl.To talCost}
//2
else if Two
then {BAQReportResult.PORel.Rel Qty}/{BAQR eportResul t.PORel.XR elQty}*{BA QReportRes ult.PODeta il.DocUnit Cost}*{BAQ ReportResu lt.PORel.R elQty}
//2a
else if TwoA
then {BAQReportResult.PORel.Rel Qty}/{BAQR eportResul t.PORel.XR elQty}*{BA QReportRes ult.PODeta il.DocUnit Cost}*{BAQ ReportResu lt.PORel.R elQty}
//3
else if Three
then {BAQReportResult.PORel1.Re lQty}/{BAQ ReportResu lt.PORel1. XRelQty}*{ BAQReportR esult.PODe tail1.DocU nitCost}*{ BAQReportR esult.JobM tl.Require dQty}
//3a
else if ThreeA
then {BAQReportResult.PORel1.Re lQty}/{BAQ ReportResu lt.PORel1. XRelQty}*{ BAQReportR esult.PODe tail1.DocU nitCost}*{ BAQReportR esult.PORe l1.XRelQty }
//4
//else if Four
// then 4
//5
else if Five
then {BAQReportResult.PORel1.Re lQty}/{BAQ ReportResu lt.PORel1. XRelQty}*{ BAQReportR esult.PODe tail1.DocU nitCost}*{ BAQReportR esult.SE-R emainingQt y} +
{BAQReportResult.JobMtl.To talCost}
//6
else if Six
then {BAQReportResult.PORel.Rel Qty}/{BAQR eportResul t.PORel.XR elQty}*{BA QReportRes ult.PODeta il.DocUnit Cost}*{BAQ ReportResu lt.SE-Rema iningQty} +
{BAQReportResult.JobMtl.To talCost}
//6a
else if SixA
then {BAQReportResult.PORel.Rel Qty}/{BAQR eportResul t.PORel.XR elQty}*{BA QReportRes ult.PODeta il.DocUnit Cost}*{BAQ ReportResu lt.SE-Rema iningQty} +
{BAQReportResult.JobMtl.To talCost}
//7
//else if Seven
// then 7
//8
else if Eight
then {BAQReportResult.JobMtl.To talCost}
//9
else if Nine
then {BAQReportResult.JobMtl.To talCost}
else 0
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-CalcIs
and {BAQReportResult.SE-CalcIs
and {BAQReportResult.SE-CalcIs
OneA := {BAQReportResult.JobMtl.Re
and {BAQReportResult.SE-CalcIs
Two := {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel.XRe
TwoA := {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel1.XR
Three := {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel1.XR
and {BAQReportResult.PORel1.XR
ThreeA := {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel1.XR
and {BAQReportResult.PORel1.XR
//Four := {BAQReportResult.SE-CalcIs
// and {BAQReportResult.PORel.XRe
// and {BAQReportResult.PORel1.XR
Five := {BAQReportResult.SE-CalcIs
and {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel1.XR
Six := {BAQReportResult.SE-CalcIs
and {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel1.XR
SixA := {BAQReportResult.SE-CalcIs
and {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
//Seven := {BAQReportResult.SE-CalcIs
// and {BAQReportResult.PORel.XRe
// and {BAQReportResult.PORel1.XR
Eight := {BAQReportResult.SE-CalcIs
and {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel1.XR
Nine := {BAQReportResult.SE-CalcIs
and {BAQReportResult.PORel.XRe
and {BAQReportResult.PORel1.XR
""
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.To
//1a
else if OneA
then {BAQReportResult.JobMtl.To
//2
else if Two
then {BAQReportResult.PORel.Rel
//2a
else if TwoA
then {BAQReportResult.PORel.Rel
//3
else if Three
then {BAQReportResult.PORel1.Re
//3a
else if ThreeA
then {BAQReportResult.PORel1.Re
//4
//else if Four
// then 4
//5
else if Five
then {BAQReportResult.PORel1.Re
{BAQReportResult.JobMtl.To
//6
else if Six
then {BAQReportResult.PORel.Rel
{BAQReportResult.JobMtl.To
//6a
else if SixA
then {BAQReportResult.PORel.Rel
{BAQReportResult.JobMtl.To
//7
//else if Seven
// then 7
//8
else if Eight
then {BAQReportResult.JobMtl.To
//9
else if Nine
then {BAQReportResult.JobMtl.To
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
You could have 2 formulas, one with and one without. Group on the one wthout.
mlmcc
Where do you have the formulas?
mlmcc
mlmcc
ASKER
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.
The total formula is placed in the Group Header 4.
Where is the other formula?
mlmcc
mlmcc
ASKER
The formula for the variable is not placed anywhere at the moment.. Does this need to be placed in the report?
ASKER
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..
Sorry, like I said before, I'm just starting to learn about variables here..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help, it works now that its in the report:)
ASKER
Make sure your Variable Declaration Formula is in your report..
ASKER