Category Archives: 12352

Cooking With CUBEs

Microsoft sneaked a couple of ne

As we should all know by now, PowerPivot provides Excel with a powerful way to harness data from one or more sources, and to do further analysis on that data within familiar pivot tables.

Furthermore, because PowerPivot is creating an in-memory cube of the data, it is possible to build an analysis using CUBE formulae. I have blogged a couple of times about CUBE formulae, in Cycling Through The Fog and in Cracking The Code.

In Excel, as with any development, you want your solution to be as flexible and dynamic as possible. This blog is about building dynamic tables using CUBE formulae, but to start with the following formula shows an example of a value extracted from a PowerPivot model using CUBE functions

=CUBEVALUE(“PowerPivot Data”,
CUBEMEMBER(“PowerPivot Data”,”[Measures].[Sum of SalesAmount]”),
CUBEMEMBER(“PowerPivot Data”, “[DimProductCategory].[EnglishProductCategoryName].&[Bikes]”),
CUBEMEMBER(“PowerPivot Data”,”[DimDate].[FiscalYear].&[2006]”))

Equation 1

This formula gets the Sales Amount from the PowerPivot cube for the Bikes product category, for the fiscal year 2006. There will be many values at this intersection, there can be many dates in 2006 and many products within that category, all pre-aggregated in the cube; the CUBEVALUE function returns that aggregate amount

We could build the whole table of values using similar formulae. In our table we need to know what the value is related to, so we have row and column headers that identify the intersection points. We could define those headers using the CUBEMEMBER functions giving a table such as shown in Figure 1 below, which shows a table based on AdventureWorks.

Figure 1 – Table of values over year and product category

The formulas for the headings are

=CUBEMEMBER(“PowerPivot Data”,”[DimDate].[FiscalYear].&[2006]”)
=CUBEMEMBER(“PowerPivot Data”,”[DimDate].[FiscalYear].&[2007]”)
etc. for the column headings,

Equation 2


=CUBEMEMBER(“PowerPivot Data”, “[DimProductCategory].[EnglishProductCategoryname].&[Accessories]”)
=CUBEMEMBER(“PowerPivot Data”, “[DimProductCategory].[EnglishProductCategoryname].&[Bikes]”)

etc. for the row headings.

The values at the intersection points simply use these heading cells like so

=CUBEVALUE(“PowerPivot Data”,”[Measures].[Sum of SalesAmount]”,$A3,B$1)

This is equivalent to the formula given in Equation 1.

Slicing the Vegetables

Further richness is bestowed upon us because we can also link slicers to our table, giving us the sort of filtering we have with the pivot tables. For example, Figure 2 shows the same data table built using CUBE formulae with a fiscal year slicer; the data reflecting the fact that only the years 2006, 2007, and 2008 have been selected.

Figure 2 – Table of values reflecting years slicer selections

Showing the slicer selections on your report has been covered elsewhere, but it is so useful and asked so often that I thought I would also cover. I also have a couple of variations that I haven’t seen elsewhere, which are worth presenting.

The Menu

Previously, as shown in the formulae in Equation 2, we built the row and column headers using hard-coded values for the year and category fields. We need to be more dynamic in how we list these values. To show the slicer selections as in E5, F5, etc., we need a list of values from which we can choose and display the individual ordered items. The CUBESET function gives us this. The syntax for CUBESET is

CUBESET(connection. set_expression, , [sort_order], [sort_by])

where connection is the cube, set_expression is the set of values required, and caption is a value to display. So, looking at cell D1 we have the formula

=CUBESET(“PowerPivot Data”,Slicer_FiscalYear,”Set of Years

Equation 3

which would look as shown in Figure 3 when added to cell D1 to build our set of fiscal years.

Figure 3 – Slicer years set formula

As can be seen, we use Slicer_FiscalYear as the set_expression, so the set will include all selected values in that slicer, with the caption signifying the cell contents.

So far, so good, but we still need to list those selected values. For this, we use the CUBERANKEDMEMBER function, which returns the nth, or ranked, member in a set. The syntax of this

=CUBERANKEDMEMBER(connection, set_expression, rank, )

where connection is the cube as before, set_expression is the set of values to choose from, and rank is nth value. So, to get the first member, we use

=CUBERANKEDMEMBER(“PowerPivot Data”,$D$1,1)

for the second

=CUBERANKEDMEMBER(“PowerPivot Data”,$D$1,2)

and so on.

Because there are 5 years in the PowerPivot model, and when filtered in the slicer we might be showing less than 5, we need to cater for a variable number of items. The simplest way is just to add an error wrapper around the formula,

=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,$D$1,1),””)

=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,$D$1,2),””)


Equation 4

Why Extra Ingredients?

We could nest the CUBESET function within the CUBERANKEDMEMBER,but that would mean that a set is evaluated 5 times. By defining the set in its own cell and referring to that cell within the CUBERANKEDMEMBER function, it is evaluated just the once. A small matter, but it makes the spreadsheet easier to maintain, and is more efficient.

Cooked To Perfection

It’s as simple as that.

But hang on a minute, have we overcooked it?

Looking at the syntax definition for these two functions, we can see that they both take set_expression as an argument. The CUBESET function is passed the slicer values as its set, and in turned is passed to the CUBERANKEDMEMBER function as its set.

As the slicer values is a set_expression, you would think that we should be able to pass the slicer values directly to CUBERANKEDMEMBER as a set and be done with. And so we can, these formulae

=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_FiscalYear,1),””)

=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_FiscalYear,2),””)


Equation 5

work equally as well as those formulae in Equation 4

Managing The Ingredients

The list of product categories can also be listed in a similar way using CUBESET and CUBERANKEDMEMBER. Here we do need CUBESET as there is no pre-defined set of values as we had with the fiscal year that we can pick up. The set will be all values for the Product Category English name in the Product Category table,

=CUBESET(“PowerPivot Data”,
“Set of Categories”)

Equation 6

As can be seen, .Children gets us all of the category values.

One thing to note is the use of the caption argument. Again, this helps to highlight the cell containing the set.

We now have formulae that can define our full table, such as

D1: the formula in Equation 3
=CUBESET(“PowerPivot Data”,Slicer_FiscalYear,”Set of Years”)

D2: the formula in Equation 6
=CUBESET(“PowerPivot Data”,
“Set of Categories”)

E5:I5: the formulae in Equation 5
=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_FiscalYear,1),””)
=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_FiscalYear,2),””)


D6:D9: formulae for the product categories

=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,$D$2,1),””)
=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,$D$2,2),””)
=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,$D$2,1),””)
=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,$D$2,2),””)

And finally, in E6:I9, the formulae for the values

=IFERROR(CUBEVALUE(“PowerPivot Data”,”[Measures].[Sum of SalesAmount]”,$D6,E$5),””)
etc., each cell reflecting the correct product category ($D6) and fiscal year (E$5).

Our table now has a full set of values, and reflects the choices made in the fiscal year slicer.

(If we wished, we could add the product categories to a slicer, and make our table dynamically reflect that.)

Ready to Serve?

Although we have been diligent in storing the evaluated sets in one place rather than nest a CUBESET function within the CUBERANKEDMEMBER function, but there are still a number of things going on here that I just don’t like:

  • The connection is hard-coded, multiple times
  • If a new year is added to the data, just copying cell I5 to J5 won’t work because the rank is hard-coded in the formula, it will need a small change
  • If no selection is made in the fiscal year slicer, the values shown are the total of all years, with a header value of All, as shown in Figure 4. This may be what is required in some instances, in others we may want to show each year’s values individually.

Figure 4 – Showing all years as a total with no slicer selections

These ‘difficulties’ can be overcome relatively easily.

Rather than hard-code the connection within each formula, put the connection text ‘PowerPivot data’ (without the quotes), in a cell, say D3, and assign an Excel name _cube, then use that defined name within the formulae. Note that this also makes the transition to Excel 2013 simpler, where the connection has now changed to ‘ThisWorkbookDataModel’ (again, without the quotes).

The rank is managed by using a function that returns a variable number depending upon the row or column of the cell, namely ROW([reference]) or COLUMN([reference]). You might think that you can use COLUMN(A1) in E5, and copy that across so that it updates to COLUMN(B1), COLUMN(C1), etc. Believe me, this is a very bad idea. Although everything will be fine at first, what happens if you decide to insert a column before column E? The answer is that COLUMN(A1) will update to COLUMN(B1) and whereas the first column of year table originally reflected the first selected year in the fiscal year slicer, it will now reflect the second. You might say that you would never do that, but no-one ever does until they do. For the sake of a simple change it is hardly worth risking it.

The suggested change is to use COLUMN()-COLUMN($D$5), which uses the top left cell of our table as an anchor point. Thus, a formula in cell E5 using these functions will return 1 for that calculation, and so on. If a column is inserted to the left of the table, those parts of formulae will update to COLUMN()-COLUMN($E$5), which means the formula that was in cell E5 which has now moved to cell F5 still return 1 for that calculation.

Similarly, the category list will use ROW()-ROW($D$5).

Finally, how can we show each year in the column headers and the vales for those years when no slicer selection is made, rather than showing ‘All’ and totals for all years? We already have the formula in D1 that gets the set of selected slicer years, that is

=CUBESET(_cube,Slicer_FiscalYear,”Slicer Years”)

As we showed before, we can get a set of all year regardless of slicer selection with the CUBESET function and the member’s children property, as in

CUBESET(_cube,”[DimDate].[FiscalYear].Children”,”Dimension Years”)

But how do we know when to use which? One way would be to test whether the first member of this slicer set returns All. If it does, there are no slicer selections so we show all years individually, if not we show the slicer selected years. We can check the first slicer set value with the following

IF(CUBERANKEDMEMBER(_cube,Slicer_FiscalYear,1)<>”All”, …

Adding all three elements together, we have the following formula in D1 that determines what goes into the set of years that will drive the table column headings

CUBESET(_cube,Slicer_FiscalYear,”Slicer Years”),
CUBESET(_cube,”[DimDate].[FiscalYear].Children”,”Dimension Years”))

With this formula to get the years set, we can see all the years listed when no selections are made on the ribbon rather than showing all year totals, as in Figure 5.

Figure 5 – Showing all years with no slicer selections

What’s For Dessert?

That’s about it. Using this technique we have a table that shows the value by year by product category, with a slicer for selecting specific years which is reflected in the years shown in the table. The years and product categories are dynamically built and so can accommodate extra years and extra categories in the source data, and the years can also handle a full slicer set without showing the values as totals for all years.

Ribbon On The Fly Part2

Microsoft sneaked a couple of ne


In my last blog post, I described how I thought that I could build an Excel 2007 ribbon on th fly. The technique was founded upon having a ‘worker’ addin that handled the main functionality as well as the version management and ribbon building; and a simpler Excel 2007 ribbon wrapper addin.

This approach would be similar to the myriad of table driven menu solutions around, but the ‘table’ would be a configuration file in this case, to allow the user to control the menu/ribbon.

The ‘worker’ addin would read the configuration file that held the details of the each procedure to be run, and construct the menu or ribbon on the fly. In the case of the ribbon, the customUI XML would be generated, written back to the (as yet) unopened ribbon wrapper addin, then open the ribbon wrapper addin so as to display the changed ribbon in all its pristine glory.

At that point, this was just an idea, albeit an idea that I was confident that it could be implemented. Since then I have implemented it, so it is time to share the details.

Linking the Ribbon Addin to the ‘Worker’ Addin

Having a ‘worker’ addin and a separate ribbon presentation addin does create one problem, namely how dos a button on the ribbon run a procedure in the ‘worker’ addin. This is easily resolved using Application.Run, rather than a simple call to the procedure, as this allows specifying the file as well as the procedure name.

In fact, this allows even greater flexibility when deploying in the ‘real’ world. If we release our super application, with a configuration file, the ‘worker’ addin, and a ribbon addin, a user can add items to the configuration file as long as it runs a procedure already defined in our ‘worker’ addin. But what if they want to run a completely new process, how do we provide the ability to extend the applications overall functionality? We could just open up the addin and tell the user to do add thewir code to the ‘worker’ addin, but is this a good idea? I don’t think this is good, it could break the whole application. A better way is to tell the user that they can build their code in an entirely separate project, and addin, Person.als/xlsm, or whatever they wish, and the include the full file/procedure call in the configuration file procedure column.

Format of Configuration File

It is probably a good point to describe the configuration file at this point.

I have set it up as a standard Excel workbook, in Excel 2003 format for backwards compatibility, and I have a separate tab for each (user) functional ribbon group (or sub-menu for Excel 2003).

I built this technique to service the running of a set of user reports, which are all template files and generally all run the same process, but it could be anything that you want, you would just setup the configuration data, and extract it in the configuration management code.
Figure 1 – Configuration File Group shows a typical layout. If you have any other attributes that you need to capture, just insert extra columns in the first part (in my application, I was importing XML files, so I had to specify the XML table worksheet etc.).

As you can see, some of the columns are relating to the report running, and some relate to the presentation of the ribbon.

Figure 1 – Configuration File Group

I have a column for Report Type, this is so that the client can have the templates in different locations, say company reports on a central server, departmental reports on a departmental server, and the user can have their own report templates locally.

This is all defined on the Client worksheet, Figure 2 – Configuration File Client.

Figure 2 – Configuration File Client

This simply allows the user to provide the client name to appear on the ribbon/menu, a logo file, and the various template directories. Again, if you have a requirement for other client related details, add them here.

The only amendable field here is the client name, the directory values are added by clicking the browse buttons.

I have also added a button to add new groups sheets, so as to keep a consistent format.

Check the Configuration File

Writing the XML back to the ribbon addin is an expensive process, cracking open the zip file, updating the customUI.xml file and then re-writing the file, so I have added a check to ensure this is only done if and when the configuration file is updated.

This is simply achieved with some simple change event code in the configuration file, Figure 3 – Configuration File Change Code, lines 440-450 simply setup a defined name with a value of TRUE. This is checked in the ‘worker’; addin initialise code to determine whether to update the ribbon addin or not.

As you can see, there is a lot of code here for creating a simple name. In addition to this, the code also checks for a duplicate report ID, resetting if a duplicate value is added (this is to avoid compromising the ribbon).

Private mcPrevValue As Variant

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = WS_TEMPLATE Then

End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim mpName As Name
Dim mpSheet As Worksheet
Dim mpDuplicateRepID As Boolean
Dim mpDuplicateGroupID As Boolean
Dim mpLastrow As Long
Dim i As Long

10 On Error GoTo ws_exit

20 Application.EnableEvents = False

30 If Sh.Name = "Client" Then

40 ElseIf Sh.Name = "_template" Then

50 Else

60 If Target.Column = 1 Then

70 mpDuplicateRepID = False
80 For Each mpSheet In Sh.Parent.Worksheets

90 If mpSheet.Name <> WS_CLIENT And mpSheet.Name <> WS_TEMPLATE Then

100 mpLastrow = mpSheet.Cells(mpSheet.Rows.Count, "A").End(xlUp).Row
110 If mpLastrow > 2 Then

120 For i = 3 To mpLastrow

130 If Sh.Name <> mpSheet.Name Or Target.Row <> i Then

140 If mpSheet.Cells(i, "A").Value2 = Target.Value Then

150 mpDuplicateRepID = True
160 Exit For
170 End If
180 End If
190 Next i

200 If mpDuplicateRepID Then Exit For
210 End If
220 End If
230 Next mpSheet

240 If mpDuplicateRepID Then
250 ShowMessage Replace(MSG_ERROR_DUPLICATE_REPORT, _
"", Target.Value), vbOKOnly + vbExclamation
260 Target.Value = mcPrevValue
270 End If
280 ElseIf Not Intersect(Sh.Range(NAME_GROUP_ID), Target) Is Nothing Then

290 mpDuplicateGroupID = False
300 For Each mpSheet In Sh.Parent.Worksheets

310 If Sh.Name <> WS_CLIENT And Sh.Name <> WS_TEMPLATE And Sh.Name <> mpSheet.Name Then

320 If mpSheet.Range(NAME_GROUP_ID).Value2 = Target.Value Then

330 mpDuplicateGroupID = True
340 Exit For
350 End If
360 End If
370 Next mpSheet

380 If mpDuplicateGroupID Then
390 ShowMessage Replace(MSG_ERROR_DUPLICATE_GROUP, _
"", Target.Value), vbOKOnly + vbExclamation
400 Target.Value = mcPrevValue
410 End If
420 End If
430 End If

440 Set mpName = ThisWorkbook.Names.Add(Name:="_Changed", RefersTo:="=TRUE")
450 mpName.Visible = False

460 mcPrevValue = Target.Value
470 Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
mcPrevValue = Target.Value
End Sub

Figure 3 – Configuration File Change Code

Testing Excel Version

Determining whether to build a menu or a ribbon is very straight-forward, all we need to do is to check the application version, as shown in Figure 4 – Test Excel Version. There is nothing particularly trick here, but there are a few call to other procedures that will be covered in the next post, namely

  • AppInitialise – opens the configuration file, does some basic validation of the template paths, and then grabs all of the report details
  • OpenRibbonAddin – this checks if the configuration file has been changed since the last run, builds the XML, and writes it back to the ribbon addin.

There is one other procedure call, BuildMenus, which I will not go into, it is a standard commandbar menu builder.

Private Const mmModule As String = "ThisWorkbook"	

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Val(Application.Version) < 12 Then

Call DeleteMenus

End If
End Sub

Private Sub Workbook_Open()
Const mpProcedure As String = "Workbook_Open"

On Error GoTo Workbook_Open_Error
PushProcedureStack mpProcedure, True

Call AppInitialise
If Val(Application.Version) < 12 Then

Call BuildMenus
mgConfigWB.Close SaveChanges:=False
Set mgConfigWB = Nothing


Call OpenRibbonAddin
End If


Application.DisplayAlerts = True
If Not mgConfigWB Is Nothing Then mgConfigWB.Close SaveChanges:=False
Set mgConfigWB = Nothing
Exit Sub

If Err.Number = AppBypassErrorNum Then Resume Workbook_Open_Tidy
If AppErrorHandler(mmModule, mpProcedure, True) Then
Resume Workbook_Open_Exit
End If
End Sub

Figure 4 – Test Excel Version

Next Post

In the next post, I will give details on the getting the configuration file details, building the ribbon XML, and using the addin.

Ribbon on the Fly, or the Zip

I recently needed to build an Excel addin that supported a variable number of options, with buttons to invoke the options. That in itself is not unusual, but I also wanted the numbers to be driven by the user; which always complicates matters.

The definition of the buttons is simple, even allowing for the fact that it will be maintained by users. I could use a separate configuration workbook, an INI file, or even XML. As I am looking to make this a user driven facility, within an overall Excel framework, an Excel workbook seems the best option.

For the addin, in Excel 2003, we have a simple solution, namely read the workbook and build a menu of options dynamically on startup. As seems to be the norm, it is not so simple for Excel 2007 and 2010. I could just build commandbars as for Excel 2003 and let them show in the Addins tab, but this is a very poor solution in my view. I showed in a previous post last year how we can manage addins in both Excel 2003 and later versions, and support menus or the ribbon, and it was my intention to use this technique here. This does not cater for the variable options in the ribbon however.

There couple of obvious ways to manage the variable options in the ribbon. One would be to use a dynamic menu control, which can be dynamically populated when the menu is invoked. This is a nice option for lists, such as file lists, but I don’t like it as the only option in my ribbons, it does not seem to fit the ribbon paradigm. Another way would be to define a fixed number of buttons, make the visibility of the buttons a dynamic setting, and use the values in the configuration workbook to determine which are made not visible. Again, this is not an ideal solution in my view, it is almost guaranteed that not enough will be defined.

As I mentioned above, I already have a technique for supporting menus and the ribbon in my addins, and this consists of an Excel 2003 addin which is the primary component that checks the version, builds commandbar menus if the Excel version is less than 12 (Excel 2003 or earlier), otherwise it opens a ribbon wrapper addin. All of the grunt work is done in the Excel 2003 addin, and these procedures are called from the ribbon wrapper addin. The key here for Excel 2007 and on is that the ribbon addin is not initially opened. As Excel 2007 is a zip file with various components, including a CustomUI XML component, it is possible to open this zip file and overwrite the CustomUI XML. So, before I need to open the ribbon addin, I can dynamically update the XML, and I have a fully dynamic ribbon.

Of course, there is still the issue of calling the correct procedure regardless of whether the addin is loaded in Excel 2003 or a ribbon enabled Excel. The issue arise in the ribbon wrapper, the button actions need to call procedures in the Excel 2003 addin, and in my technique I do this using Application.Run calls across projects. Because all of the code in my required addin will perform the same action, just on differing files which are defined in the configuration file, this works fine, but the configuration file could always be extended to provide the name of the project and procedure to be called.

So much or the theory, just need to get on and create it now.

Does Excel Do MDX?

One of my primary interests at present is Business Intelligence (BI), using available tools to present meaningful information to the business that actually adds value.

I won’t go into what BI is, the quality of BI products available at present, or the how businesses perceive or fails to perceive BI, that may be a topic for another day, but rather in developing my skills and capabilities in this area. Excel is a wonderful medium for presenting this information, and the majority users that I know would rather use Excel than other tools.

Most people who are familiar with BI will have heard of OLAP cubes, a technology that allows manipulating and analyzing data from multiple perspectives, and provides fast analysis of data in a way that a relational database just cannot match. In Excel, it is very simple to build a pivot table that can plug into an OLAP cube, allowing retrieval of large, complex of data sets easily and quickly.

I have built many applications that utilise pivot tables against cubes, and have also started to build more analyses using the newly added cube functions in Excel. The basic format of a typical cube function, say CUBEVALUE, is

CUBEVALUE(connection, member_expression1,member_expression2…)

with an example formula being

=CUBEVALUE(“MDX Step-By-Step Chapter 3 Cube”,”[Product].[Category].[Bikes]”,”[Measures].[Reseller Sales Amount]”)

where a member_expression, such as “[Product].[Category].[Bikes]”,”[Measures].[Reseller Sales Amount]”)is a text string of a multidimensional expression (MDX) that evaluates to a member or tuple within the cube. This is not the most intuitive syntax I have come across. There are various ways to find these tuples, which we will not go into here but maybe a subject for a later post, but I felt it would be a good idea to get better acquainted with MDX. By getting a better understanding of MDX I fell I would be able to query cubes more effectively, from within SQL Server 2008 Management Studio (SMS), or even within Excel.

To this end, I bought myself SQL Server 2008 MDX Step by Step, a book from Microsoft Press, and the following is my view of this book (yes, I did pay for this book, it is not a free review copy :)).

One thing to explain here, especially for those of you who know this primarily as an Excel blog, is that this book is primarily aimed at developers working in Analysis Services and makes no mention of Excel that I can recall, and all the examples are shown within SMS.

Before I make any specific comments, I will state that I had used MDX prior to reading this book, but felt a bit at sea. It may that as this was my first book on the subject that it found me as a soft target, but I liked the book, and felt it was just about worth my outlay. The comments below should be taken with this overall view in mind.

The book starts gently enough, guiding you through an overview of BI and MDX, introducing the MDX Query Editor in SMS, and explaining the concept of tuple.

Chapter 3 was the first where any real MDX code was deployed, using a simplified cube in order to focus on the points being made. As well as tuples, it introduced attribute and user hierarchies, which are key cube fundamentals to understand.

Chapter 4 was the first where the material became more than just simple explanation and more detailed concepts were introduced. I think that in too many places the authors did a less than satisfactory job of explaining what was going was going on; too often things were stated without an explanation as to why, and the rationale for techniques was not explained. This is especially important in this chapter, the first where we meet these less obvious concepts and where that clear explanation is vital. For example,

– on p67 the Distinct function is introduced, and explains how it can be used to remove any duplicate rows (or columns) that the query might generate. The thought occurred to me that maybe you just add that to every set statement to make sure there are no duplicates, but of course I am sure it comes at a high cost in performance. Nothing was mentioned as to the pros and cons. Just stating what the function does should not be the objective of a book in my view, that is what help is or, it should look to guide you through when and where to use them, best practices, and so on

– when discussing MeasureGroupMeasures in chapter 4, most of the rows returned currency or quantity formatted cells, one returned non-formatted cells, but there was no explanation as to why. Figure 1 below shows the query in question, and Figure 2 shows the results with the cells in question highlighted.

Figure 1

Figure 2

The answer seems to be due to how the cells within the cube space are designed, the format of the cells is given here. By adding some code to the query to get the cell properties

Figure 3

and double-clicking one of the non-formatted cells we can see that the format is null

Figure 4

This is covered as a topic earlier in the book, but it could have been reiterated here in my view, to clarify this seeming anomaly, and to empathise that earlier description.

Quibbles aside, Chapter 4 introduced these key concepts in a logical fashion, and generally did a good job explaining them.

Expressions were introduced in Chapter 5, the ability to add calculated members to the already powerful cube. As the authors state, this adds a whole new dimension (no pun intended) to the cube, and they rightly give it a thorough explanation, 30 pages on a single topic as against the 31 pages of Chapter 4 which covers many topics within sets.

I won’t cover the rest of the chapters here, Chapters 1- 5 covers Part I of the book, MDX Fundamentals. Parts II and III cover MDX Functions and MDX Applications. I will leave you to explore these yourself if you are minded to buy this book, suffice to say that I found them informative, relevant, and appropriate. They are less obviously helpful to me from an Excel perspective, but will help me as I work deeper with cubes in the future.

One thing that I think is difficult, and this is not aimed at this book but is a comment about cubes generally, is that it is very difficult to envisage the data, and see more than what the query returns (without creating other queries) – n-dimensions is just too hard. But we have another excellent tool available to help us, one that I made extensive use of whilst reading this book; that is our good friend Excel. Using Excel and pivot tables I was able to get a much more familiar peek into the cube, one that allowed me to double-check what the authors were saying, and also to give me wider context that helped me extend their quoted queries to delve even deeper.

Giving just one example of what I mean by this, in Chapter 4 there is discussion on querying the members of a hierarchy or hierarchy level. One of the key concepts covered was the difference in querying the members of an attribute hierarchy at the leaf level as against all levels. The query of all levels is shown in Figure 5 below

Figure 5

which returns the data set shown in Figure 6.

Figure 6

This is difficult, at least to me, because there are various levels of data here. ‘Bike Wash – Dissolver’ is a product within the ‘Cleaners’ sub-category, which is within the ‘Accessories’ category, no clear demarcation in the dataset as returned. Figure 7 shows the same query results with some manual highlighting of the groups

Figure 7

By creating a simple pivot table in Excel, Figure 8, I was able to view this same data in a much more readable manner, which helped me to understand the points being made.

Figure 8

Here you can see that Helmets is a sub-category of Accessories very clearly, and of course you have the filtering options (which I used to restrict the calendar dates as per the set SELECTion in the MDX, and geography to the United States as in the MDX WHERE clause), and grouping.

I found creating simple pivots extremely useful in working through the examples.


In summary, I found this book well structured, and that it gave me a good understanding of MDX and how to use that within SQL Server 2008 Management Studio. As explained above, it does not explicitly cover using MDX within Excel or VBA, two areas of interest to me, but I gained more than enough information to be able to apply that to my existing Excel and VBA knowledge, and feel far better equipped having read this book.

Formulas Made Easy

I have been playing with PowerPivot recently, and one of the sites I have visited is Rob Collie’s PowerPivotPro blog, One particular post that caught my eye was a guest post by Colin Banfield, on building complex DAX formulas in NotePad++. As Colin noted, the PowerPivot formula editor is severely limited, a better option is required when working with more complex DAX formulae. As Colin suggested, NotePad++ can provide this better option; with code folding, keyword highlighting, and Intellisense. In his post, Colin showed how to extend NotePad++ to provide this functionality for DAX formulas.

Being primarily an Excel guy, I couldn’t help but empathise, I feel exactly the same about creating complex Excel formulas. Sure, it is possible to break formulas over several lines by inserting Alt-Enter into the string, and Excel 2007 and 2010 has improved matters somewhat with the resizable formula bar, but these are tweaks around the edges, the core problem remains (the resizable formula bar is a good idea in concept, but in practice it takes up far too much real-estate, which when added to the space gobbler that is the ribbon, makes it far from ideal).

So, inspired by Colin’s post, I set about to extend NotePad++ to support Excel formulas. Believe me, with the huge number of functions, creating tooltips and argument lists, this was no trivial task. I was amazed at the number of functions I hadn’t come across before, besides those that I never use.

Assuming you have NotePad++ to hand, this is how to extend NotePad++ for Excel. This duplicates much of what Colin has written, but it is worthwhile repeating for clarity.

Notepad++ saves language definitions in an XML User Defined Language file (UDL), you can start the XML User Defined Language editor from the menu (View -> User Define Dialog…), or from the toolbar button.

The first thing to do is to give your UDL an identifier, I call mine XLF here, and to specify the extension of any files to use this UDL, again mine is xlf.

The first tab allows setting up the folder options. An Excel formula has no obvious fold points, so I have invented two keywords, BBEGIN and BEND, that  I embed in the formula simply to provide the fold points. Clearly, these need to be removed before copy-pasting the formula back to Excel. Figure 1 shows my folder tab settings.

Figure 1.

The second tab is used to define the keywords, and the highlight colour. Figure 2 shows my Keyword settings. You can see that even though I have used all four keyword sets, they have the same attributes. This is because I used an Excel spreadsheet to generate this data (surprise!), and I grouped them in standard functions, 2007 new functions, 2010 new functions, and cube functions.  

Figure 2.

Next, we have in Figure 3 the tab for defining comments. Unfortunately, an Excel formula doesn’t support comments, so I use this just to add some preliminary comments if I want to save the formula in a file.

Figure 3.

Finally, figure 4 shows the operator settings.

Figure 4.

So far we have defined the UDL for our particular edit type, and when XLF is selected from the Language menu, any input will be formatted according to these settings. Figure 5. Shows a ‘typical’ Excel formula created in Notepad++.

Figure 5.

Where it gets really good is by adding intellisense, description and argument tooltips. To do this, you have to create an XML file with the same name as your chosen language id, that is XLF.xml in this case. This file is stored in the NotePad++ file directory, in Notepad++\plugins\APIs folder. The format of a typical function is shown in Figure 6.

<KeyWord name="AVERAGEIFS" func="yes">
  <Overload retVal="number" descr="Returns the average (arithmetic mean) for the cells specified by a given set of conditions or criteria. Excel 2007 and later">
    <Param name="average_range"/>
    <Param name="criteria_range1"/>
    <Param name="criteria1"/>
    <Param name="[criteria_range2]"/>
    <Param name="[criteria2]"/>
    <Param name="…"/>

Figure 6.

After creating all of the xml code (auto-generated in Excel for me), and building the XLF.XML file, when inserting a function in NotePad++ you get the following assistance, as shown in figures 7 and 8.


Figure 7.

Figure 8.


Notepad++ has to be enabled to allow auto-complete and function tips. To do this, go to menu Settings>Preferences>Backup/Auto-Completion and check the Enable auto-completion and Function Parameters hint boxes, as shown in Figure 9. 

Figure 9.

To save you the chore of recreating all of this data, I have provided a zip file containing the XLF.xml file, and the exported UDL data. The XML file has to be stored in the Notepad++\plugins\APIs folder, and the UDL file can be imported within the UDL.

Who Scratched My Table?

One of the better additions to Excel 2007 are, in my view, the functionality added to tables

I was recently working on a 2007 project and I was looking to add some highlighting using conditional formatting. That will be easy thought I, but as usual, I was way off the mark.

Formatting The Table

For the sake of clarity, I will not use the actual formatting that I was trying, but a far simpler case.

Figure 1 shows an example of a typical table in Excel 2007.

Figure 1

Let’s assume that I want to show those rows where the Ship Date has not yet been set, i.e. are showing TBA.

Using conditional formatting, I can easily do that by selecting B2:E8, and then adding a formula of


and adding an appropriate fill colour.

To demonstrate this, Figure 2 shows the formatted table.

Figure 2

Using Structured References

But hey, this data is in a table, and one of the great features of tables is structured references. So I decided that I would use structured references in my conditional formatting.

As a demonstration of  structured references, Figure 3 shows the table with my CF formula off-table.

Figure 3

As you can see, you reference the table, names tblShipping here, and the column, not each individual cell.

So clearly, I just have to replace the first CF formula with cell references with this new formula with structured references, Figure 4.

That is what I did, and to my utter surprise, I got the following error.

Figure 4

As an aside, I did click Help, but I won’t bore you with the details on the lack of help that provided, just suffice to say that it was to Excel 2007’s usual standard.

But what can the error be? There is nothing wrong with the formula as Figure 3 shows.

One thing did occur to me. My testing formula was not part of the table, it was outwith the table, whereas CF is part of the table (perhaps, who knows?). If you add that formula to the column next to the table, the table expands to encompass that column, Figure 5.

Figure 5

Although I used exactly the same formula as before, when the table incorporated the column with that formula, it dropped the table reference, clearly it is superfluous, or implicit.

Armed with all of this, I decided to try that version of the formula in my CF. I still got an error, but a different error

Figure 6


This suggests to me that and formatting added by conditional formatting is not ‘part of the table’. And because it is part of the table, it does not work, you cannot use structured references in CF.

I tried a few variations of the formula, mainly in desperation, but none of these worked either. One interesting aside, when I tried to use a formula with an explicit intersection within the formula embedded in the table, it didn’t strip off the table name, I had to use

