Posts Tagged excel
So, I’m in a session at MS BI Conference 2010 with Dave Wickert, and he’s doing a great job of working us through things to look out for when installing the PowerPivot client.
This is an Add-In that runs in the context of Microsoft Excel 2010. Any version of Excel that supports add-ins will support the PowerPivot add-in (which is almost any version!)
The download is available from http://www.powerpivot.com/download.aspx
Make sure you download and install the version that correlates to the version of Excel (not your Operating System). So, basically 32-bit Excel = 32-bit Add-in (regardless of 64 bit OS) And yes, you want to use the 64-bit version if at all possible. It allows you to create and manage larger datasets, any size that fits in RAM will work.
However, please note that only 4G files can be saved to disk, and only 2G files can be uploaded to Sharepoint. Yes, only J
Preconditions for the Add-in to work include:
VSTO installed and running
- The Complete install of Office manages this by default
- If installing only Excel, be sure to select “Shared Components” as well to include VSTO
The PowerPivot add-in is enabled. (once installed)
- Check under File – Options – Addins
- Ensure that the add-in is not in the Disabled State
If the add-in is still gone, even if both are ok, it is possible to expose the VSTO exceptions on Excel start up
- Create an environment variable, VSTO_SuppressDisplayAlerts, and set to 0
- Check www.powerpivotgeek.com for full details
Debugging options for the client add-in can be set through PowerPivot à Settings. It’s possible to log events off to a log file, which can be opened and analysed in SQL Server Profiler. Also possible to create a snapshot of the current memory usage, by clicking “Snapshot”.
Well, I’m going to write this particular blog post at the risk of looking incredibly silly, however it may help someone…
I’m in the process of creating some internal reports using Excel 2010 Beta on top of an Analysis Services back end. This requires the creation of a number of Pivot Tables, and some super slinky Slicers, and the report is looking really nice.
One small glitch in the process is that whenever I change Slicer values, the Pivot Table columns automatically resize, leaving me with a number of cells with the inevitable “###” values. This requires me to auto expand the column to see the values again. Not an ideal solution to present to my Financial Director J
To overcome this small issue, simply go to the Pivot Table options, and unselect the “Autofit column widths on update” check box. Set the column widths to what you need, and you’re in the game.