Using Cube Functions in Excel to Create MTD

Cube Functions inside Excel are really powerful tools for cube reporting, especially when you realize that you can leverage MDX within the functions to make some very useful calculations. Today, I will describe how you can use the CUBESET function to calculate Month To Date values, even when there are no MTD defined measures.

First, let’s review the syntax of the CUBESET function:

CUBESET(connection,set_expression,caption,sort_order,sort_by)

The bolded inputs are required, while the other are optional. The Connection input is the name of the cube connection in excel and the Set Expression is a string “that results in a set of members or tuples.” (You can read more about the syntax here.)

We’re most concerned with the Set Expression. Typically, this can refer to a range of cells in Excel that themselves are CUBEMEMBER functions. But the less obvious choice here is to write out the Set Expression in MDX. This opens a whole world of opportunities, such as:

[Item].[Item Numbers].children

Which would return a set of all the item numbers. That, combined with the sorting options defined and the CUBERANKEDMEMBER function, can be used to return a list of you Top 10 most popular items.

But we’re here to talk about is tricking Excel into making a MTD calculation. The cubes do their aggregations, a measure for a single will include the entire month. If you’re trying to look at a list of Sales Orders that were supposed to ship this month, the current month might be misleading in that the aggregations will include orders that are going to ship later this month as well. Thankfully, selecting a range of dates in MDX is as simple as using a semi-colon, like:

[Start Date]:[End Date]

The semi-colon would be read as “to” in MDX. To define a date range in the AX cube, your Set Expression would look like this:

[Transaction date].[Date].[All].[Friday, November 01, 2013]:[Transaction date].[Date].[All].[Friday, November 08, 2013]

Since dates are a little different in the GP world, the same range would be expressed as:

[Master Date].[Date].[All].[2013].[Q4].[November].[01 Nov 2013]:[Master Date].[Date].[All].[2013].[Q4].[November].[08 Nov 2013]

Combining this syntax with other Excel functions (like TODAY() and DATE()), it becomes very easy to create a set that represents only days up to the date you desire.