Jack of all that is Microsoft, Master of None

July 28, 2006

SharePoint Surveys – Finding the Results in the Database

Filed under: SharePoint, SharePoint Portal Server 2003, SPS 2003 — cregan @ 4:33 am

A while ago, I worked on a project with one of my developers that required building a web part to display the results from a SharePoint survey in a different manner.  The survey itself was a self-assessment… it was composed of 13 questions with approximately five options under each question, and the answers were the ‘Rating Scale’ type.  Below is an example of the survey:

While SharePoint does provide basic reporting out of the box, it never turns out to be what the client needs.  Because of this, a custom web part was built that allowed an administrator to look at the survey results in a variety of ways – by User – with a drill down to the individual Questions, or Summarized – with a drill down to individual questions & users.  An example of the summarized results view by question appears below:

So now I’ve shown off the cool stuff.  Before I go any further, I would like to thank Josh Carlisle for his assistance on this – without it, this post wouldn’t exist.  Now – the reason I’m really writing this post is because I’ve seen questions asked in the past, and I figured I could lend a bit of a hand with Josh’s guidance… the questions were usually…

“How do I find the survey results in the SharePoint database?”

“Where in SQL are the SharePoint survey answers stored?”

And my first reaction is one of caution… from the mouth of Microsoft…

Warning  Modifying the database schema or database structures is not supported. Changes that you make to the database contents may be overwritten when you install updates or service packs for Windows SharePoint Services, or when you upgrade an installation to the next product version.

Okay you say – you understand… and you want to go forward with finding the results data… well, let’s do it!  I’m running SPS 2003 on SQL 2005, so my screen shots are from the SQL 2005 Management Studio – but you can do everything in the SQL Clients Tools in 2000 as well.

  1. First, you need to crack open the Content Database of the portal which contains the survey and open up the Lists table.

  2. I would recommend you expand the width of the tp_ID & tp_Title columns to make your life a bit easier.
  3. Now,  scroll through the tp_Title columns until you find the name of the survey you are looking for.  In my example, I’m looking for the ‘Horizons Self Assessment’.

  4. Once you’ve found the survey name in the tp_Title column, look at the tp_IDcopy & paste or jot down the ID of the survey.
  5. Now, open up the UserData table and expand the width of the tp_ListID column.

  6. You’ve got some digging to do now.  You now need to scroll through this table and find the ID that you wrote down before in the tp_ListID columnNote:  If the survey has been completed multiple times, then the ID will show up multiple times in the tp_ListId column (Each survey submission represents one record in the table).  In my example, I’m looking for the ID of ‘595665fc-6c09-424a-964d-d137b3db5a30’.
    1. I don’t have SQL 2000 Client Tools in front of me (although I know this can be done), but in the SQL 2005 Management Studio, I don’t have to dig to find the ID.  I can just right click on the column, choose Pane -> Criteria.  Then, in the second row up top under Column, select the tp_ListID.  In the Filter column for that row, copy the ID in.  Then execute this query – and you’ve got all of your survey results right in front of you.  The following image shows an example of the query & results:

    2. Or, feel free to use the following query:
      SELECT *
      FROM UserData
      WHERE tp_ListId = ‘unique identifier here’
  7. So you’ve found the rows that contain your survey results – should be no problem pulling out the results now, right?  Well, not exactly.  Depending on the types of questions you are asking in your survey, that dictates how ‘cleanly’ SharePoint stores the results.  So the easiest way to start out is to scroll through the columns to your right, and you will see the survey results stored for that particular response.  In my example, using all Rating Scale questions, all of the survey results are stored in the ntextX columns, where X is equal to a number.  This is a real pain, as Rating Scale questions each get their own ntextX column, but then each Option & answer pair for that question is dumped into the single column that then needs to be parsed.  So, let’s take an example:
    1. My survey has one Rating Scale question about ‘Time Management’, and there are 6 options that the user can rate under this question.

    2. Now, once this is stored in the database, it appears for me in the following format in the ntextX column:

      Establishes satifactory work-life balance.;#+4#Prioritises workload and conflicting demands to achieve key results.;#+4#Organises self and others to meet deadlines.;#+3#Delegates work to achieve optimum overall performance.;#+3#Challenges and improves upon processes which waste time.;#+4#Reduces the impact of distractions upon self and others.;#+3#

    3. So now, in order to use this data, I would have to parse it.  But obviously, the data is there and available for you if you need to work with it.

