Averaging particular items to get an overall status

Question

I'm having trouble articulating this so hopefully I make sense!  I have a list which contains project names, their associated manager, and the status.  What I am trying to do is have a listing of each manager and an overall status of their projects.  I want to take all of the projects, average the status field, and output this to a list or somehow display each manager and a single overall status field.  I'm drawing a blank on this, not even sure if it's possible.

Answer 1

Moving to Admin. Not site customization.

---

WSS FAQ sites: http://wssv2faq.mindsharp.com and http://wssv3faq.mindsharp.com
Total list of WSS 3.0 / MOSS 2007 Books (including foreign language) http://wssv3faq.mindsharp.com/Lists/v3%20WSS%20FAQ/V%20Books.aspx

Answer 2

Hello Mikey52,

 

I ran into a similar situation recently.  We tied several combinations of creating an algorithm to support this, but ultimately decided on a bar chart to display the number of projects in each category (i.e. Red, Yellow, Green) and scaled the width of the table cell to be the percentage of the total.  The reason we went with this approach was due to how the value of projects were determined and using a waited average to display an overall status didn’t make sense from an end-user perspective.

 

After the variables were defined, the XSL output was something like this:

<table>
  <tr>
	<xsl:if test="($GreenPercent*100)&gt;'0'"><td width="{($GreenPercent*100)}%" bgcolor="green" align="center"><xsl:value-of select="$Green" /></td></xsl:if>
	<xsl:if test="($YellowPercent*100)&gt;'0'"><td width="{($YellowPercent*100)}%" bgcolor="yellow" align="center"><xsl:value-of select="$Yellow" /></td></xsl:if>
	<xsl:if test="($RedPercent*100)&gt;'0'"><td width="{($RedPercent*100)}%" bgcolor="red" align="center"><xsl:value-of select="$Red" /></td></xsl:if>
  </tr>
</table>

 

All the best,

Mike

Answer 3

Hi Mikey,

As I understood that you have a list like

Project Name           Manager              Status

abc                              A                          50
xyz                              B                          30
mno                            C                          45
pqr                              A                          70
lkj                               C                          20

Manger can work on 'n' no.of projects

What I mean to say is Create a custom webpart, populate Mangers column with distinct values (mangers can be duplicate) into a drop down list, then read the ddl values one by one, then using CAML query (<OrderBy><FieldRef Name='Manager' /><FieldRef Name='Status' /></OrderBy><Where><Eq><FieldRef Name='Manager' /><Value Type='Text'><here goes your ddl value filled with manager names> </Value></Eq></Where>) get one managers data.

Then after pick the Status   column value and sum the status value if the query count is 2 or more, then calculate average Total/No.of row count (store the sum and average in a variable)

Then you can insert the row into a fresh custom list.

Let me know in case if you have any queries.