I reported the depreciation of the WITH CUBE functionality.
This is an interesting case that requires more investigation. Microsoft's Excel 2007 does not respect the NULL rows for subtotaling in the returned cursor using Microsoft Query into an Excel Pivot table. (By the way, kudos to Microsoft for the GREAT improvement in Excel 2007's handling of Pivot integration with SQL Server...more on this at a later time.)
When Excel grabs the Cube and puts it into Excel (again great job on the automation but Stored Procedures are not straight forward) it doubles all counts because it turns null into a row in the cube and calls it (blank).
Ok, so the solution is busy work (turn off the (blank) fields in all row groups. It is a bit clumsy but it works. The bigger question is shouldn't Microsoft take advantage of 2008 functionality and automatically adjust for the NULL (blank) rows coming from SQL Server 2008 in Excel?
This was the question I posed to the Microsoft Excel 2007 Team. The response from the Microsoft support team was "don't use this feature because it was being depreciated." Ok, fine.
Notice that the answer was not use the CUBE() feature. The reason it was not use the CUBE() feature was that the Excel Team had not caught up with the SQL Team.
So, with the CUBE() or WITH CUBE (much appreciation to the submission that cleared up the confusion on support for the 2008 TSL CUBE) functionality we are back at the same question "when will Microsoft fully support (automate) the CUBE functionality in Excel 2007 or higher?
Again, my hat is off to Microsoft's efforts to integrate Excel and SQL Server but I want (and my customers surely do) better automation support between Excel and SQL Server. The last time I checked both products were made by the same company and that company.
Cheers and Jeff again THANKS for your enlightening original article. I WoWed some customers this weekend based on some of the fundamentals your article provided.