Manually Creating and Resetting Running Total in CR 8.5

Hi could anyone pls help me in manually creating and resetting running totals in Crystal Reports 8.5

Problem :

I have a table in foxpro which stores purchase or sales,product name,qty,rate. I need to calculate the cost price of balance qty in hand. say for example :

PU/SL--Name--Qty--Rate

Purchase--prodA--10---100
Sale---prodA--5---150
Sale---prodA-- 5---130
Purchase--prodA--25---90

and so on
desired output

PU/SL--Name--Qty--Rate--Amount--Bal Qty--Bal Rate

Purchase--prodA--10---100---1000---10----100
Sale---prodA--5---150---750----5----50
Sale---prodA--5---130---650----0----0
Purchase--prodA--25---90---2250---25----90

It is something similar to avg priceing that people generally use it for stock after making several transactions for same script to know what price does each stock cost.

Hence after the qty becomes 0 and new purchase made the purchase price should be the cost price and the profit made shouldnt reduce the cost price.

I have tried explaining the best possible way yet if you have any other query pls feel free to contact me on isicles@.hotmail.com

PLS HELPYou have to use Formulas in calculating running totals. See you help documentation regarding running totals you will get help.

Also see functions like
BeforeReadingRecords, WhileReadingRecords, WhilePrintingRecords & Evaluate(X). You will need these extensively for calculating running totals.

Revert if you need more help

Regards
Amarjit|||Thank You Sir,

Running totals are created using formula. But i wanna know how can i reset these totals on 2 conditions.

1) The running total value goes to 0 and
2) On change of Group

Thanks|||Why dont you use If then Else in the formula before running totals.

----See this tutotial From Crystal Reports Help Documentation --------

Example report using evaluation time function

This example report illustrates the use of an evaluation time function to take a formula that would normally be evaluated while reading records and force it instead to be evaluated while printing records.

Scenario

You want to create an order detail report that:

shows the extended price for each line item,

calculates a running total for each line, and

resets itself to 0 for each new order.

Unless you disabled the Samples and Examples option during installation, the report, called EVALTIME.RPT, is one of the sample reports that was installed on your system. An example showing partial data from that report follows:

Order# Item# Qty Price Extension RunTotal
Order #2203

2203 1002 3 276.00 828.00 828.00
2203 1102 1 1,230.00 1,230.00 2,058.00
Total for order 2203 2,058.00
Order # 2204

2204 1001 7 192.00 1,344.00 1,344.00
2204 1002 3 276.00 828.00 2,172.00
2204 1003 1 484.00 484.00 2,656.00
2204 1102 3 1,230.00 3,690.00 6,346.00
Total for order 2204 6,346.00

The report uses four database fields:

Order# {detail.ORDERNUM}
Item# {detail.ITEMNUM}
Qty {detail.QTY}
Price {detail.PRICE}
It uses three formulas:

@.extend {detail.QTY} * {detail.PRICE}

@.extend simply calculates the extended price for a line item (quantity ordered times price per item). This provides the values in the Extension column.

@.initialize NumberVar Runtotal;
If {detail.ORDERNUM} <> Previous ({detail.ORDERNUM}) Then
Runtotal := 0
Else
Runtotal := Runtotal

@.initialize resets the variable Runtotal each time the order number changes so the running total for each order begins at 0.

@.runningWhilePrintingRecords;
NumberVar Runtotal;
Runtotal:= Runtotal + {@.extend}

@.running calculates the running total for each order by adding the extended price for each line item to the existing running total for the order. This provides the values in the Running Total column.

And it includes one subtotal:

Sum of @.extend End group #1: detail.ORDER
Sum of @.extend

It subtotals the extended price each time the order number changes, thus providing the Total for order nnnn order total.

Evaluation Times

Formulas are normally evaluated at the following times:

If no database or group field is included in the formula, the formula is evaluated before the program reads database records.
If a database is included in the formula, the formula is evaluated while the program reads database records.
If a group field, page # field, subtotal, etc. is included in the formula, the formula is evaluated after database records are read and while the data from the records is being printed in the report.

The evaluation time problem which is solved with one of the evaluation time functions involves the relative evaluation time of two formulas, @.initialize and @.running.

@.initialize includes the Previous function which is evaluated while records are printed. Any formula that includes the Previous function, therefore, will be evaluated while the records are printing as well.
@.running, on the other hand, is normally evaluated while records are read. @.running includes the formula @.extend in its calculations, and @.extend involves a database field. Whenever a database field is involved in a formula, the formula is evaluated while reading records (unless there is something in the formula a subtotal, a page # field, etc. that causes the formula to be evaluated later).

If we leave both formulas to be evaluated at their normal times,

@.running is evaluated first (during record read time) and it outputs running totals for each line item.
Then, after it is finished calculating the running totals, @.initialize is evaluated (during record print time). This formula initializes (sets to 0) the Runtotal variable each time the order number changes.

By this time it's too late. The running totals have already been calculated without being initialized between orders. The final printed report shows running totals getting bigger with each line item; they are not reset from order to order.

Your report comes out looking like this:

NOTE: Order detail report @.running doesn't include an evaluation time function.

Order# Item# Qty Price Extension RunTotal
Order #2203

2203 1002 3 276.00 828.00 828.00
2203 1102 1 1,230.00 1,230.00 2,058.00
Total for order 2203 2,058.00

Order # 2204

2204 1001 7 192.00 1,344.00 3,402.00
2204 1002 3 276.00 828.00 4,230.00
2204 1003 1 484.00 484.00 4,714.00
2204 1102 3 1,230.00 3,690.00 8,404.00
Total for order 2204 6,346.00

To solve this problem, you must make certain that @.initialize is evaluated at the same time as @.running. You can't force a print time evaluation formula (@.initialize) to evaluate at read time (forcing it to be evaluated before the required data is available), but you can force a read time evaluation formula (@.running) to evaluate later, at print time. You do this by starting the formula (as we did) with the function WhilePrintingRecords;

WhilePrintingRecords;
NumberVar Runtotal;
Runtotal:= Runtotal + {@.extend}

When you do this, your report comes out looking like you want it, like this:

NOTE: Order detail report@.running includes evaluation time function.

Order# Item# Qty Price Extension RunTotal
Order #2203

2203 1002 3 276.00 828.00 828.00
2203 1102 1 1,230.00 1,230.00 2,058.00
Total for order 2203 2,058.00

Order # 2204

2204 1001 7 192.00 1,344.00 1,344.00
2204 1002 3 276.00 828.00 2,172.00
2204 1003 1 484.00 484.00 2,656.00
2204 1102 3 1,230.00 3,690.00 6,346.00

--------------------------

Hope it helps

Regards
Amarjit|||THANK YOU SIR

Your effort is highly appreciated. The code helped me learn get new ideas and ultimately solved the issue.

Thank You so much once again.

Hope If need further help you would help the young boy.|||Thank you very much that i could help you.

Please rate this post.

Regards
Amarjit