Report Design: Minimum of Date

Minimum of Date

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21789
Printed Date: 03 May 2024 at 5:09pm

Topic: Minimum of Date

Posted By: grego0010
Subject: Minimum of Date
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!

Replies:

Posted By: DBlank
Date 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

Posted By: grego0010
Date 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.