Reset a variable

[Rena]

I apologize if this has been asked and answered before but I couldn't find this question in previous threads.

I have an inventory report that displays:

avg cost,
amt allocated,
total allocated cost,
on hand amt,
total on hand cost,
difference (between allocated cost and on hand cost)

The report is grouped on

jobno (group1)
matno(group2)

I have created several formulas to the calculations. The total allocated cost is as follows:

currencyVar Sum_TotCst ;

If {uomcost} = 'M' Then
Sum_TotCst := Sum_TotCst + ({committed} * {avgcost}) / 1000
Else
If {uomcost} = 'CWT' Then
Sum_TotCst := Sum_TotCst + ({committed} * {avgcost}) / 100

It all works well except than I need to clear or reset the Sum_TotCst variable when the matno group changes. I have tried to accomplish this by creating the following formula and putting it in the group header for matno:

currencyVar Sum_TotCst := 0.00 ;

But is doesn't reset this variable. Any suggestions on what I am doing wrong.

TIA
Rena

 

[Ngolem]

a couple of things

first of all add "WhilePrintingRecords;" to all of your formulas....including your reset formula

this controls when the formula is evaluated

Second .... placeing a reset formual in a header has one problem if the group straddles 2 pages. In this case the reset will be fired before the final total was calculated.

to prevent this make your formula as follows:

@Reset

WhilePrintingRecords;

if not inRepeatedGroupHeader then
currencyVar Sum_TotCst := 0.00 ;



so 2 things ....add WhilePrintingRecords to your formulas and protect against accidental resets...hope this helps.
Jim Broadbent

 

[IdoMillet]

Any chance the formula you have in the group header is:

currencyVar Sum_TotCst = 0.00 ;

instead of

currencyVar Sum_TotCst := 0.00 ;

If that is not the issue, how exactly do you know the reset is not working? Are you displaying (for testing purposes) the reset formula?

hth,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[Rena]

Hi Jim,

When I put in the WhilePrintingRecords, my Sum_TotCst formula stopped working correctly.

I inserted the If statement you suggested. I am still working with the first two matno's so I haven't gotten to page breaks to know if that was a problem or not . Thanks for the tip.

Hi Ido,

I copied the formula from Crystal directly into the post so, yes it is definately :=. But that was a good question. I have analyzed all my formulas to see if I have mistyped something but I haven't found any typos.

The reason I know that isn't working is because the total for the second matno should be 0.00 but it is the same value as the first matno (which in this case was $573.29) The Sum_TotCst is on the group footer for matno but I have put it in the detail section to watch it work. It never resets to 0.00.

Any other suggestions would be greatly appreciated.

Rena

 

[Ngolem]

This does not seem like the complete formula to me
*****************************************************

currencyVar Sum_TotCst ;

If {uomcost} = 'M' Then
Sum_TotCst := Sum_TotCst + ({committed} * {avgcost}) / 1000
Else
If {uomcost} = 'CWT' Then
Sum_TotCst := Sum_TotCst + ({committed} * {avgcost}) / 100

*****************************************************

it looks to me that you have typed it rather than cut and pasted it because formulas are of the form {@formula} and fields are {table.field} and Parameters are {?Parameter}

Post the exact formula...complete so we can see what is going on.

the only reason why "WhilePrintingRecords" might cause a problem is if these are not real sums but rather are summary operations.

I also note that there is no provision for a "division by zero" in your formula snippet...
Jim Broadbent

 

[Rena]

Hi Jim,

Thanks for getting back to me. I did cut and paste the formula however, I modified the fields to remove the table name hoping it would make it easier to read. Also, I didn't type in the formula name since it didn't seem relevant. I called the formula @Sum_TotCst. Here is the actual formula cut and pasted from Crystal:


currencyVar Sum_TotCst ;

If {ssinvent.uomcost} = 'M' Then
Sum_TotCst := Sum_TotCst + ({bbinvmov.committed} * {ssinvent.avgcost}) / 1000
Else
If {ssinvent.uomcost} = 'CWT' Then
Sum_TotCst := Sum_TotCst + ({bbinvmov.committed} * {ssinvent.avgcost}) / 100


I chose to not address the issue of the uomcost being something other that 'M' or 'CWT' because elsewhere in the report I flag the record if it is different. But your point of not addressing the division by zero is well taken and I will make a modification for that.

I have the @Clr_Sum_TotCst formula (the one that resets the Sum_TotCst variable) in the group header for matno. This header is normally suppressed but I un-suppressed to see what is going on. The Sum_TotCst IS 0.00 in the header but it is not reset for the @Sum_TotCst formula.

I'm really confused. This shouldn't be that hard!

 

[dgillz]

Do you need to show the running total the whole way? Or just the subtotals by jobno and matno? Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[Rena]

I just want to show by jobno and matno. The final report will have both GHs and the detail suppressed.

 

[dgillz]

Then dump your variables and create a simple formula:

if {uomcost}='M' then ({committed}*{avgcost})/1000 else
if {uomcost}='CWT' then ({committed}*{avgcost})/100

Then subtotal this formula at both levels, and grand total it.

Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[Rena]

OK. Here is a dumb question - how do I sub-total a formula? Here is the formula I currently use in the detail (which will be suppressed) known as @Total_Cost:


WhilePrintingRecords ;

If {ssinvent.uomcost} = 'M' Then
({bbinvmov.committed} * {ssinvent.avgcost}) / 1000
Else
If {ssinvent.uomcost} = 'CWT' Then
({bbinvmov.committed} * {ssinvent.avgcost}) / 100


I can not perform a 'Subtotal' on this formula (as in right click formula, select Insert, select Subtotal). This is why I went to using variables and manually subtotalling. What am I missing?

 

[dgillz]

Step 1 - Get rid of the whileprintingrecords; This will keep the field from being able to be summarized.

Step 2 - From the design window, right click on the formula field, select insert summary, and pick the field you want it to summarize by.

The WhilePrintingRecords and Variable approach that you were using definately has its place, but it can make simple reports a lot harder. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[Rena]

Thank you! Thank you! Thank you! That works.

Now I can go on to something else .