“Unsupported features” warning message from Excel Services

(Thanks to Ankur Goyal, one of the PowerPivot mid-tier testers, for this detailed write-up)

You might have noticed that Excel Services sometimes complains about how the PowerPivot workbook is organized. It reports an warning error like this one::

In this posting, I’ll talk about why these rectangles exist (i.e. what they do) and how you can have this error come up – along with a fix for how to stop them.

Let’s start with the fix – and then I’ll explain how this error arises and how the fix works.

1. Open the workbook in PowerPivot client and click on the any Pivot chart in the workbook and click on Selection Pane from the Format menu.

2. You will see a new pane “Selection and Visibility” right next to Field List. . In the pane I selected objects with name “Rectangle…” and deleted them. In the selection pane you see all shapes on the sheet: charts, slicers, rectangle shapes etc. You need to delete all rectangle shapes in order to get rid of the warning. Sometimes when you delete rectangle shape new one gets created, you can leave these new shapes alone. When you save workbook, they will be removed by our add-in. In CTP3 we used to create rectangle shapes with name O…, in the RTM build they have default name “Rectangle …”.

3. Save your workbook and upload it back to SharePoint. Clear your browser cache and you will notice that the warning message is gone. Don’t worry if rectangles come back after you delete them. This is expected behavior (because of the way that the add-in works – it always needs the rectangles to be there – our goal here is to delete the old ones.

———————————————————————————————————

Ok. The fix is done. Now let’s get our geek hats on and talk about what is happening and why.

First, what is the purpose of these rectangle areas? You will notice that each rectangle area surrounds a slicer area. The PowerPivot addin uses these rectangles to auto-position the slicers. When the add-in starts up, it create a rectangle for each slicer area. When Excel closes, the add-in removes the rectangles that it added. The add-in won’t touch other rectangles – it just deletes the ones that were added. Thus the normal condition is that the workbook with embedded PowerPivot data does not have rectangles in it.

This is good because Excel Services does not support rectangles. In fact it does not support any ‘shapes’ in the workbook. If they appear, then an error message is generated. This is the famous “Unsupported feature in workbook” error message.

Things are all well and good – The PowerPivot add-in does its thing – and Excel Services does its thing –> all is great.

Now along comes a bug in the PowerPivot add-in. There is a sequence of steps (in fact, a commonly used sequence of steps) that can cause the add-in to not remove the rectangles. And once they are in the workbook, there is no automatic way to remove them. You have to use the fix above to remove them manually.

So, Dave, don’t hold us in suspense any longer! What’s the root cause?

(do you know you know how many PowerPivot developers it takes to screw in a light bulb?)

Ok, Sorry.

The bug is that if you hit Close in Excel with a dirty workbook, then when the data is saved to the workbook, but the rectangles are not removed (it is caused by a weird way that the Excel OM raises events). If you hit Save and then Close things are OK. So whether or not you get into this situation is not related to if you save or not – but rather it is HOW you save.

Always Save and then Close – never just Close Excel.

Enjoy.