SSAS: Custom format string for “duration” using MDX

I recently struggled with the following challenge in Analysis Services:
In one of the fact tables there was a field duration which actually was a duration of a certain status in seconds. I needed to display this in a readable format, like “2 days 22:15:59″.

bing brought me to the following great article, where Mosha explains how you can use MDX to create a custom format:
http://sqlblog.com/blogs/mosha/archive/2008/09/26/displaying-duration-values-mdx-expressions-in-format-string.aspx

When you apply this for a calculated member you get something like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[DurationInDays]
     AS [DurationInDays],
    FORMAT_STRING =  
        IIF([Measures].[Duration] < 1
         ,’”0 days” hh:mm:ss’
         ,’”‘ + cstr(int([Measures].[Duration])) + ‘ days” hh:mm:ss’)
    ,
    VISIBLE = 1;

But this brings us to another problem:
This custom format will only display the correct result if [Measures].[Duration] contains decimal seconds.
For more information about the conversion from seconds to decimal seconds, read the following article on wikipedia: http://en.wikipedia.org/wiki/Decimal_time
In short, you need to divide the number of seconds by 86.400. The resulting number presents the number of days on the left side of the decimal separator and the remaining decimal seconds on the right side.

So the final result will be something like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[DurationInDecimalSeconds]
     AS
        [Measures].[DurationInSeconds] / 86400,
    VISIBLE = 0;  

    CREATE MEMBER CURRENTCUBE.[Measures].[DurationInDays]
     AS [Measures].[DurationInDecimalSeconds],
    FORMAT_STRING =  
        IIF([Measures].[DurationInDecimalSeconds] < 1
         ,’”0 days” hh:mm:ss’
         ,’”‘ + cstr(int([Measures].[DurationInDecimalSeconds])) + ‘ days” hh:mm:ss’)
    ,
    VISIBLE = 1;

If you want this custom format to be displayed in Excel, make sure the connection properties are set correctly to retrieve the number format from the server:

Go to Connection Properties, tab Usage, and check Number Format in the OLAP Server Formatting options.

image 

There is one more thing I would like to tell you about these options: Normally all these options will always be checked by default, but I’ve noticed that when you start from an existing odc-file, this is not the case. So, when you’re used to deliver an odc-file to your users to start analyzing from Excel, you will have to replace this odc-file by an Excel template that connects to the cube, to make sure this option is always on.

There might be a better way to change this behavior for odc-files, but I couldn’t find any. If you know it, please let me know ;)

 

Enjoy!

Share
  1. Nathan Griffiths
    Apr 8th, 2010 at 04:26 | #1

    The OLAP Server formatting settings are unfortunately stored with the spreadsheet, not the ODC connection file. This means that when opening a spreadsheet from a connection file you have to enable these settings each time.
    However, if you open a blank spreadsheet and then use the Get External Data.. method of connecting to Analysis Services then the above settings are enabled by default.

  2. Mar 16th, 2011 at 21:23 | #2

    Here are this and some other articles on Measure Formatting:

    http://ssas-wiki.com/w/Articles#Measure_Formatting

  3. Bonne
    Jul 22nd, 2011 at 14:38 | #3

    Thanks this was very helpfull.
    I realized that you could simplify the code somewhat with the same result.

    I now use:
    CREATE MEMBER CURRENTCUBE.[MEASURES].[DurationInDays]
    AS [MEASURES].[DurationInSec] / 86400,
    FORMAT_STRING =
    ‘”‘+ cstr(int([MEASURES].[DurationInDays])) + ‘ days” hh:mm:ss’,
    VISIBLE = 1;

  4. May 1st, 2012 at 20:56 | #4

    The ODC bug where the Number Format checkbox doesn’t default to checked has been fixed in hotfixes:
    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/0671fd2e-cd46-4262-afa4-1d01459bd358/

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>