THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 http://cid-74f04d1ea28ece4e.skydrive.live.com/browse.aspx/MDXStudio/v0.2.8

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 http://www.ssas-info.com/forum/MDXStudio. 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:

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]
Published Monday, June 16, 2008 5:20 PM by mosha
Filed under:
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement