08 March, 2011

Export To Spreadsheet not showing response columns from Survey:MOSS 2007

Survey list has so many limitations and this list is very unique from all lists which are present in sharepoint.

I have handled so many issues regarding survey from last few weeks. From all of them, i have noticed one which is very unqiue and need some tweaks to get your issue resolved.

When we used "export to spreadsheet" option for survey then we dont get all the columns data  and shows only time stamp and items field.

End user opened a ticket for this and it took some time to resolve this issue as it was new for me also.

Please refer the following steps regarding the same:
1. Open the survey list

2. Edit Page

3. Modify shared webpart

4. Edit the current view

5. Select the appropriate view in which you wants to make the changes

6. Copy the View GUID ID from the URL and paste in a note pad https://sharepoint2007/sites/AHRC/Comp%20Survey%20Report/_layouts/viewedit.aspx?List=%7B415A8DFB%2D0FFF%2D4412%2D925D%2DC4191DDF4127%7D&View=%7B02550769%2DEBF3%2D45F8%2D987C%2DE19F51CC06DB%7D&Source=https%3A%2F%2Ftestl%2Ewsstools%2Econsumer%2Esharepointgroup%2Enet%2Fsites%2FAHRC%2FComp%2520Survey%2520Report%2Fdefault%2Easpx%3FPageView%3DShared

7. Edit the GUID to eliminate few characters , notice the change carefully: %7B02550769%2DEBF3%2D45F8%2D987C%2DE19F51CC06DB%7D&  becomes “02550769- EBF3- 45F8-987C-E19F51CC06DB”

8. Select the number of columns as per your requirements and click OK

9. Click on Actions-Export to spreadsheet. Now as you mentioned in the screenshot below there is a view ID in connection properties-Replace the View GUID with the one copied above and click OK.

10. It will give you the correct spreadsheet.

If you have any queries/questions regarding the above mentioned information then please let me know. I would be more than happy to help you as well as resolves your issues, Thank you

