WSS survey data reporting

[Avi]

Hi,

I am creating a SRS report where I need to report workspace survey
data. I am unable to find where survey data is stored in the PS2007
databases. I am mainly using the reporting db but can tap into any of
the other databases if that is where the survey data is stored.

Basically, PMs are creating Survey (Site
actions>create>Tracking>Survey) in their respective WSS sites.

Thank you for your time in advance.
Please let me know if something is unclear.

Avi

 

[Chak]

Hi Avi,

I don't think you will find the survey data in reporting database.
Reporting database is useful to get issues, risks and document details
from workspace sites. Also if you have custom columns in the issues &
risks, you will not see these columns in reporting database.
Reporting database is good for default workspace columns in issues &
risks.

If you have any custom columns or custom lists, you have to write your
own SQL view on WSS_Content database to get the data. It's an complex
SQL View to retrieve data of custom columns. (Very soon, I will be
posting these custom SQL views on my website. Stay tuned.)

Survey will be treated as list in WSS. Meanwhile, here is the clue for
you to do treasure hunt

1) Run the following query on WSS_Content database (Change where
condition accordingly with your Survey title)

Select * from dbo.AllLists

Where tp_title like '%survey%'

2) You will get tp_id, tp_WebID etc. from above query. Also join this
query with Sites table to find Site_ID

3) Start looking in AllUSerData table by joing with tp_id, tp_WebID
and Site_Id of above queries. You will find it here.


Thanks
Chak
http://www.epmcentral.com

 

[Avi]

Hi Avi,

I don't think you will find the survey data in reporting database.
Reporting database is useful to get issues, risks and document details
from workspace sites.  Also if you have custom columns in the issues &
risks,  you will not see these columns in reporting database.
Reporting database is good for default workspace columns in issues &
risks.

If you have any custom columns or custom lists, you have to write your
own SQL view on WSS_Content database to get the data. It's an complex
SQL View to retrieve data of custom columns. (Very soon, I will be
posting these custom SQL views on my website. Stay tuned.)

Survey will be treated as list in WSS. Meanwhile, here is the clue for
you to do treasure hunt

1) Run the following query on WSS_Content database (Change where
condition accordingly with your Survey title)

Select * from dbo.AllLists

Where tp_title like '%survey%'

2) You will get tp_id, tp_WebID etc. from above query. Also join this
query with Sites table to find Site_ID

3) Start looking in  AllUSerData table by joing with tp_id, tp_WebID
and Site_Id of above queries. You will find it here.

Thanks
Chakhttp://www.epmcentral.com







- Show quoted text -

Thanks Chak for the quick reply. I will do deep dive based on your
feedback and post back with findings.

 

[Avi]

Thanks Chak for the quick reply.  I will do deep dive based on your
feedback and post back with findings.- Hide quoted text -

- Show quoted text -

Hey Chak,

I looked into the WSS_CONTENT database and found some survey data,
unfortunately not complete. So, I ran the below query

SELECT DISTINCT AllLists.tp_Title, AllUserData.nvarchar1
FROM AllUserData INNER JOIN
AllLists ON AllUserData.tp_ListId =
AllLists.tp_ID
WHERE (AllLists.tp_Title LIKE N'%survey%')

AllLists.tp_Title is the Survey Name
AllUserData.nvarchar1 is the list of questions.

What I am unable to find is the responses to the questions. I looked
through all the other fields in the AllUserData but could not find
it. It seems like it would live there but maybe I am not seeing
something obvious. If you can please help that will be appreciated.
Thanks!

 

[Chak]

Hi Avi,

Your query is not complete yet, you didn't followed my step 2. You
have to use Sites and Webs table and finally you are joining four
tables. Please read my original posting again.

Thanks
Chak
http://www.epmcentral.com

 

[Avi]

Hi Avi,

  Your query is not complete yet, you didn't followed my step 2. You
have to use Sites and Webs table and finally you are joining four
tables. Please read my original posting again.

Thanks
Chakhttp://www.epmcentral.com









- Show quoted text -

Hey Chak, thanks for the follow up. I did not find much useful data
in the sites table but the webs table helped and completed (almost) my
search for survey data. The query result is as below. The only issue
I am facing now is that I am unable to find where individual survey
questions are stored? I searced through the alluserdata tables but
did not find individual survey questions stored. Therefore, I might
need to hard code the question(s) in the SRS report (not elegant).
The query before was actually presenting 'list' results. I now am
getting the correct survey results and find that tp_fields has all the
survey questions in one big html code field, leaving it not usuable in
the report.

SELECT AllLists.tp_Title AS [Survey Name], AllLists.tp_Description
AS [Survey Description], AllLists.tp_Fields, AllUserData.nvarchar3 AS
[Survey Response1],
AllUserData.nvarchar4 AS [Survey Response2],
AllUserData.bit1 AS [Survey Response3], Webs.Title AS [Project Name]
FROM AllUserData INNER JOIN
AllLists ON AllUserData.tp_ListId =
AllLists.tp_ID INNER JOIN
Webs ON AllUserData.tp_SiteId = Webs.SiteId AND
AllLists.tp_WebId = Webs.Id
WHERE (AllLists.tp_Title = N'Test Survey')

Any feedback?
Thank you!