Store Previous row value for current row in Crysta

问题:

I want to retrieve previous row value in current row just like below (Crystal Report 2008):

+------------+-----------------+----------+------------+----------------+
| Date       | PreviousBalance | Loan     | Collection | CurrentBalance |
+============+=================+==========+============+================|
| 14/02/2012 | 00.00           | 10000.00 | 00.00      | 10,000.00      |
| 15/02/2012 | 10,000.00       | 00.00    | 500.00     | 9,500.00       |
| 16/02/2012 | 9,500.00        | 00.00    | 500.00     | 9,000.00       |
| 18/02/2012 | 9,000.00        | 5,000.00 | 00.00      | 14,000.00      |
+------------+-----------------+----------+------------+----------------+

回答1:

It's easier than it seems:

1. Create two Running Totals one for Loan {#TLoan} for the field {table.Loan} and one for Collection {#TCol} for the field {table.Collection}.

2. Create a Formula: {@Total} with following content:

{#TLoan}-{#TCol}

This will have CurrentBalance value.

3. Create another formula: {@Prev} with following content:

{@Total}-{table.Loan}+{table.Collection}

This will have PreviousBalance value.

Using this approach don't need complicated formulas and the result is the expected.

Hope it helps!

回答2:

The use of the previous() function seems like it would be the obvious choice for this solution, but because formulas can't reference each other cyclically (that is, {@CurrentBalance} can't reference {@PreviousBalance} and vice-versa) nor can they be recursive it's tougher to implement it this way than it first seems.

Instead, you should use a variable to keep track of the balance between transactions. You can accomplish this by creating 3 formulas and placing them in the appropriate sections of your report.

// {@initVars} - Initialize balance variable
//This formula should be placed in your report header
whileprintingrecords;
numbervar balance:=0

// {@previousBalance} - Display previous line's balance
//This formula should be placed in your Details section
whileprintingrecords;
numbervar balance;

// {@currentBalance} - Display current balance after transactions
//This formula should be placed in your Details section
evaluateafter({@previousBalance});
numbervar balance := balance + {table.LoanAmount} - {table.CollectionAmount}

回答3:

I think there are a number of ways. How about:

previous({Table.Field})

Or you could have a running total then do {#total} - {Table.Field}?

回答4:

In the stored procedure store the previous balance inside variable by using function that reads from the table and returns the previous balance and store it inside a variable then inside the Stored Procedure read the current balance. Use union to read previous and Current in separated rows and get them in the report.

回答5:

Try:

// {@PreviousBalance}
If PreviousIsNull({@CurrentBalance}) Then
  0
Else
  Previous({@CurrentBalance})

回答6:

from the stored procedure (if you are using one) order the data by date ASC, or if you are not using SP you hane to sort the data table or data set by date ASC, then you have to determine GROUPING criteria (ClientId) to group the information for one client for example in such ordering and grouping as you mentioned, you can group the records from (Field Explorer -> Group Name Fields -> Insert Group)

回答7:

please look at this link. You haven't specified the underlying datasource so I found a sample for MS SQL.

If you do not have access to the database in order to query / edit stored procedure, you can write a foreach loop and calculate the appropriate values. The problem is that reports usually summarize, slice and filter data. The concept of "previous" and "following" loses it's meaning in that context.