Excel 2010 PivotTable What-If Analysis (Writeback)

re: Excel 2010 PivotTable What-If Analysis (Writeback)

Sam Kane — Wed, 23 Feb 2011 21:07:28 GMT

Here are this and some other articles on SSAS Write Back: <a href="ssas-wiki.com/.../Articles

re: Excel 2010 PivotTable What-If Analysis (Writeback)

Diego Oppenheimer — Mon, 02 Nov 2009 21:51:27 GMT

@ Billy Gee

This is an OLAP only feature. What this means is that only OLAP Engines like Microsoft SQL Analysis Services can be used as data sources for the What-if Analysis feature.

re: Excel 2010 PivotTable What-If Analysis (Writeback)

Billy Gee — Mon, 02 Nov 2009 20:33:36 GMT

Are there file size limitations regarding using this with Access data or internal Excel data?

re: Excel 2010 PivotTable What-If Analysis (Writeback)

Diego Oppenheimer — Tue, 20 Oct 2009 20:20:31 GMT

@Colin Banfield

Sorry about the confusion I misinterpreted your scenario. The answer you got is correct we do not allow automatic recalculation of formulas in What-if Analysis. The reasoning for this was to avoid infinite loop scenarios.

A couple of more scenarios not supported with What-if analysis are:

- Structured references in value cells (Ex: Structured references to tables).

- External names (names referencing another workbook)

- "Advanced formulas (ex: UNION, INTERSECT)

re: Excel 2010 PivotTable What-If Analysis (Writeback)

Colin Banfield — Mon, 19 Oct 2009 23:04:09 GMT

Diego, I don't mean to belabor the issue, but the scenario I described in my bug report was using a simple reference on the *same* worksheet as the PivotTable. For example, in the PivotTable cell entering something like =H5 instead of a fixed value like 435670. When the value in H5 changes, the cell in the PivotTable is updated, but the PivotTable is not recalculated (although set to automatic). It was this simple scenario that was indicated as not being supported. Was my report misinterpreted?

re: Excel 2010 PivotTable What-If Analysis (Writeback)

Diego Oppenheimer — Mon, 19 Oct 2009 21:59:02 GMT

@ Colin

You can have external references (to other workbooks) in What-If Analysis value cells but Excel 2010 does not support references to external names.

@Sam

No improvements in this area for this release. If you want to send me something explaining what sort of improvement you would want in this area that would be great (diego@microsoft.com)

@Lum

What help file are you referring to ?

re: Excel 2010 PivotTable What-If Analysis (Writeback)

Lum — Sat, 17 Oct 2009 12:29:00 GMT

Anyone able to get the help file to work?

re: Excel 2010 PivotTable What-If Analysis (Writeback)

sam — Sat, 17 Oct 2009 00:05:23 GMT

Are there any improvements to Calculated fields and Calculated items in a Normal pivot Table.

Will we be able to use Range formulas, refer to cells out side the table etc

re: Excel 2010 PivotTable What-If Analysis (Writeback)

Colin Banfield — Fri, 16 Oct 2009 17:39:24 GMT

"No that is not by design, just a bug present in Technical Preview."

Well, I submitted a bug report for this problem way back on Auguts 18th. I just received the official resolution as "By Design."

Here's the explanation:

"Thanks for the bug submission. This behavior is by design since we do not want to get into infinite loops when using formulas for values to write-back in PivotTables. So automatically calculate does only work for non-formula (numeric) values in PivotTable value cells."

It's unfortunate that Excel doesn't at least generate an error message when you attempt to use an external reference.

re: Excel 2010 PivotTable What-If Analysis (Writeback)

Diego Oppenheimer — Thu, 15 Oct 2009 01:43:15 GMT

At this time writeback in Excel 2010 is just leveraging the Writeback feature of SSAS. Assuming a provider was written to mimic the architecture it would be possible to use other data providers.

"It isn't clear what checks Excel uses to enable/disable the feature. It's disabled for standard PivotTables but enabled for Gemini PivotTables, although the latter doesn't support the feature."

This is just a bug in Technical Preview.

@ Lum

The SSAS cube creation wizard is not available in Excel 2010.