All about Business Intelligence

If you have ragged dimensions, i.e. some paths from the top node to a leaf node differ in length, and you are using Excel 2007 as a front end, then… bad news! It will not work.

A client tool can get the ragged dimensions from the server if it uses MDX Compatibility=2 in its connection string. See MSDN: Working with Ragged Hierarchies.
But Excel 2007 forces the connection string to contain MDX Compatibilty=1. There is a trick suggested here to force it to use MDX Compatibility=2, alas this has severe side effects. The trick is to put the following in the connection string:

Extended Properties="MDX Compatibility=2";MDX Compatibility=2;

In my tests it displays the All level somewhere in the middle of the hierarchy, shows the same node at different levels, etcetera. My advice: do not do this, as the Excel pivot table clearly does not know how to handle ragged dimensions.
In exceptional cases you could get away with a work around suggested by Chris here.