Finally, just a quick reference guide for you.  I’ve done some research, and I’ve found that the different types of survey answers are stored in different columns… this is a summary of my findings.

TYPE OF ANSWER COLUMN NAME
Single line of text nvarchar
Multiple lines of text nvarchar
Choice nvarchar
Rating Scale ntext
Number float
Currency float
Date & Time datetime
Lookup int
Yes / No bit

And if you are interested in the database schema, you can always check out:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/spptsdk/html/tsdbTables_SV01051532.asp

Advertisements

28 Comments »

  1. I noticed on 2 of the questions in the survey you were able to use multiple lines. How do you do that?

    Comment by Mark — August 1, 2006 @ 9:47 pm

  2. I get that after so many characters it drops down one line, but how can I get it to drop down with less characters?

    Comment by Mark — August 1, 2006 @ 9:51 pm

  3. You have no idea how much I appreciate this information. I have been posting to multiple forums and finally found this.

    I will test it out tomorrow!

    Thank you!!

    Comment by Seanzies — August 2, 2006 @ 8:40 pm

  4. Hi Mark,

    The survey page was edited in FrontPage. Word wrap was shut off and the were using absolute values instead of percentages. Give this a shot and let me know if you have any questions.

    Thanks,
    Chris

    Comment by cregan — August 3, 2006 @ 8:12 pm

  5. Seanzies,

    Good luck – let me know how it goes!

    -Chris

    Comment by cregan — August 3, 2006 @ 8:12 pm

  6. Chris,

    Thank you so much for the article. It provided a great roadmap and I have been able to find all the pieces I need.
    I am struggling with parsing the values out of ntextX field and making them usable.
    Do you think you could point me in the right direction of where to begin, how you did it, etc…? I would really appreciate it.

    Thanks,
    Dennis

    Comment by Dennis Porter — August 9, 2006 @ 8:00 am

  7. You can also get at the results in an XML format by making an HTTP request to http://myserver/sites/mysite/_vti_bin/owssvr.dll?XMLDATA=1&List=%_LISTIDTOKEN%

    where you replace %_LISTIDTOKEN% with the listID you got from the Lists table

    Comment by Rebb — September 20, 2006 @ 8:17 am

  8. Hi guys,

    How can we get a sample of this webpart?

    Comment by Roberto — September 27, 2006 @ 10:56 am

  9. Roberto,

    I’ll check with the developers and see what we can do about getting a sample of the code up for reference.

    Thanks,
    Chris

    Comment by cregan — September 28, 2006 @ 1:13 am

  10. Hi,
    Can I create a Online test using survey. I am a trainer and would want to create few assessements which can be hosted on Sharepoint. It should give me the results also.

    Thanks
    naresh

    Comment by Naresh Tatiraju — January 31, 2007 @ 10:55 am

  11. Hello,

    I wanted to see if we could look up anonamous info on a user in Sharepoint.

    For example, we’ve set up a survey on our Intranet for which we’re getting feedback from our user community on an application we’ve completed recently, but I wanted to track down who said what.

    Thanks,
    Michael G

    Comment by Michael G — June 4, 2007 @ 3:51 pm

  12. Hi, well, this post was useful to me when I need to save the GridChoice values in SharePoint 2003, but now with SharePoint 3.0 it’s different!. Can you post the new way which the values are saved?

    Thanks!

    Sorry, my english is bad because I speak spanish =)

    Comment by Martin — June 27, 2007 @ 9:43 am

  13. this is very handy information. Another way of solving this problem is to use a Drop Down menu Choice question format instead of the radio button scale format. Not quite as elegant but exports properly into Excel and can be easily then associated with the “correct” numeric values while still preserving the cool functions and speed of SP surveys.

    Comment by Russell — July 25, 2007 @ 8:37 pm

  14. Question about reusing the survey in a training environment. Let’s say that you have one class a month for twelve months, and you have the PERFECT survey that you would like to reuse. At the end of the year, you would like to take the 12 surveys and munge them together to create a yearly report on the effectiveness.

    How do you set about reusing the survey? I assume it will be a combination of 1) save survey as a template 2) create a new survey based on the template 3) Turn off access to previous surveys (so you don’t confuse students). 4) Some type of reporting webpart to report the data.

    Seems to me it would be easier to create several columns (METADATA), Month, Year, Survey name or Number. Then search on the columns to tie like survey data together?

    Comments or suggestions welcome!

    Comment by Walt — August 24, 2007 @ 7:52 am

  15. […] ב- SQL של MOSS? אז מסתבר שהייאוש דחף מישהו לצעד קיצוני של חשיפת הדרך לגשת ישירות לטבלאות הסקר ב- SQL … מיותר לציין (והוא ציין) שגישה כזו אינה נתמכת ע"י […]

    Pingback by סקרים רבותי, סקרים! חלק 2 - MOSS is my middle name — January 14, 2008 @ 4:41 pm

  16. Thanks alot for this (finding the data).
    I have wsp 3.0. Your survey looks nicer than the template produced surveys I get. Is that a customer survey template, you used?
    michael moore

    Comment by Michael Moore — February 4, 2008 @ 3:05 pm

  17. i meant custom…. not customer!!!!

    Comment by Michael Moore — February 4, 2008 @ 3:06 pm

  18. Hi Michael,

    Sorry for the delayed response… but this is the standard survey, however, we are using a custom-developed web part to pull back the data and display it nicely.

    Thanks,
    Chris

    Comment by cregan — February 13, 2008 @ 11:28 pm

  19. Did you find out how to get the questions to go with the answers? I can’t find a table that holds these… Cheers, Mark

    Comment by Mark — April 16, 2008 @ 10:28 am

  20. hi how can i create questions those in the above format in moss 2007

    Comment by deepak — September 10, 2008 @ 11:48 am

  21. good start, but really what we need is to create a view that joins in the questions, and maybe something that can return a dataset of response based on a like search of questions.

    If anybody knows where we can get this.. please forward it to me!

    Thank you.

    Comment by jc — December 23, 2008 @ 5:49 pm

  22. Accessing the data directly breaks your warranty and support with MS should you be really doing it this way ?

    Comment by devsp — January 19, 2009 @ 9:35 pm

  23. Devsp – that is why I put my warning in the blog post. If you are just running queries against the data, you shouldn’t break anything. Just be careful, and if you’re not sure – don’t touch!

    Comment by cregan — January 19, 2009 @ 10:48 pm

  24. I was searching for same requirement and this article helps me a lot. So much thanx to author of this article.

    Comment by Bilal Iqbal — March 20, 2009 @ 6:49 am

  25. Hi, this post really helps since I have a survey and not able to see all the results(saved results). However, is there anyway to force the data in the database to be displayed in the survey?

    Thanks,
    Shelley

    Comment by Shelley — April 6, 2009 @ 11:32 am

  26. Where the Questions get stored in database ? I appreciate any help

    Comment by Amir — May 6, 2010 @ 8:02 pm

  27. Interesting article you’ve got there. Thanks a lot.

    Ferienparks

    Comment by woordlaro — June 2, 2010 @ 4:40 am

  28. I know i’m late to the party here but thank you so much for posting this!

    Comment by richard1014 — February 17, 2017 @ 10:45 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: