I was working with SharePoint surveys the other day and discovered an old issue working with surveys and Microsoft Office that appears to have resurfaced.
We were trying to see what other reports we could generate from a SharePoint survey. Naturally with the option to export to Excel, we figured why not just export all the survey data into Excel and play with it there. To test it out I tried to get a variety of questions in the survey, one of these questions being the rating (matrix) survey question.
I created a few responses to the survey and attempted to export the list data to Excel (I’m running 2010 Beta). Everything came over well with the exception of the matrix type questions. I did some searching and this was also an issue with Excel 2003. However, people said it was resolved with Excel 2007. I installed Excel 2007 and sure enough the matrix questions imported into Excel 2007 just fine. I tried Excel 2010 once more and the values still failed to come over.
So, it appears that the problem with importing matrix type of questions from SharePoint 2007 to Excel 2003 has returned in Excel 2010. Granted, this is the beta, so maybe it will be fixed by the release.
I also want to do some testing with SharePoint 2010 surveys, Excel 2010 and possibly PowerPivot. I’ve been reading some great things about PowerPivot but haven’t had much of a chance to play with it for myself. Once I get a chance to do some testing with these tools I’ll write a new post. Hopefully there will be some good news about doing more in depth analysis of SharePoint Survey results using these new tools.