SharePoint By Yagya Shree

How to find total number for incomplete surveys

with 2 comments

While working on Surveys on SharePoint, the strange question that we generally face is that, the number of surveys is different than the total surveys shown in the list.

Mostly, the reason is “incomplete surveys”. Also, we are asked many times about the list of users that have the surveys in the incomplete or saved status but unfortunately there is no direct method in the SharePoint UI which allow you to see this.

Today we came across the same question where user has 4 survey count but in the list he doesn’t see any survey response and the mystery again is incomplete surveys but user posed the same question,  which users are these that have not completed the survey?

I worked on the issue and while moving through different forums, got the following SQL statement which can be run against the content database of the site collection and get the user name.

Select userdata.tp_author,
(Select tp_Title from UserInfo where UserInfo.tp_ID=userdata.tp_author and tp_siteID=’SiteID’) As UserName
from USERDATA
where tp_ListId like ‘LISTID’
and tp_level = 255
Now, SiteID and LISTID are the fields that needs to be inserted.

1.       For Site ID, Open Central administration -> application management->site administrators-> Select the site collection and Site ID is shown in the URL.
2.       For List ID, Open the List with Admin account, click on settings->target Audience settings. LIST ID is the in the URL like {LISTID}.

Query executed today returned very clear results like:
tp_author           UserName
406                  Michael David Howard
220                Peggy Shank
410               Raymond Arthur Haddas
407              Tyler Matthew Dickman

Advertisements

Written by Yagyashree

September 26, 2012 at 11:31 am

Posted in sharepoint

Tagged with ,

2 Responses

Subscribe to comments with RSS.

  1. Would this query work the same for Claims Auth? I am running the query against my DB and getting a value of NULL for the UserName.

    Berta L

    July 8, 2015 at 1:36 am

  2. If you get NULL as username it’s probably because you’ve used the spweb Id instead of the spsiste ID

    Thomas

    June 3, 2016 at 8:38 pm


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

%d bloggers like this: