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
13
Medium Priority
?
598 Views
Last Modified: 2012-06-21
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
Comment
Question by:SeyerIT
  • 8
  • 5
13 Comments
 
LVL 2

Author Comment

by:SeyerIT
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

by:mlmcc
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

by:SeyerIT
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 2

Author Comment

by:SeyerIT
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

by:mlmcc
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

by:mlmcc
ID: 35468029
Where do you have the formulas?

mlmcc
0
 
LVL 2

Author Comment

by:SeyerIT
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

by:mlmcc
ID: 35468213
Where is the other formula?

mlmcc
0
 
LVL 2

Author Comment

by:SeyerIT
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

by:SeyerIT
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

by:
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

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

Author Closing Comment

by:SeyerIT
ID: 35469891
Make sure your Variable Declaration Formula is in your report..
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question