FAO Naith

[Guest_imported]

Naith,

Yesterday you helped me with that nested if variable statement, as follows (ignoring the declarations): -

If {FLD1} = 100
Then
If {FLD2} = "A" Then Var1 := Var1 + {FLD3}
Else
If {FLD2} = "B" Then Var2 := Var2 + {FLD3};
If {FLD1} = 200
Then
If {FLD2} = "A" Then VarA := VarA + {FLD3}
Else
If {FLD2} = "B" Then VarB := VarB + {FLD3};

That was great but I would now like to show a running total value as well and cannot seem to get it to work using this approach - can you please help?

Is this related to the absence of the "ELSE Var" syntax at the end of each line? The closest I seem to be able to get is as follows: -

If {FLD1} = 100
Then
If {FLD2} = "A" Then Var1 := Var1 + {FLD3} else Var1
Else
If {FLD2} = "B" Then Var2 := Var2 + {FLD3} else Var2;
If {FLD1} = 200
Then
If {FLD2} = "A" Then VarA := VarA + {FLD3} else VarA
Else
If {FLD2} = "B" Then VarB := VarB + {FLD3} else VarB;

However this does not work for the "If {FLD1} = 200" clause and it will not allow me to add more than 2 lines into the secondary {FLD2} clause.

 

[Naith]

Hey Matt,

I'm not sure I understand what you're getting at here. Your whole formula is comprised of 4 running totals. Are you trying to show the incrementation of each variable value throughout the report? Show me some example data with a short explanation of what's going on, so I can get a picture of what you want to do.

By the by, I'd say that almost every individual who responds to a query in the Crystal forums is pretty switched on. If you exclude all of them in your post title, and the person you've marked the post FAO for isn't around, you could be waiting a very long time for a response, when really, quite a few people may have been able to help you.

Anyway, chuck up that example, and let's see what we got.

Naith

 

[Guest_imported]

Naith,

I am trying to calculate (rather then show) the incrementation of each variable through the report, with a view to showing them seperately at the end.

As you say the formula I've got is essentially supposed to be four running totals incremented after being tested by the nested if statement. However the way I currently have it does not work: -

If {FLD1} = 100
Then
If {FLD2} = "A" Then Var1 := Var1 + {FLD3} else Var1
Else
If {FLD2} = "B" Then Var2 := Var2 + {FLD3} else Var2;
If {FLD1} = 200
Then
If {FLD2} = "A" Then VarA := VarA + {FLD3} else VarA
Else
If {FLD2} = "B" Then VarB := VarB + {FLD3} else VarB;

I know there are other ways to do this (such as the one I started with yesterday) but I specifically wanted to use the nesting you outlined yesterday because of the volume of data involved; my example, while representative of what I am trying to achieve, shows only 4 out of the 28 variables that I want to include.

Thanks, Matt.

 

[Naith]

Regardless of if you use my nest, or your multi-if, a formula which calls each variable at the end of the report will display the relevant variable value.

Like:
//@Formula1 - Var1 Call
WhilePrintingRecords;
NumberVar Var1;

//@Formula2 - Var2 Call
WhilePrintingRecords;
NumberVar Var2;

...etcetera for all your variables. These would be placed in the report footer.

I hope I've understood what you meant, but if not, please set me straight.

Naith

 

[Guest_imported]

Sorry I'm not explaining myself very well.

I've got no problem with the displaying of the variables at the bottom of the report its the actual calculation that is not working, i.e. - the following code: -

If {FLD1} = 100
Then
If {FLD2} = "A" Then Var1 := Var1 + {FLD3} else Var1
Else
If {FLD2} = "B" Then Var2 := Var2 + {FLD3} else Var2;
If {FLD1} = 200
Then
If {FLD2} = "A" Then VarA := VarA + {FLD3} else VarA
Else
If {FLD2} = "B" Then VarB := VarB + {FLD3} else VarB;

I am sure that it is not working because I am displaying the variables (via another formula) during the calculations and the figures do not stack up.

There is something wrong with the above code and it is this I was hoping you could help with. Can you spot anything in it?

Thanks again for your time, Matt.

 

[Naith]

Would you mind posting up a sample of data and demonstrating what you expect the formula to do, compared with what it's actually doing?

Naith

 

[Guest_imported]

