locked
Write-Back Functionality RRS feed

  • Question

  • I have been waiting for What-if analyis with pivot forever. For a min I thought microsoft has decided to make my dream come true. The excitment was short-lived as I can not get it to work and as I understand I need SQL and lot of cube knowlwdge to make it work. Does anyone know of any way to get write-back to work without having to get all fancy and expensive?
    Wednesday, November 25, 2009 9:10 PM

Answers

  • As you have observed, the current version of PowerPivot does not support writeback. You would need the traditional Analysis Services server to perform writeback what if type of analysis.

    Thanks,
    Lisa
    Wednesday, November 25, 2009 10:01 PM
  • Here are the steps to create linked table
    1. Assume you have a regular Excel worksheet that contains the data you want to change to see the what-if effect, with cursor in
     the current spread sheet, click PowerPivot tab, then Create Linked Table ribbon
    2. In the Create Table dialog box, make sure the cell range covers your table of data, and if the first row is column header, check "My table has headers"
    3. Click OK to close the dialog box. A copy of table is created inside PowerPivot workbook which is linked to the original Excel table. When you make any change to the Excel data, the linked table inside PowerPivot will change accordingly.

    Hope this helps. Please let us know if you have further question on this.

    Thanks,
    Lisa

    Monday, January 11, 2010 10:29 PM

All replies

  • As you have observed, the current version of PowerPivot does not support writeback. You would need the traditional Analysis Services server to perform writeback what if type of analysis.

    Thanks,
    Lisa
    Wednesday, November 25, 2009 10:01 PM
  • Note that you may still use the Linked Tables feature of PowerPivot though in order to get some level of what-if support without getting too fancy or expensive...

    Olivier [MFST]
    Wednesday, December 2, 2009 11:28 PM
  • Olivier,

    Do you mean just write directly to the table and then update PowerPivot? I am hoping there is a way to edit the data directly into the pivot table. I tried that and unless I am doing it wrong it is not working.
    Saturday, December 5, 2009 10:57 PM
  • Here are the steps to create linked table
    1. Assume you have a regular Excel worksheet that contains the data you want to change to see the what-if effect, with cursor in
     the current spread sheet, click PowerPivot tab, then Create Linked Table ribbon
    2. In the Create Table dialog box, make sure the cell range covers your table of data, and if the first row is column header, check "My table has headers"
    3. Click OK to close the dialog box. A copy of table is created inside PowerPivot workbook which is linked to the original Excel table. When you make any change to the Excel data, the linked table inside PowerPivot will change accordingly.

    Hope this helps. Please let us know if you have further question on this.

    Thanks,
    Lisa

    Monday, January 11, 2010 10:29 PM
  • Hi Lisa,

    What about writing back from powerpivot workbook (not in pivottable, but in data) to the original excel data file? Does that work as well? Thanks~

    Hoon

    Monday, December 6, 2010 6:47 PM