tblShipping[[#This Row],[Ship Date]])=”TBA”

Yet another oddity.


I have written before about the implementation of conditional formatting in Excel 2007, in Conditional Formatting In Excel 2007 – Chav or Mini?, and  Open Office Is Looking Good. From these posts, you should be clear that I am far from impressed with conditional formatting in Excel 2007, and unfortunately this looks like another piece of poor implementation.

It seems that you can conditionally format a table in Excel 2007, but you cannot use structured references in that CF. Okay, so you can use normal CF, but big deal! Why can’t we use structured references when applying CF to a table? To my mind, not being able to is a total nonsense, another CF car crash piling up on the others.

I did also try and use a structured reference in CF on cells not in the table, still no joy.

If anyone knows how structured reference can be, should be, used, I would love to hear it.


The Kid Grows Up

In a previous post, I talked about building stored procedures (SPs)in an Access database, and calling the same from Excel using ADO.

As I mentioned in that post, I am not a fan of the Access GUI. Whilst GUIs can be okay for doing some simple testing, checking whether something works or, I find it far easier to build a script when I need to do similar things over and over (such as building all of the SPs for an application). I am an inveterate scripter (see Autogen’ed Ribbon Code and XML Is Such A Pain); rather than build the stored procedures using the Access GUI, I much prefer to build a script file that can be rerun at any time. This is very much in line with my preferences to autogen as much as possible, and also with me development methodology, where I prefer to allocate design time before ploughing into the functional code.

In the post mentioned above, I said that … you can remove all of the inline SQL from your applications, create a separate SP creator app that creates the SPs, have better structured code, and more maintainable. This post will cover such a creator app.

In this app, I have a script file that defines all of the SPs, and the application just reads that file and builds the SPs defined therein. I have used an INI file as my SP definition file; I like the flexibility of INI files, the format does not have to be too rigid, and they are easily segmented, and easily read (via code).

The format of my file is as follows

 3   [spb_DB]
 5       DBType=Access ;could be SQL Server or any other DB
 6       DBPath=C:\MyDatabases\
 7       DBName=MyDB.mdb
 9   [spb_Stored_Procs]
11       TypeCount=7
13       I001=Get
14       I002=Check
15       I003=Insert
16       I004=List
17       I005=Query
18       I006=Table
19       I007=Update
20       I008=Delete
23; Get Stored Procedures
25       [spb_SP_Get]
27           SPCount=7
30           [spb_SP_Get_001]
32               SPName=spGetCompanyGoals
34               [spb_SP_Get_001_Parameters]
36                   ParameterCount=1
38                  [spb_SP_Get_003_Parameters_001]
40                      ParamName=prmLoginID
41                      ParamDataType=VarChar
43               [spb_SP_Get_001_SQL]
45                   SQLLineCount=4
47                   Line001=SELECT
SUM(SalesGoal) AS ‘Company Sales Goal’,
48                   Line002=      SUM(BonusGoal) AS ‘Company Bonus Goal’
49                   Line003=FROM
50                   Line004=WHERE LoginID
= prmLoginId;


Line 1 isn’t actually used, it is just for completeness.

Lines 3-7 define the database, the type and location. Note that the type is to allow for building SPs in different databases, although we will just discuss Access.

Lines 9-20 define the SP categories, I do this so as to break up the SPs and keep them grouped, for easier maintenance. The Type (Get, Check, etc.) is used as part of the section id for the SP details, as in lines 20, 30, 34, and 38.

Line 27 defines how many SPs are in that group, used as a loop index in the code.

Line 32 is the SP name, used in the code to Drop the SP then Create it anew.

Lines 34-41 defines the parameters. As you can see, there is a ParameterCount specifying how many parameters the SP uses. A definition for each parameter, if applicable, follows, with an incrementing suffix index so that the app can extract each in turn.

Lines 43-50 define the SP code, with a SQLLineCount defining how many lines of SQL are within the SP. In the example above, the SP is very simple, but of course SPs of any complexity can be built.

Lines 29-50 are repeated for each SP within that category.

Lines 22-50 are repeated for each category of SPs.

The category names are not relevant, it can be any name and any number, as long as the sections match up.


Care has to be taken that the definitions are consistent, the category id is correct, the SP index is carried through, the parameter name in the parameter definition is the same as the parameter in the SQL code., and so on.


The SP Builder addin can be downloaded from here. It is unprotected, so the code can be examined, updated as you see fit.


I use this technique for all of my databases, so I have a script file for each, and can easily recreate the database code. As I mentioned, by creating a script file it helps in better design, thinking about the code required rather than diving into the GUI and building as required.
This technique could be extended to creating the database, building the tables etc. I have a separate app for this, but have not combined them as I find myself creating the SPs far more often than the database, I find it more convenient to keep as separate applications.

You Say CustomUI Editor, I Say TextPad


I have written before about hand-crafting XML so as to
customise an Office 2007 ribbon at XML
Is Such A Pain
and Autogen’ed
Ribbon Code


As I have mentioned before, the CustomUI editor is far from
perfect. It does allow adding of  image
files and to validate the XML, but the editing experience is absolutely awful.


My favourite editor is TextPad,
and one of the useful features of TextPad is the facility to incorporate syntax
files that are file type dependent, for instance a Visual Basic syntax file for
.bas and .vbs file types. The syntax files can highlight elements of your files
which assist in  entering script as well
as making the script more readable.


Although I rarely use the CustomUI editor when I am developing
Excel ribbons these days as I autogen everything, my autogen doesn’t yet handle
Word or Access ribbons. As I still don’t want to use the CustomUI editor, I
built myself a CustomUI syntax file for use with TextPad. I can now edit my XML
in TextPad, and copy and paste into the CustomUI editor when it is ready. I
find this much more productive.


It is still necessary to setup TextPad with a document class
for these files, setup the keyword colours and so on, but if you think it could
be useful, you can get it a copy via the RSS feed, or download it here.