Office 2010: Excel 2010 – New Buttons on Ribbon for Pivot Tables – Custom Named Sets!

Just this week I blogged about adding Named Sets in VBA. Well lo and behold, in Excel 2010, there is a button “Fields, Items, & Sets” that lets you define your own Named Sets. Either with MDX or based on rows/columns you have on your pivot table


Works pretty slick! There goes the need for the custom VBA solution, which is fine by me. Although I am disappointed you still can’t create your own calculated measures. The OLAP Pivot Table Extensions add-in lets you, so I wonder why the built in functionality still doesn’t let you.

Excel 2007, OLAP Cubes: Customizable, User Defined Named Sets in Excel 2007 using VBA

In a perfect world, your master data and master data management (MDM) is set up so everyone can see things how they want. Categories of Items, Regions, etc, etc are all defined in your dimension data, and you can create hierarchies, etc that make sense and everyone is happy. But, this is not a perfect world 🙂

Some users want to see “their” items, “their” regions, etc. And they ask and ask for you to add it to the cube, but you have to deny them. Why? Because if you add their named set, you have to add all of them that are requested, that don’t make sense to 99% of the other people using the cube.

You probably could even go about doing some crazy MDX or something in your cube to create the named sets per user, but then you still have to manage it all in your MDX script in your SSAS project.

I blogged a while ago about the OLAP Pivot Table Extensions on CodePlex , which, is an awesome toolkit, but it is geared around Calculated Measures. I downloaded the code and took a gander here and there and it would be pretty easily modified to work with Named Sets (change xlCalculatedMember to xlCalculatedSet , add a radio button on adding a new calc, etc) – but, its in VS2005, and I have VS2008 (2005 BIDS, not C#), and right now I don’t feel like mucking around with that, maybe someone with more time, and more ambition can do it 🙂 – Or maybe when I get some free time I will take a look, but for now here is a solution.

I took the idea outlined at the bottom half of this blog  about named sets and creating them in macros..

Sub AddNamedSet()

Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String
Dim cbf As CubeField

Set pvt = Sheet1.PivotTables("PivotTable1")
strName = "[My Mountain Bikes]"
strFormula = "[Product].[Product Categories].[Bikes].[Mountain Bikes].children"
pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedSet
Set cbf = pvt.CubeFields.AddSet(Name:="[My Mountain Bikes]", Caption:="Mountain Bikes")

End Sub

That macro is pretty sweet, you can add a named set to your workbook. But, its pretty static. You have to edit the macro every time you want to add more items, it doesn’t update or remove the sets you might have, etc.

What I whipped up quick was an updated macro that lets you build customizable named sets based on data in another worksheet in your Excel file (you can imaging it coming from other sources – another Excel file, a SQL table, etc, etc). I did this with a cube we use at work, but for this example, I changed it to pull off the AdventureWorks DW SSAS DB, AdventureWorks Cube, (SQL 2005 edition)

First off, here is the macro: 

Text File of Macro: Custom_NamedSets_Macro.txt</a

Excel 2007 Workbook with Macro: Custom_NamedSets.xlsm

Sub AddNamedSet()

    ' data sheet top row must be set name [Set Name] and then item numbers after
    Dim sourceData As Worksheet
    Set sourceData = Worksheets("Data")

    Dim strName As String
    ' get the name of the set, including brackets []
    strName = sourceData.Range("A1").Value

    ' used range is the whole column, rangeVals is the item numbers
    Dim usedRange As Range
    Dim rangeVals As Range
    Dim maxRow As Integer

    Set usedRange = sourceData.usedRange

    maxRow = usedRange.Rows.Count

    ' get the item numbers
    Set rangeVals = sourceData.Range("A2:A" & maxRow)

    Dim strFormula As String
    Dim i As Integer

    ' loop through and build forumla
    strFormula = "'{"

    For i = 1 To rangeVals.Count
       strFormula = strFormula & rangeVals(i, 1) & ","
    Next i

    ' remove last comma and add last curly and tick
    strFormula = Left(strFormula, Len(strFormula) - 1) & "}'"

    ' get pivot table object
    Dim pvt As PivotTable
    Set pvt = Sheet1.PivotTables(1)

    ' Add a calculated member titled "[MySet]"
    pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedSet

    ' Add a set to the CubeField object.
    Dim cbf As CubeField
    Set cbf = pvt.CubeFields.AddSet(Name:=strName, Caption:=Replace(Replace(strName, "[", ""), "]", ""))

End Sub

Sub DeleteNamedSets()

    Dim pvt As PivotTable
    Set pvt = Sheet1.PivotTables(1)

    Dim i As Integer

    For i = 1 To pvt.CalculatedMembers.Count
    Next i

End Sub

Now, this was a 15-20 minute VBA hack. It could really use some cleanup, but works. What I did to make it work with Advendture Works was just put the whole “member” string in the data tab of the spreadsheet. In my case at work, I just was using item number and had some of the member string in the macro. But really you can see you could add a named set on each column, or something, you really could make this powerful. And since I added the delete, if they re-run the add, it will recreate the set, so they can modify the data and re-add. It should be expanded on and made a little more robust, but you get the idea. Now, think of how you could use this with your cubes, and how you could get your user’s thinking about ways to use custom named sets!

OLAP PivotTable Extensions on CodePlex

This weekend, I ran across this on CodePlex – OLAP PivotTable Extensions which got me thinking back to a post by the Excel blog about adding calculated measures and named sets in VBA (which is another blog post completely)

From CodePlex:

OLAP PivotTable Extensions is an Excel 2007 add-in which extends the functionality of PivotTables on Analysis Services cubes. The Excel 2007 API has certain PivotTable functionality which is not exposed in the UI. OLAP PivotTable Extensions provides an interface for some of this functionality.

What an awesome tool. I have been playing with it for a couple days and I have turned on some of the “power” users of the OLAP cubes to it as well. The first thing I thought of when running across this was “Woah, ok, when business users request calculated measures that might be more obscure, or just specific to them, they can add them! We don’t have to do a special release, maybe not even a release at al!”

The uses for this tool could be pretty extensive. You can import and export calculation libraries, you can also see the MDX that Excel is producing, which is another plus (I know there are other ways to get it, but this tool makes it easy!) – With the MDX you can just copy it and run it in SSMS to see the results there. You can see how Excel is doing things behind the scenes with your result set to make it look nice.

Another sweet feature, if you have a cube with tons of attributes, there is a search tab to search for the attributes you want.

I haven’t seen any issues yet. One user had to install the Visual Studio 2005 Tools for Office Second Edition Runtime which the CodePlex site says is required, so no big deal.

If you have tons of users using OLAP Cubes with Excel 2007, take a look at this free open source tool on CodePlex, you probably will get some good mileage out of it. I think Microsoft should put these features in the next version of Office!