Select the lates date

Posted on 2009-02-19
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

    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....


    LVL 1

    Expert Comment

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

    Author Comment

    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?
    LVL 34

    Accepted Solution

    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

    Thanks!  That worked perfectly.
    LVL 34

    Expert Comment

    You're welcome.  Glad I could help.


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
    This video discusses moving either the default database or any database to a new volume.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now