How to read Sharepoint 2007 Survey list and get some statistics

Sharepoint Services is awesome new framework from Microsoft. And these days where I work, I mostly do Sharepoint 2007 and MOSS development.

I got this request where they wanted a Survey. Sharepoint does have survey, but it lacks statistics on the survey. In their Survey, the Choices for all the questions are identical. For example voting on “Employee of the Month”. MOSSAfter responding to this survey, they wanted to show the top employee in each Choice. The style of the answers are Choice type, where the user choose one of the option under the name of the employee. For example:
– Emplyee name 1
Choice 1: Co-Operation
Choice 2: Great Example

– Emplyee name 2
Choice 1: Co-Operation
Choice 2: Great Example
.
.
.

So the best solution is to make your Web Part read from that Survey’s items.

And to do that, we just need to loop through the Items of the Survey List, look for the “Choice” type of field in it and read its content (The choosen Choice. Choice 1 or Choice 2 … etc). The title of the field is the question text(Employee name).

So we need two loops one to look for the items, and one to look for items’ fields with the type “Choice” – then its title and content.

This is the code to do the loops and readings:

using (SPSite mySite = SPContext.Current.Web.Site)
{
using (SPWeb myWeb = mySite.OpenWeb())
{
SPList SurveyList = myWeb.Lists["Employee of the month"];
foreach (SPListItem item in SurveyList.Items)
{
foreach (SPField field in item.Fields)
{
if (field.TypeAsString == "Choice")
{
if (item[field.Title].ToString() == "Co-operation")
{
coop += field.Title.ToString() + ",";
}
else if (item[field.Title].ToString() == "Great Example")
{
example += field.Title.ToString() + ",";
}
}
}
}
}
}

Here I used normal string variables to store the names of the employees for each Choice, maybe you would like to use some sort of array instead, but I didn’t since I dont have many questions.

So when the code run, I will have something like the following in my variables:
Co-Operation variable: “Hasan B, Ahmad D, Farah H, Aya K, Hasan B, Amal K, Ahmad D”
Great Example variable: “Tahseen K, Sebhi J, Jafar A, Tahseen K”

Now by counting how many times each name is repeated for each choice, I can get an idea of who got the most votes for each choice.

I used the following C# function to count how many times each phrase or word accured in a string variable:

private string CountWords(string input)
{
char[] delims = new char[] { '.', ',', ';' };
string[] wordlist = input.Split(delims, StringSplitOptions.RemoveEmptyEntries);
SortedList words = new SortedList();
foreach (string item in wordlist)
{
if (!words.ContainsKey(item))
words.Add(item, 1);
else
words[item]++;
}
StringBuilder sb = new StringBuilder();
foreach (KeyValuePair kvp in words)
sb.Append(kvp.Key + ": " + kvp.Value + "
");
return sb.ToString();
}

So now from my Web Part I can write:

writer.Write("Co-Operation" + "
" + CountWords(coop) + "
" +
"Great Example" + "
" + CountWords(example))

And the function CountWords will return me the number of times a name accured in that passed string variable which represents the Choice.

For my example the output would look like:

Co-Operation
Hasan B: 2
Ahmad D: 2
Aya K: 1
Amal K: 1
Farah H:1
Great Example
Tahseen K: 2
Jafar A: 1
Sebhi J: 1

This code needs some additions to make it more useful, but I searched on the net, and I didn’t find answer for this, so I thought I would share with you what I got so far. I’m probably going to work more on this.

Hope it helped.
kick it on DotNetKicks.com

Share
  • Pingback: Hasan’s Web Site » Read Sharepoint 2007 Scale Survey with some statistics()

  • i love the sample names 😉 Tahseen! lolllll

  • Benny Hung

    Tried to follow the sample, but I have to do it in a workflow instead of a web part. When I try to display the value of a survey field (item[field.Title].ToString()), it gives me the actual title instead of the value. Any idea why that is?

  • Hello Benny,

    I’m not sure why is this happening to you, but could you try using the field index instead of the string?

    I’m not using the SharePoint right now…but I think you just should take a look at the item and what properties it has…make sure you are calling the right one.

  • diego

    Hello, what if the questions are dynamic?

    • Anonymous

      Hello diego, I don’t have the exact answer now, but on the top of my head I have an idea it isn’t optimal but can get you going for now: Maybe you could make all the questions start with the string “Q:” that way you can then parse that.