I am able to produce about 20 pivot tables from SSAS cube one by one. Is it possible to produce these tables programmatically with header and footer information?
Please note that the end users like to use excel.
View Complete Post
SQLServer 2008 R2
Excel 2010 x64
I have a fairly complex Excel report where a raw data sheet is updated from 10 or so PivotTables pulling from an Analysis Services Cube, (2008 R2), with a VBA script that refreshes all pivottables, and then does some post-processing, formatting etc.
This was originally developed in Excel 2003, and the updating of all the pivot tables ran in under 10 seconds
I've now recreated in Excel 2010 and *each table* is taking up to a minute to refresh!
I've also observed that I can get the refresh time of a single table back down to 1sec, if I delete all other pivottables from the worksheet/book.
Its deeply frustrating, as I'm touting 2010 as being the way for our business to go...it won't help the cause if I deliver something with a x60 increase in runtime!
I've run traces of both the 2003 and 2010 versions executing against the SQL Server, the only thing I can see is that the 2003 version passes 1 single MDX query, whilst the 2010 version appears to be firing it in sections.
I've posted this in the Excel forum too.
Please can anyone help?