Joined: 08 Mar 2013
Online Status: Offline
Posts: 64
Topic: Date Filtering Posted: 20 Jun 2014 at 3:14am
I'm designing a report in Crystal Reports XI R2 that pulls data from a transaction history table for the part and date range specified.
My fields are: TxnHist.Part TxnHist.Date TxnHist.Type TxnHist.Amount
I need to write two formulas: One to select the amount of only the first transaction of type "C" in the table that matches the beginning date of the date range and One to select the amount of only the last transaction of type "C" that matches the last date of the date range.
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Posted: 24 Jun 2014 at 9:55am
This is going to be not that straight-forward. Assuming there is just one part on the report and your data is grouped by part, create something like the following formulas:
{@FirstAmount}
WhileReadingRecords;
Numbervar firstAmt;
BooleanVar firstIsSet;
if OnFirstRecord then firstAmt = 0;
if OnFirstRecord then firstIsSet = false;
if not firstIsSet and {TxnHist.Type} = 'C' then
{
firstAmt := {TxnHist.Amount};
firstIsSet := true;
};
{@LastAmount}
WhileReadingRecords;
EvaluateAfter({@FirstAmount});
NumberVar lastAmount;
BooleanVar firstIsSet;
if firstIsSet and {txnHist.Type = 'C'} then lastAmount := {TxnHist.Amount};
NOTE: {@FirstAmount} will set the value of the "firstAmount" variable only for the first record with a type of "C". {@LastAmount} will set the value of the "lastAmount" variable for every record of type "C" after firstAmount is set - this is the best way to get the last value.
Put both of these formulas in the details section where you are showing the data from TxnHist. Neither one of them will show any data because of the final semi-colon (";").
Then create two formulas to display the data where you need it. They'll look like this:
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum