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

Advertisements

About Karl Beran

I take interest and satisfaction from enabling people within an organisation to have an accurate, concise and flexible representation of the right information they need, when (or before) they need it. I believe this allows the business to make both operational and strategic decisions quickly and with more confidence, thus improving the agility and efficiency of the company. Having worked with BI stacks from the major vendors, the focus of my blog is on BI principals and strategy with examples from the Microsoft stack as I believe it has the most dynamic and engaged user community with the knowledge base and components fairly readily available to new developers.

Posted on September 7, 2010, in Excel. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: