Group by Another Group Summary

[kristal9]

Hi and thanks in advance for wisdom.
Using CR XI and SQL Database
I have a report that snapshots some projects & their resources.

Group 1 {project.currentphase)
Group 2 {project.class}
Group 3 (project.name}

Group 1 and 2 are suppresed an in group 3 I have a summary of the group phases for Requirements, Design & Development.

Each of these phases are actually detail data from another table called task. Project table to task table is a 1 to many relationship.

So, I have formulas for the tasks to define the task resource.

//{@RequirementsResource}
if [task.type}="Requirements" then {Task.Resource}

//{@DesignResource}
if [task.type}="Design" then {Task.Resource}

//{@DevelopmentResource}
if [task.type}startswith "Development" then {Task.Resource}

there are only 1 task per project that can be a Design or Resource but there may be up to 3 tasks for Development.

So, for {@RequirementsResource} and {@DesignResource} i have inserted maximum summary by group and placed that into the Group 3 header.

for {DevelopmentResource} these remain in the details. Group 3 header is formatted to underlay so that the appearance for all resources appears on one line.

So for example the data is actually like this:

Current phase class Projects Task Resource
Requirements A My Project 1 Requirements
Requirements A My Project 1 Design fred8
Requirements A My Project 1 Development X kristal9
Requirements A My Project 1 Development Y team b
Requirements A My Project 1 Development Z team c
Development C My Project 2 Requirements dave7
Development C My Project 2 Design alan4
Development C My Project 2 Development X team a
Development C My Project 2 Development Y teamb
Development C My Project 2 Development Z fred8
Requirements B My Project 3 Requirements
Requirements B My Project 3 Design
Requirements B My Project 3 Development X


the way it displays is:
Current phase class Projects Requirements Design Development
Requirements A My Project 1 fred8 kristal9
team b
team c
Development C My Project 2 dave7 alan4 team a
team b
fred8
Requirements B My Project 3 dwayne1 fred8



Everything works fine except that (ironically) 3/4 way through developing the report, they changed the requirements.

If you notice the resources may be blank (a null value) or a person or a team.

So, what they now want is to have another group between the Group 1 current phase and group 2 project type that will group by whether any of the resources per project are null, or whether any of the resources are a team or whether all of the resources are assigned to a person.

So that if you were to look at the group tree it would display:

Group 1: Current Phase
Group 2: Missing Resources
Group 3: Project Type
Group 4: Project Name
Group 1: Current Phase
Group 2: Team Resourced
Group 3: project Type
Group 4: Project Name
Group 1: Current Phase
Group 2: Completely Resources (e.g. persons assigned)
Group 3: project Type
Group 4: Project Name

Determining by task the resource assignment can be done by a formula
//{@TaskAssigned}

if isnull({Task.Resource}) then "Missing Resource" else
if {Task.Resource} startswith "Team" then "Team Resourced" else "Assigned Resource"

But I am thrown by trying to make this a group prior to the group of the project itself.

In my sample data above, if i were to group by {@taskAssigned} and place that as group 2 where the project group becomes group 4, then "My Project 1" would actually show up 3 times on the report because it has three instances where the resource is one of the 3 scenarios. (missing, team assigned and assigned)

It should show up only once and under "Missing Resources" because is one of the task.resources is a null value that should be evaulated first.

I should also mention that apparently it is also possible for a project to not have any or all of the tasks. So a project may have only Requirements & Design but not have a task detail for Development. In this case, that project should also appear under the group 2 for data of "Missing Resources"

Please let me know if anyone can possibly help.

 

[Madawc]

You can group using a formual field, provided it uses values from just one detail line. Does that help?

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[kristal9]

thanks Madawc,

No. That won't do it.

I think I am just going to re-write my formula for the resources by task to

//{@Development Resource}

if task.type startswith "development" and isnull(task.resource} then "no resource" else
if task.type startswith "development" the task.resource

and condiditonally format text that = "no resource" to red.

it won't give them the primary group by whether or not any resource for any given task is null or not but it will draw attention to unassigned tasks. Hopefully that will satisfy the requirement.

thanks again much for your reply.