Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Date Filtering Post Reply Post New Topic
Author Message
GrisCorp
Groupie
Groupie
Avatar

Joined: 08 Mar 2013
Online Status: Offline
Posts: 64
Quote GrisCorp Replybullet 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.

How can I accomplish this?



Edited by GrisCorp - 20 Jun 2014 at 3:38am
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet 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:

{@ShowFirstAmount}
WhilePrintingRecords;
NumberVar firstAmount

{@ShowLastAmount}
WhilePrintingRecords;
NumberVar lastAmount

Note that there is no final semi-colon, so these will display the variable values. Place these in the section where you need to display the data.

-Dell
IP IP Logged
Printable version Printable version

Forum Jump
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