Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Select the lates date

Posted on 2009-02-19
Medium Priority
Last Modified: 2012-05-06
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.
Question by:Angelmar
LVL 17

Expert Comment

ID: 23685929
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....



Expert Comment

ID: 23686928
Create a formula on the milestone date field.   maximum(dbo.milestone_date)

Author Comment

ID: 23696705
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?
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 35

Accepted Solution

James0628 earned 2000 total points
ID: 23697831
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.


Author Comment

ID: 23724247
Thanks!  That worked perfectly.
LVL 35

Expert Comment

ID: 23728264
You're welcome.  Glad I could help.


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question