A Rose by any other name - MDX Formatting

A little while ago Jamie posted about how he formats SQL code which happens to be quite similar to the way I do it. But this then lead me to think about the equivalent formatting for MDX.

So if I get MDX like the following:

with member measures.ptd as 'sum(periodstodate([Date].[Calendar].[Month],
[Date].[Calendar].currentmember),[Measures].[Sales Amount] )',format_string = "currency"
select   {[Measures].[Measures].[Sales Amount] ,measures.ptd} on 0,
{[Date].[Calendar].[Month].&[2003]&[2].children} on 1 from [Adventure Works] where 
([Product].[Product Categories].[Category].&[1])


I will tend to change it to something like this:


    MEMBER measures.ptd AS 
            ,[Measures].[Sales Amount] 
        ,FORMAT_STRING = "currency"
    {Measures.[Sales Amount] 
    ,Measures.ptd} ON 0,
    {[Date].[Calendar].[Month].&[2003]&[2].children} ON 1 
FROM [Adventure Works]
WHERE ([Product].[Product Categories].[Category].&[1])


When formatting MDX I apply the following formatting guidelines:

  • All keywords are in uppercase
  • The main Keywords WITH, SELECT, FROM and WHERE are the only ones on the left margin, each at the start of the line
  • Each inline MEMBER or SET definition is on a new line and is indented and the expression for those members or sets is indented under the MEMBER/SET line
  • Member and Set definitions are never quoted as strings (unless you are still working in AS2000 where you have no choice in this matter)
  • Each axis is starts on a new line and if I have multiple members or expressions on an axis I will indent them
  • I never mix axis numbers and axis names, if I use ON COLUMNS, I will use ON ROWS, if I have used ON 0 (for the column axis) I will use ON 1 (for the rows).
  • I usually put commas at the start of the line (after indenting) not at the end

I often uppercase functions although I will also use Camel case as the mood strikes me, I find multi word functions like PeriodsToDate() can be a little easier to read in Camel case. And for some reason I prefer to put the comma that separates axis at the end of the line which is opposition to my guideline where I put all other commas at the start of the line. I don't have a good justification for why I do this, it's just the way I do it. :)

Interestingly I am probably not quite as strict with my MDX layout as I am with my SQL. I tend to find that the nesting of functions in MDX often requires decent layout in order to make it readable, but have not really come up with a set of rules that I am 100% happy with. I tend to find that I make compromises between line length and the number of lines.

So, how do you format your MDX?

Technorati Tags: ,

Print | posted on Sunday, June 15, 2008 10:52 PM