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?