Report Design: Max Function in date

Print Page | Close Window

Max Function in 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=19782
Printed Date: 04 May 2024 at 11:45pm

Topic: Max Function in date

Posted By: anushka
Subject: Max Function in date
Date Posted: 17 Jul 2013 at 2:03pm

Hi ,

i am creating a report where i have to pull the dates for a type of document name. I created a formula in details section and then used max function in group to pull the expiration date of that document name as there are other document names too in that column.

However there are null values for few rows, now i have to do formating where all nul values will read "not available" but when i make my formula

if {table1.DOCUMENTNAME} = "physics" then
totext({table1.EXPIRATION_DATE},"MM-dd-yyyy")
else "MISSING"

but now all the values in that column are showing as missing because the max function at the group level think missing is greater than a date converted to text. Please advice if ther is a workwround to it.

-------------
Learning can be fun.

Replies:

Posted By: Sastry
Date Posted: 18 Jul 2013 at 1:19am

Hi

Use your formula for display purpose and do not insert Max() on your formula.

Insert Max() on date and place that in your gorup footer.

i.e. place your formula in detail to show null values as 'Missing', but while inserting maximum date in group footer level use your date only.

or

create a formula to insert max date in group footer like ..

Maximum({table1.EXPIRATION_DATE},Groupname)

-------------
Thanks,
Sastry

Posted By: anushka
Date Posted: 18 Jul 2013 at 7:23am

Hi , Thanks for the response but doesnt seem to work. So here is what i did after you suggested.

Detial Sections

Created a formula called FLAG EXPIRE DT

if {table1.DOCUMENTNAME} = "physics" then
totext({table1.EXPIRATION_DATE},"MM-dd-yyyy")
else "Missing"

Group header

I used the max function for variable " FLAG EXPIRE DT" as the variable has the condition for which document i need dates

Now when i run report i get only missing values.

If i change the formula to

if {table1.DOCUMENTNAME} = "physics" then
totext({table1.EXPIRATION_DATE},"MM-dd-yyyy")
then i get dates and null values. These nulll values should be flagged but i dont know how ?

-------------
Learning can be fun.

Posted By: DBlank
Date Posted: 18 Jul 2013 at 7:37am

I think Sastry just missed the part where you max value has a condition on it.

there are several ways to do this.

one way would be this...

change your formula to

if {table1.DOCUMENTNAME} = "physics" then {table1.EXPIRATION_DATE} else date(1900,1,1)

now you can do a max value of this for this formula field at your group header

right click on the formula field

select format field

select common tab

select display string

insert the formula

if currentfieldvalue=date(1900,1,1) then 'Missing' else totext(currentfieldvalue,'MM-dd-yyyy')

do the same thing for your summarized field

Print Page | Close Window