Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Minimum of Date Post Reply Post New Topic
Author Message
grego0010
Newbie
Newbie


Joined: 17 Nov 2015
Location: United Kingdom
Online Status: Offline
Posts: 2
Quote grego0010 Replybullet Topic: Minimum of Date
    Posted: 26 Nov 2015 at 4:56am
I am trying to return the minimum of a date in crystal reports XI and I get blank values returned.  I have done some investigation and it appears that crystal reports struggles to return the minimum of date fields.  From reading up it appears that a variable is the best way to achieve this.  I have no experiences of working with variables in crystal reports - so I badly need some help!
 
Now for the detail!
Originally I had a field called "Rate Set Date".  this has the following formula:
if {EVENTS.COMMENTS}<>"RATE SET" then Date(1900,01,01) else if 
{EVENTS.STATUS}="A" then Date(1900,01,01) else if {EVENTS.CCY}={transrpt.CCY} then if
{EVENTS.COMMENTS}="RATE SET"and {EVENTS.STATUS}="U" then {EVENTS.ACTION_DT}
So basically the EVENTS table has multiple records and I am only interested in those that have the COMMENTS "RATE SET" and the EVENTS.STATUS set to "U"
This was put in the details section of the report.
I have one group in the report grouped on transrpt.DEAL_NO.  I then wanted to return the minimum of the "Rate Set Date" field in the group footer for transrpt.DEAL_NO.  When the report failed to return the minimum I attempted to create a variable called "Global Variable" in the formula fields section.  This has the following formula:
 
Global DateVar ratesetdate;
 

if {EVENTS.STATUS}="U" and {EVENTS.COMMENTS}="RATE SET" and ratesetdate =minimum({EVENTS.ACTION_DT},{transrpt.DEAL_NO}) then ratesetdate={EVENTS.ACTION_DT}
 
I put this in the details section of the report.
 
I have a variable reset field called "Variabe Reset" that I have added to the Group header that has the following formula:
 
WhilePrintingRecords;
Shared DateTimeVar ratesetdate=Date(1900,01,01);
 
 
I have then got a bit stuck - I am sure I need something in the group footer but I am not sure what to put!
 
Any advice gratefully received!
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 09 Dec 2015 at 3:01am
use a running total
note: this will only work in a footer not a header

New Running Total
Name = whatever
Field to summarize = your date field
Type of evaluation = minimum
Evaluate= use a formula
COMMENTS = "RATE SET" and EVENTS.STATUS = "U"
Reset = on change of group (select your only group)
place in the group footer
IP IP Logged
grego0010
Newbie
Newbie


Joined: 17 Nov 2015
Location: United Kingdom
Online Status: Offline
Posts: 2
Quote grego0010 Replybullet Posted: 14 Jan 2016 at 3:36am
Thank-you so much for taking the time to reply.  This fixed the problem perfectly and I am now getting the results I was expecting.

Thanks Again.
IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.029 seconds.