I want to calculate average "Estimated Effort" for project associated to each EVP.
For example, Wilber has two projects, one has an estimated effort of 2 and the other one is a 3, so I want to create a table that pulls the information for Wilbert and states looks something like this:
Report
[TABLE="width: 355"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]EVP[/TD]
[TD="align: center"]Num of Projects[/TD]
[TD="align: center"]Average Est Effort[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Wilber[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2.25[/TD]
[/TR]
[TR]
[TD]Hank[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
Here is the source table:
[TABLE="width: 640"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]Project Title[/TD]
[TD="align: center"]EVP[/TD]
[TD="align: center"]Est. Project Size (T-shirt Sizing)[/TD]
[TD="align: center"]Application Dependencies[/TD]
[TD="align: center"]Estimated Effort[/TD]
[/TR]
[TR]
[TD]Remove Sybase (Architecture)[/TD]
[TD]Mike[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Pre-Requisites to Conversion to Oracle[/TD]
[TD]Wilber[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Quote Replacement[/TD]
[TD]Mike[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]OEM Lock Down[/TD]
[TD]Wilber[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Locker Pickup P2[/TD]
[TD]Hank[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]POS Restriction to Purchase when BSS has a STD xref.[/TD]
[TD]Hank[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Modify POS New Store Database build to support version 7.3[/TD]
[TD]Hank[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
For example, Wilber has two projects, one has an estimated effort of 2 and the other one is a 3, so I want to create a table that pulls the information for Wilbert and states looks something like this:
Report
[TABLE="width: 355"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]EVP[/TD]
[TD="align: center"]Num of Projects[/TD]
[TD="align: center"]Average Est Effort[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Wilber[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2.25[/TD]
[/TR]
[TR]
[TD]Hank[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
Here is the source table:
[TABLE="width: 640"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]Project Title[/TD]
[TD="align: center"]EVP[/TD]
[TD="align: center"]Est. Project Size (T-shirt Sizing)[/TD]
[TD="align: center"]Application Dependencies[/TD]
[TD="align: center"]Estimated Effort[/TD]
[/TR]
[TR]
[TD]Remove Sybase (Architecture)[/TD]
[TD]Mike[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Pre-Requisites to Conversion to Oracle[/TD]
[TD]Wilber[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Quote Replacement[/TD]
[TD]Mike[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]OEM Lock Down[/TD]
[TD]Wilber[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Locker Pickup P2[/TD]
[TD]Hank[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]POS Restriction to Purchase when BSS has a STD xref.[/TD]
[TD]Hank[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Modify POS New Store Database build to support version 7.3[/TD]
[TD]Hank[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]