Select the lates date

I have a project report and only want to show  the last milestone and date. There is no order or set sequence for the milestones. I have a list of 7 milestones and need to select the last milestone recorded. What is the best way to get this to show up per project.
Milestone 1, Milestone 2, etc. Thanks.
AngelmarAsked:
Who is Participating?
 
James0628Commented:
You have some kind of project ID field and 7 other fields with different Milestone dates?

 When you say that you want to see the last Milestone, what, exactly do you want to see?  Are you just interested in the last date, or is there other information for the last milestone that you also want to see?

 Assuming that you have a project ID field and 7 Milestone date fields, to get the last Milestone date for each project, you could group the report by the project ID field and then create a formula like the following and put it in the group footer.

 Maximum ([
 Maximum ({Milestone1 field}, {project ID field}),
 Maximum ({Milestone2 field}, {project ID field}),
 Maximum ({Milestone3 field}, {project ID field}),
 Maximum ({Milestone4 field}, {project ID field}),
 Maximum ({Milestone5 field}, {project ID field}),
 Maximum ({Milestone6 field}, {project ID field}),
 Maximum ({Milestone7 field}, {project ID field})
 ])

 That creates an array (between the []) containing the maximum for the project for each milestone field, then uses Maximum on that array to get the maximum of those values.  That should work.  I've tested the basic construct (using Maximum on an array containing another Maximum) and it seemed to work fine.

 Replace {MilestoneX field} and {project ID field} with your field names, of course.

 James
0
 
MIKESoftware Solutions ConsultantCommented:
CREATE a new report, then place the MILESTONE and DATE columns in the DETAILS section.

then create a GROUP on MILESTONE.

then right-click the DATE field and create a summaryin GROUP1 footer, ...and use MAX as the calc.

then copy the MILESTONE field from the DETAILS SECTION...and paste it into the GROUP1 footer.

then suppress the DETAILS SECTION and the GROUP1 HEADER...section

I think that will at least get you started....

LEMENO..

M
0
 
BSAS_ITGuyCommented:
Create a formula on the milestone date field.   maximum(dbo.milestone_date)
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
AngelmarAuthor Commented:
Sorry, I am looking for the last milestone for each project not all projects. Each project has seven milestone date fields. The milestones are separate date fields so I can't group by milestone. Any other way to find max date?
0
 
AngelmarAuthor Commented:
Thanks!  That worked perfectly.
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.