Okay as an example (I've put the actual values in each column as opposed to the A's & B's I had in my formula)....

---REPORT COLUMNS--- ----What I expect----
{FLD2} {FLD1} {FLD3} Var1 Var2 VarA VarB
AAS01 9009 5 5 0 0 0
AAS01 9200 2 5 2 0 0
AAS01 9009 4 9 2 0 0
AAS01 9200 3 9 5 0 0
AAS02 9009 1 9 5 1 0
AAS02 9009 1 9 5 2 0
AAS02 9200 6 9 5 2 6
AAS02 9200 8 9 5 2 14

So basically what I'm trying to do is say where: -
AAS01 and 9009 increment Var1
AAS01 and 9200 increment Var2
AAS02 and 9009 increment VarA
AAS02 and 9200 increment VarB

What is happening is that the values calculated initially (i.e. - Var1) are being passed to the other Variables, which is obviously not right. So in the above example everything is being set to 5 and then 9 because that's what Var1 says.

Matt.

 

[Naith]

You don't need me to tell you that what you're experiencing is very odd.

I just tested the original nested formula it does what I expect it to: namely, act like you suggest in your "What I expect" columns. The "Else Variable" technique formula was less reliable, because "Else Var1" catchment shuts down the alternative possibilities before they can be processed.

I'm inclined to think that the variables are inheriting the initial values from another formula in the report.

Can you paste the exact representation of the formula, as it is at the moment? (Without 100, 'A', 'B', etc). Or, if your data isn't terribly sensitive, leave your email and I'll take a look at your report myself.

Naith

 

[synapsevampire]

You might consider using Running Totals for this, and place the formula for each in the Evaluate formula.

"So basically what I'm trying to do is say where: -
AAS01 and 9009 increment Var1
AAS01 and 9200 increment Var2
AAS02 and 9009 increment VarA
AAS02 and 9200 increment VarB"

Variables and IF's will accomplish the same thing, but I see no advantage to doing so, and Running Totals should lend themselves to maintenance more readily.

<shrug>

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Guest_imported]

Naith,

Right here it is with the actual database names, var names and values from my report. Although it is quite long at the moment the bulk of the If statements are actually commented out and there are only four which are active.

I think some of the lines will show up wrapped in this text window, but in the formula these are a single line, i.e. this bit: -
if right({TTISFC001400.T$MITM},2) = &quot;01&quot; then fmi_hrs_1 := fmi_hrs_1 + {TTIHRA100400.T$HREA}.

I have tried adding the Else Variable syntax at the end of each statement but with no joy.

Thanks once again for your efforts with this, Matt.

whileprintingrecords;
global numbervar fmi_hrs_1:=
global numbervar fmi_hrs_2:=
global numbervar fmi_hrs_3:=
global numbervar fmi_hrs_4:=
global numbervar fmi_hrs_5:=
global numbervar fmi_hrs_6:=
global numbervar fmi_hrs_9:=
global numbervar ncr_hrs_1:=
global numbervar ncr_hrs_2:=
global numbervar ncr_hrs_3:=
global numbervar ncr_hrs_4:=
global numbervar ncr_hrs_5:=
global numbervar ncr_hrs_6:=
global numbervar ncr_hrs_9:=
global numbervar mtl_hrs_2:=
global numbervar mtl_hrs_1:=
global numbervar mtl_hrs_3:=
global numbervar mtl_hrs_4:=
global numbervar mtl_hrs_5:=
global numbervar mtl_hrs_6:=
global numbervar mtl_hrs_9:=
global numbervar stg_hrs_1:=
global numbervar stg_hrs_2:=
global numbervar stg_hrs_3:=
global numbervar stg_hrs_4:=
global numbervar stg_hrs_5:=
global numbervar stg_hrs_6:=
global numbervar stg_hrs_9:=

if {TTIHRA100400.T$TANO} in [9200,9007]
then
if right({TTISFC001400.T$MITM},2) = &quot;01&quot; then fmi_hrs_1 := fmi_hrs_1 + {TTIHRA100400.T$HREA}
else
if right({TTISFC001400.T$MITM},2) = &quot;02&quot; then fmi_hrs_2 := fmi_hrs_2 + {TTIHRA100400.T$HREA};
//else
//if right({TTISFC001400.T$MITM},2) = &quot;03&quot; then fmi_hrs_3 := fmi_hrs_3 + {TTIHRA100400.T$HREA}
//else
//if right({TTISFC001400.T$MITM},2) = &quot;04&quot; then fmi_hrs_4 := fmi_hrs_4 + {TTIHRA100400.T$HREA}
//else
//if right({TTISFC001400.T$MITM},2) = &quot;05&quot; then fmi_hrs_5 := fmi_hrs_5 + {TTIHRA100400.T$HREA}
//else
//if right({TTISFC001400.T$MITM},2) = &quot;06&quot; then fmi_hrs_6 := fmi_hrs_6 + {TTIHRA100400.T$HREA}
//else
//if right({TTISFC001400.T$MITM},2) = &quot;09&quot; then fmi_hrs_9 := fmi_hrs_9 + {TTIHRA100400.T$HREA};

