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:

 

WITH 
    MEMBER measures.ptd AS 
        SUM(
            PERIODSTODATE([Date].[Calendar].[Month]
                         ,[Date].[Calendar].CurrentMember
                         )
            ,[Measures].[Sales Amount] 
            )
        ,FORMAT_STRING = "currency"
SELECT   
    {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

Comments on this post

# re: A Rose by any other name - MDX Formatting

Requesting Gravatar...
"I tend to find that the nesting of functions in MDX often requires decent layout in order to make it readable"

Sounds like this is the time for MDX beautifier. Will you take the challenge?
Left by Miky Schreiber on Jun 16, 2008 7:43 AM

# re: A Rose by any other name - MDX Formatting

Requesting Gravatar...
I took the challenge :)
http://sqlblog.com/blogs/mosha/archive/2008/06/16/mdx-formatting-with-mdx-studio.aspx
Left by Mosha on Jun 17, 2008 9:37 AM

# re: A Rose by any other name - MDX Formatting

Requesting Gravatar...
Hey,

In case you hadn't noticed, the latest version of MDX Studio has a "format MDX" command.
Left by Chris Harrington on Jun 25, 2008 8:33 AM

Your comment:

 (will show your gravatar)