Category Archives: Excel

Excel Pivot tables & Role playing dimensions

I’m not a huge fan of having a rant on blogs, but am going to break that rule briefly.

Role playing dimensions have been around in SSAS since 2005, unfortunately Excel 2007 & 2010 don’t handle them gracefully at all.

Below is a simple pivot table that shows the time between the week an order is placed (Date.Calendar Week of Year) on columns and the week it is delivered (Delivery Date.Calendar Week of Year) on rows. This pivot table is in tabular form and has the option Display|”Display field captions and filter drop downs” set to True (which makes the attribute names appear as circled.

What you’ll notice is that the Report Filter area shows the dimension name for Ship Date as well as the attribute name as Ship Date.Calendar Week of Year. So obviously this meta data is being persisted through to the pivot table, yet it isn’t possible to show this on the rows or columns.

PivotTable_RolePlayingDimension

What we would really like to see is an option to add these dimension names to the field captions or rename attributes as requested in Chris Webb’s MSDN post which I suggest you vote for.

As a a workaround I suggest moving your pivot table to cell “B2” (using the “Move PivotTable” button then have the below VBA using a PivotTableUpdate event on the worksheet “Sheet1”.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

'Add_RolePlaying_DimensionNames

Set pvtTable = Worksheets("Sheet1").Range("B2").PivotTable
iRow = 0
iCol = 0

'Clear First Row
Worksheets("Sheet1").Rows(1).Clear

For Each pvtColumn In pvtTable.ColumnFields
    iCol = iCol + 1
    Cells(1, iCol + 2).Value = pvtColumn.Name
Next pvtColumn

'Clear First Column
Worksheets("Sheet1").Columns(1).Clear
For Each pvtRow In pvtTable.RowFields
    iRow = iRow + 1
    Cells(iRow + 2, 1).Value = pvtRow.Name

Next pvtRow

End Sub

This will insert the below labels into your worksheet, while you still wont see the dimension names in the field list at least it’ll be on your worksheet.

PivotTable_RolePlayingDimension2

%d bloggers like this: