Posts Tagged excel

PowerPivot DAX Session Notes 1 – Dates

Part of my preperation for TechEd Africa this year has required me to really get into PowerPivot and DAX, and I thought I’d share some of the lessons learnt along the way…
So, in standard Analysis Services models, the concept of a Date dimension is quite accepted… and the world of PowerPivot is not much different.
Part of creating this type of Dimension / Lookup table involves adding attributes like ‘Month’, ‘Year’, ‘Day of Week’ to a given date. Given the date expressions in Excel, it is quite straight forward to add these attributes using these standard Excel functions.
One that I didn’t find out the box though was a MonthName(<date>) function.
So, approaches to getting this right are:
1. Using a massive switch / case statement; yuck 🙂
2. Using a lookup table of sorts to map MonthNumber to MonthName; less yuck.
3. Use Excel date formatting features to achieve the same result… (and yes, I only learnt about this this morning, always more to learn)
 – =TEXT([@Date],”mmmm”)
Another common hinderance is the sort order of months.
By default sorting is alphabetical, meaning April suddenly appears before February. (and oddly enough this does confuse end users!)
Again, the more SQL based approaches would involve injecting some kind of number into the month name and sorting accordingly. (But 02-February is also not so user friendly on reports!)
Fortunately, Standard Excel sorting functions understand Months. So, if you sort the months A-Z, Excel will sort correctly.

, ,

Leave a comment

Excel 2010 – Save & Send to SharePoint Issue

So, I have just finished installing (yet another) BI 2010 Virtual Machine, on Windows Server 2008 R2 RTM.
On this one, I was unable to use Excel’s “Save & Send” feature to publish a workbook to Sharepoint.
When I tried to use the “Browse for a Location” dialog, I would type in the Sharepoint URL, and got a message along the lines of “this application can’t open this location”.
Quite odd.
Turns out that you need the Desktop Experience feature turned on Windows Server 2008 R2 to allow this to work.
Thanks Alan Whitehouse!

, , ,

Leave a comment

Thoughts when Installing PowerPivot – Client

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

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:

  1. VSTO installed and running
    1. The Complete install of Office manages this by default
    2. If installing only Excel, be sure to select “Shared Components” as well to include VSTO
  2. The PowerPivot add-in is enabled. (once installed)
    1. Check under File – Options – Addins
    2. Ensure that the add-in is not in the Disabled State
  3. If the add-in is still gone, even if both are ok, it is possible to expose the VSTO exceptions on Excel start up
    1. Create an environment variable, VSTO_SuppressDisplayAlerts, and set to 0
    2. Check 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”.

The settings window is also the equivalent of HelpàAbout in most apps. The RTM current version of the add-in is 10.50.1600.1


Leave a comment

Excel 2007 / 2010 + Pivot Table Column Width

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.