21 comments:

  1. Amol,

    We are using SP2010 and created a Survey with Branching logic with users can read and edit only responses created by them. The Export to Excel is missing. Any ideas or suggestions on how we can export all the responses to Excel.?

    Appreciate your Help

    ReplyDelete
  2. In SharePoint 2010, you should Activate the feature 'SharePoint Server Enterprise Site Collection features' to see the 'Export to Spreadsheet' option in Actions menu.

    please check and let me know the results, thanks for your patience

    ReplyDelete
  3. Amol,
    The 'SharePoint Server Enterprise Site Collection features' is already activtaed and export to Excel is available on all other Surevys except this one. Does the Branching logic or any changes in the webpart views in the Overview.aspx page make this Excel option go away?

    ReplyDelete
  4. I have successfully reproduced your issue :) now i need to see that the following steps will resolves your issue or not...

    Open the survey list in which you are facing the issue

    edit page

    modify shared webpart

    Under the selected view option

    select 'current view' and click on apply

    as soon as you click apply then check the results

    this should resolves your problem.

    Note: if i select 'summary view' then export to spreadsheet option will be gone.

    ReplyDelete
  5. make sure you take the proper bakup first and then implement the above mentioned steps.

    please let me know in case of any queries, thank you

    ReplyDelete
  6. Amol,
    Thank you for your help.

    I already changed the Current View to various views by creating multiple pages with different views. Also created a Custom View and add this view to the page.

    I also exported this Survey to my Test Environment and I still don't see the Excel option. "hat I would assume is changing the View from the Default Overview to other view would have made the Excel export go away or may be due to the branching logic."

    As suggested above, I tried changing the view to the Current View and this did not help either.

    I can see the Graphical Summary of all the responses.

    We need to present the Survey information Collected to the Senior Staff Further Analysis.

    Is there a easy way to pull all of this info and then present it in an Excel Format(I mean fro the Content DB)

    ReplyDelete
  7. Amol,
    This is all SP2010 Enterprise

    ReplyDelete
  8. Amol,
    This did not help.
    I already tried the above options but invain.
    Is there any other way to get the Survey Data?

    ReplyDelete
  9. try saving the survey list as a template and create a new one based on the same.

    let me know he results of this input. till that moment, i am trying some other options.

    ReplyDelete
  10. Amol,
    I created the Survey template and even tested the same with a new survey on my Test Environment. the excel export option is still missing.

    ReplyDelete
  11. If you try to create a access view/standerd view by using the following steps:

    - Go to any list and click on create view
    - Copy that URL
    - go back to the survey list now
    - settings
    - list settings
    - copy the url from the browser (i.e. List=)
    - paste it in the create view (first step)
    - you will get the create view page
    - try to create the view and let me the know the results

    ReplyDelete
    Replies
    1. Hi Amol. Do you know of a way to have some of the survey responses saved or exported to a folder based on how a question was answered? I have a training evaluation survey. One of the questions is: Who was the trainer? Based on who they select from the drop down list in the survey is how I want to view the results. (i.e. trainers Amy, Teresa, Tammy) I want each trainer to be able to review surveys completed on their sessions but not be able to view responses done on other trainers. I also want them to be able to view the responses at any time and see the updated results. Thanks.

      Delete
  12. First of all, I would like to set up proper expectations regarding survey list as this list is having restricted functionality and drawbacks can be overcome by means of custom list.

    Now regarding export, yes -you can export your survey data to excel spreadsheet and by means of normal sorting & filtering process, we can get the results as per our requirements. [Survey list-action-export]

    Now regarding second question and 3rd one- requirements can be partially achieved by means of creating a different view in the same survey list but consist of so many loopholes

    I would recommend you to explore the custom list functionality and get back to me in case of any questions, Thanks for your patience

    ReplyDelete
  13. Hi Amol,
    I am backup site and restored on new MOSS 2010 server and few links can be edited through SPD but its showing incompatible page conatins code blocks so could not see changes in preview in browser mode.
    Please guide me what could be reason?

    ReplyDelete
    Replies
    1. Could you please post the complete error message?

      I need to understand two things:
      are you getting this error message in browser/in SPD?

      is this happening with only one site collection or across all site collections?

      Is this site collection has been customized or default site?

      if you change the master page then still the error persist with the same error message?

      Delete
    2. An error occurred during the compilation of the requested file, or one of its dependencies. The type or namespace name 'UserProfiles' does not exist in the namespace 'Microsoft.Office.Server' (are you missing an assembly reference?)

      Troubleshoot issues with Microsoft SharePoint Foundation.

      Correlation ID: 9f7ce74f-9387-403e-bdcc-318e5d0862ea

      Date and Time: 5/9/2013 12:03:18 AM


      Yes, its not working in SPD and is at one site collection only..with every click on preview in browser, feature id keeps changing but other error details are same.
      Also other pages have successfully chnaged with new site link but few pages still showing old url

      Delete
    3. if the correlation is continuously changing then we will definitely get something in the SP logs. we will definitely look that but let's check some other factors...

      One thing is pretty much sure that this is happening because of the customizations that were present on that master page which are not reflecting properly after the migration.

      On the site settings page, there is one option which has to use after the migration to make the SP2010 compatibility. did you check that? basically what it does- it will change the moss2007 look n feel to SP2010 interface as well as make a proper compatibility.

      also, you can try to close all the WebParts which are present on your site home page and then check the results. How you can do this? it's very simple.

      suppose this is your URL: http://mssharepoint/sites/SP2010/pages/default.aspx

      what you need to do: append the above URL with ?contents=1

      it will show you the web parts maintenance page, select the WebParts and click on close from the top. now u can check the results that page is rendering properly or not.

      if you are worrying about the webparts then let me assure that you can add the close webparts anytime because you are closing them and not deleting....

      Delete
  14. Thanks Amol, it works for me also! nice blog..

    ReplyDelete
  15. I believe everything posted was actually very logical.
    However, think on this, suppose you added a little information?
    I ain't suggesting your information is not solid, however what if you added a headline to maybe grab people's attention?
    I mean "Export To Spreadsheet not showing response columns from Survey:MOSS 2007" is a little boring.
    You ought to look at Yahoo's home page and note how they create post titles to get people to open the links. You might add a related video or a related picture or two to grab readers interested about what you've written.
    Just my opinion, it would bring your blog a little livelier.


    my blog :: Air Max Pas Cher

    ReplyDelete
  16. Hi Amol,
    I have Sharepoint Survey 2007 with 3 columns with Rating scale and Created a "Team" view Filtered by My manager field which is defined by user, so it means Manager can see list items created by his/her reportee, and we have provided Export to Spreadsheet option in team view, where manager can extract report and see responses of his team inclusing rating scale fields as well, We want to show average of rating scale columns withing that report when manager exports it.
    How can we do it? I checked in SPD 2007 but coudl see only "Count" option, no average or sum etc..
    Please help me out.

    ReplyDelete
    Replies
    1. that's right, you will not find the avg function in a SharePoint default manner.

      what i can suggest is once you export the data in spreadsheet then whatever the functions which are present in excel, you can apply those on the specific columns and get the required results.

      Delete

Your feedback is always appreciated. I will try to reply to your queries as soon as possible- Amol Ghuge

Note: Only a member of this blog may post a comment.