THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

MDX Formatting with MDX Studio

Darren Gosbell put up a blog post about MDX Formatting, where he describes how he prefers to format MDX to look nice on the screen. So I thought – why to leave to a human something that machine can do. I decided to implement these rules (with some modifications) into MDX Studio. It became MDX Studio 0.2.8 release, available immediately for download at

I only had couple of hours to work on it, so it is rough on the edges, and I will appreciate comments in the MDX Studio forum at But basic staff like calculated members, named sets, subselects etc should work. (One known exception is set aliases).

The functionality is accessible through MDX –> Format menu. Here is example from Darren’s blog

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])

And here is how it looks after autoformatting:

  MEMBER measures.ptd AS 
      [Measures].[Sales Amount]) 
    , format_string = "currency"
    [Measures].[Measures].[Sales Amount],
  } ON 0,
  [Date].[Calendar].[Month].&[2003]&[2].children ON 1
FROM [Adventure Works]
  [Product].[Product Categories].[Category].&[1]
Published Monday, June 16, 2008 5:20 PM by mosha
Filed under:
Anonymous comments are disabled
Privacy Statement