I recently published an article on MSSQLTips.com titled Format Durations in SQL Server Analysis Services. For those who haven't read it I'll summarize it quickly: it explains how to create an MDX formula that formats durations into an [hh]:mm:ss format, where [hh] can go over 24 hours. For more detailed information, please check out the article itself.
An example:
[
][1]
However, I recently found out at a project that the formula doesn't really deal well with null values. For instance, when I browse the sample data I get the following result:
[
][2]
The returned data set contains (null) values for the Unknown member, although empty cells should not be included. If I browse the same set with the unformatted measure, the empty cells are not returned so it the issue is caused with the formatting formula.
[
][3]
My educated guess is that SSAS doesn't know upfront if the result of the formula will be null or not, so it calculated the measure anyway and displays everything on the grid. The solution is to add an extra check for null values into the formula.
CREATE MEMBER CURRENTCUBE.[Measures].[DurationFormatted]
AS Iif(IsEmpty([Measures].[Duration])
,null
, Cstr((Int([Measures].[Duration]) * 24)
+ CInt(FORMAT(CDate([Measures].[Duration]), "HH")) )
+ FORMAT(CDate([Measures].[Duration]), ":mm:ss")
)
,VISIBLE = 1
,ASSOCIATED_MEASURE_GROUP = 'Customer Service';
```<p style="text-align: justify">
When browsing the cube now, the Unknown member is now ignored due to the explicit check for <em>null</em> values.
</p>
[<img class="alignnone size-medium wp-image-3128" src="https://lessthandot.z19.web.core.windows.net/wp-content/uploads/2014/12/IssueFixed-300x296.jpg" alt="IssueFixed" width="300" height="296" srcset="https://lessthandot.z19.web.core.windows.net/wp-content/uploads/2014/12/IssueFixed-300x296.jpg 300w, https://lessthandot.z19.web.core.windows.net/wp-content/uploads/2014/12/IssueFixed.jpg 396w" sizes="(max-width: 300px) 100vw, 300px" />][4]
[1]: https://lessthandot.z19.web.core.windows.net/wp-content/uploads/2014/12/FormattedDurations.jpg
[2]: https://lessthandot.z19.web.core.windows.net/wp-content/uploads/2014/12/NullProblem.jpg
[3]: https://lessthandot.z19.web.core.windows.net/wp-content/uploads/2014/12/Unformatted.jpg
[4]: https://lessthandot.z19.web.core.windows.net/wp-content/uploads/2014/12/IssueFixed.jpg