if {TTIHRA100400.T$TANO} = 9009
then
if right({TTISFC001400.T$MITM},2) = &quot;01&quot; then ncr_hrs_1 := ncr_hrs_1 + {TTIHRA100400.T$HREA}
else
if right({TTISFC001400.T$MITM},2) = &quot;02&quot; then ncr_hrs_2 := ncr_hrs_2 + {TTIHRA100400.T$HREA};
//else
//if right({TTISFC001400.T$MITM},2) = &quot;03&quot; then ncr_hrs_3 := {TTIHRA100400.T$HREA} + ncr_hrs_3
//else
//if right({TTISFC001400.T$MITM},2) = &quot;04&quot; then ncr_hrs_4 := {TTIHRA100400.T$HREA} + ncr_hrs_4
//else
//if right({TTISFC001400.T$MITM},2) = &quot;05&quot; then ncr_hrs_5 := {TTIHRA100400.T$HREA} + ncr_hrs_5
//else
//if right({TTISFC001400.T$MITM},2) = &quot;06&quot; then ncr_hrs_6 := {TTIHRA100400.T$HREA} + ncr_hrs_6
//else
//if right({TTISFC001400.T$MITM},2) = &quot;09&quot; then ncr_hrs_9 := {TTIHRA100400.T$HREA} + ncr_hrs_9;

//if {TTIHRA100400.T$TANO} = 9010
//then
// if right({TTISFC001400.T$MITM},2) = &quot;01&quot; then mtl_hrs_1 := {TTIHRA100400.T$HREA} + mtl_hrs_1
// else
// if right({TTISFC001400.T$MITM},2) = &quot;02&quot; then mtl_hrs_2 := {TTIHRA100400.T$HREA} + mtl_hrs_2
// else
// if right({TTISFC001400.T$MITM},2) = &quot;03&quot; then mtl_hrs_3 := {TTIHRA100400.T$HREA} + mtl_hrs_3
// else
// if right({TTISFC001400.T$MITM},2) = &quot;04&quot; then mtl_hrs_4 := {TTIHRA100400.T$HREA} + mtl_hrs_4
// else
// if right({TTISFC001400.T$MITM},2) = &quot;05&quot; then mtl_hrs_5 := {TTIHRA100400.T$HREA} + mtl_hrs_5
// else
// if right({TTISFC001400.T$MITM},2) = &quot;06&quot; then mtl_hrs_6 := {TTIHRA100400.T$HREA} + mtl_hrs_6
// else
// if right({TTISFC001400.T$MITM},2) = &quot;09&quot; then mtl_hrs_9 := {TTIHRA100400.T$HREA} + mtl_hrs_9;

//if {TTIHRA100400.T$TANO} = 9200
//then
// if right({TTISFC001400.T$MITM},2) = &quot;01&quot; then stg_hrs_1 := {TTIHRA100400.T$HREA} + stg_hrs_1
// else
// if right({TTISFC001400.T$MITM},2) = &quot;02&quot; then stg_hrs_2 := {TTIHRA100400.T$HREA} + stg_hrs_2
// else
// if right({TTISFC001400.T$MITM},2) = &quot;03&quot; then stg_hrs_3 := {TTIHRA100400.T$HREA} + stg_hrs_3
// else
// if right({TTISFC001400.T$MITM},2) = &quot;04&quot; then stg_hrs_4 := {TTIHRA100400.T$HREA} + stg_hrs_4
// else
// if right({TTISFC001400.T$MITM},2) = &quot;05&quot; then stg_hrs_5 := {TTIHRA100400.T$HREA} + stg_hrs_5
// else
// if right({TTISFC001400.T$MITM},2) = &quot;06&quot; then stg_hrs_6 := {TTIHRA100400.T$HREA} + stg_hrs_6
// else
// if right({TTISFC001400.T$MITM},2) = &quot;09&quot; then stg_hrs_9 := {TTIHRA100400.T$HREA} + stg_hrs_9;

 

[Naith]

Matt,

When you declare your variables, why are you declaring them like:

global numbervar fmi_hrs_1:=

as opposed to

Global NumberVar fmi_hrs_1;

When you finish up with := instead of ;, what you're saying is fmi_hrs_1 is whatever fmi_hrs_2 is, but fmi_hrs_2 is whatever fmi_hrs_3 is, and so on and so forth.

Replace your := declarations to ; in your first 30 lines or so, and tell me what happens.

Naith

 

[Guest_imported]

Naith,

Many apologies for that, as soon as I read what you'd said I realised what I'd done wrong. When I switched the formula from the multi-if using := to the nested if I neglected to change it to ;.

It now works and I feel pretty stupid for not spotting that. Thanks once again and sorry if I have wasted your time.

 

[Naith]

Not at all.