Home      Functions     Common Solutions      Contact     






MDX Functions:
 
MDX Functions

 
Common Solutions

Common Solutions
 
Resource Links

MDX Books
MDX Related Software
MDX Related Blogs
 
Other Links

Contact Us
 
Microsoft SQL Server Analysis Services Function Page
Function Name: CoalesceEmpty
Category: Statistical
Description: The CoalesceEmpty function converts an empty value to a supplied nonempty string or number.
Syntax: COALESCEEMPTY( «Numeric Expression»[, «Numeric Expression»...] )
Technet Link: MDX CoalesceEmpty Function




The query below returns the string NoValue where Sales Amount is empty.
 
WITH
      MEMBER [Measures].[Test] AS
      COALESCEEMPTY(([Measures].[Sales Amount]), "NoValue")
SELECT
      {[Measures].[Test]} ON COLUMNS,
      {[Date].[Fiscal].[Month].Members} ON ROWS
FROM
      [Adventure Works]
 
Notice the member August 2004 has a value of NoValue for the calculated member Test.
 

 
The query below returns the number 999999 where Sales Amount is empty.
 
WITH
      MEMBER [Measures].[Test] AS
      COALESCEEMPTY(([Measures].[Sales Amount]), 999999)
SELECT
      {[Measures].[Test]} ON COLUMNS,
      {[Date].[Fiscal].[Month].Members} ON ROWS
FROM
      [Adventure Works]
 
Notice the member August 2004 has a value of 999999 for the calculated member Test.
 

 
 

 
Copyright 2008 by MDXpert.com.
 
Terms of Use                              Privacy Statement
 
Site Powered By - WinHost.Com