Group Selection problem

[JimVGE]

Enviroment: CR 10/11; DB=ADO.net, XSD file;
Have 2 tables:
a list of projects;
a list of tasks;
link between tables is project ID.
Fields in project :
Workspace ID : Unique id, links to task
Name : Name of project
Fields in task :
Workspace ID : Link to Project Table
Name : Name of task
Number of task : unique for task
Status : 0=not started, 1=in progress, 2=complete, 4=unknown
Actual Finish Date : date task was completed, or null if not complete
Milestone : Boolean, is the task a milestone task

Group by Project ID

Need to create a report, that
- only includes milestone tasks (got this, using record selection)
- shows the max "Actual Finish Date" for each project; this will only be for tasks with a status of 2
- shows "No Milestones Complete" if "Actual Finish Date" is null for all tasks in project; These will have a status other then 2.

Sample Data:
Project 1
Name Number ActualFinish Status Milestone
Task 1 5 1/10/05 2 True
Task 2 10 1/0/00 2 True
Task 3 15 1/0/00 2 True
Task 4 20 1/0/00 2 True
Task 5 25 3/20/05 2 True
Task 6 30 1/0/00 0 True
Task 7 31 1/0/00 0 True

Project 2
Name Number ActualFinish Status Milestone
Task 1 7 11/17/04 4 True
Task 2 8 11/17/04 2 True
Task 3 9 1/21/05 4 True
Task 4 10 1/13/05 2 True
Task 5 16 1/27/05 2 True
Task 6 21 1/0/00 4 True
Task 7 25 1/0/00 0 True
Task 8 30 1/0/00 0 True
Task 9 31 1/0/00 0 True

Project 3
Name Number ActualFinish Status Milestone
Task 1 7 1/0/00 4 True
Task 2 14 1/0/00 4 True
Task 3 19 1/0/00 4 True
Task 4 24 1/0/00 4 True
Task 5 29 1/0/00 4 True
Task 6 30 1/0/00 4 True

Desired Report output:
Project 1
Name Number ActualFinish Status Milestone
Task 1 25 3/20/05 2 True
Project 2
Name Number ActualFinish Status Milestone
No Milestones completed
Project 3
Name Number ActualFinish Status Milestone
Task 4 16 1/27/05 2 True

I am almost there (I think) after reading a number of threads here.
I am using a group selection formula,

({Task.ActualFinish} = Maximum ({Task.ActualFinish}, {Workspace.WorkspaceID}))
or ({Task.Number} = Maximum ({Task.Number}, {Workspace.WorkspaceID}));

But I am getting more records than I expect:
for projects with a task that has a max"Actual finished date", I get BOTH the max date finished task, and the max task number
for projects with no Max"Actual finished date", I get all the tasks.
Output Sample:
Project 1
Name Number ActualFinish Status Milestone
Task 1 25 3/20/05 2 True
Task 7 31 1/0/00 0 True
Project 2
Name Number ActualFinish Status Milestone
Task 1 7 1/0/00 4 True
Task 2 14 1/0/00 4 True
Task 3 19 1/0/00 4 True
Task 4 24 1/0/00 4 True
Task 5 29 1/0/00 4 True
Task 6 30 1/0/00 4 True
Project 3
Name Number ActualFinish Status Milestone
Task 2 16 1/27/05 2 True
Task 6 31 1/0/00 0 True

I've tried a number of things:

({Task.ActualFinish} = Maximum ({Task.ActualFinish}, {Workspace.WorkspaceID})

which does not show any projects where Task.ActualFinish is null
Also:

if ({Task.Status} = 2) then 
        IncludeTask := ({Task.ActualFinish} = Maximum ({Task.ActualFinish}, {Workspace.WorkspaceID}))
else 
     IncludeTask := ({Task.Number} = Maximum ({Task.Number}, {Workspace.WorkspaceID}));
IncludeTask;

which still includes the max date task, and the max task number task
A couple of dozen more, none of which worked.
Am I way off track?
Any suggestions on how to solve are greatly appreciated!!
Thanks
Jim

 

[lbass]

The following should return projects where the finish is date(0,1,0):

{Task.ActualFinish} = Maximum ({Task.ActualFinish}, {Workspace.WorkspaceID})

But you say it doesn't, so I wonder whether your sample display data is really what your data looks like. Specifically, you are showing your date field NOT as null, but as 1/0/00. Is this the result of a formula like the following {@date}?

if isnull({Task.ActualFinish}) then date(0,1,0)

If so, then try using:

{@date} = maximum({@date},{Workspace.WorkspaceID})

This would return all records for unfinished projects, since they would all have the same date. You could then use the section expert to suppress the detail section using:

{@date} = date(0,1,0)

This would leave projects with no finish date with no detail records, so then create another formula:

if {@date} = date(0,1,0) then "No Milestones Completed"

Place this in the group footer or a group header_b which formatted to "suppress blank section".

-LB

 

[JimVGE]

LB,
Thanks for the reply.
My sample data is not what the data really looks like, sorry.
I did not notice that my sample had a date of 1/0/00; when I run it in Crystal, I gate a date of 12/30/1899, which I understand is the default date displayed when a date is null. I think that the 1/0/00 date showed up when I was formatting my post here (I put the data into excel to try to get it to align in nice columns).

However, I was able to use your suggestions above to achieve the results I needed!
I created the following formula, and placed it in the group footer, and then suppressed section when blank (which I would not have thought of)

if DateDiff ("yyyy",{Task.ActualFinish},date("12/30/1899") ) =0 then 
"No Milestones Completed"

I placed a similiar formula in the section expert, to suppress the details section when appropriate:

DateDiff ("yyyy",{Task.ActualFinish},date("12/30/1899") ) = 0

Thanks for the great suggestions (on this, and many other posts)!
Jim