Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

How to create Variables from Several If Statements in Crystal 2008

Posted on 2011-04-26
Medium Priority
598 Views
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!

0
Question by:SeyerIT
• 8
• 5

LVL 2

Author Comment

ID: 35467095
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..
0

LVL 101

Expert Comment

ID: 35467332
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
0

LVL 2

Author Comment

ID: 35467681
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?
0

LVL 2

Author Comment

ID: 35467943
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
0

LVL 101

Expert Comment

ID: 35467975
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
0

LVL 101

Expert Comment

ID: 35468029
Where do you have the formulas?

mlmcc
0

LVL 2

Author Comment

ID: 35468084
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.
0

LVL 101

Expert Comment

ID: 35468213
Where is the other formula?

mlmcc
0

LVL 2

Author Comment

ID: 35469355
The formula for the variable is not placed anywhere at the moment.. Does this need to be placed in the report?
0

LVL 2

Author Comment

ID: 35469446
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..
0

LVL 101

Accepted Solution

mlmcc earned 1000 total points
ID: 35469528
If it isn't on the report, it doesn't get evaluated.

mlmcc
0

LVL 2

Author Comment

ID: 35469881
Thanks for the help, it works now that its in the report:)
0

LVL 2

Author Closing Comment

ID: 35469891
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customerâ€™s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. â€¦
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater â€¦
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signatâ€¦
Integration Management Part 2
Suggested Courses
Course of the Month13 days, 9 hours left to enroll