Returns: A numeric value or a string, depending on the type of the arguments.
Syntax
.-,------------------. V | >>-CoalesceEmpty--(--+-NumericExpression1----NumericExpressionN-+-+--)->< | .-,-----------------. | | V | | '-StringExpression1----StringExpressionN-+---'
Parameters
- NumericExpression1
- Specifies the numeric expression whose value to check. If the value of this expression is null, the function returns the value of one of the replacement numeric expressions.
- NumericExpression2 ... NumericExpressionN
- Specifies the numeric expressions whose values the function can return to replace a null NumericExpression1. The function returns the first non-null value that one of the replacement expressions evaluates to. If all of the replacement expressions evaluate to null, the function returns null.
- StringExpression1
- Specifies the string expression whose value to check. If the value of this expression is null, the function returns the value of one of the replacement string expressions.
- StringExpression2 ... StringExpressionN
- Specifies the string expressions whose values the function can return to replace a null StringExpression1. The function returns the first non-null value that one of the replacement expressions evaluates to. If all of the replacement expressions evaluate to null, the function returns null.
Description
The CoalesceEmpty function replaces the values of empty tuples with a string or numeric value that you specify.
In many cases, you want to manage null values by removing empty cells from your result data because it takes up space and affects the presentation of a report. However, in some cases you might want to view the empty cells for a business reason, such as to see which products were not sold at a particular store location. In this case, you do not want the empty cells removed, instead you want to give these empty cells a particular value. You can use the CoalesceEmpty function to substitute the null value with a more meaningful value such as a zero ("0") or "none".
The CoalesceEmpty function will return a string value when you specify a string expression or a numeric value when you specify a numeric expression. The two parameters within an expression must be the same type.
Example
In this example, you want to compare a value that is totalled for a month with the value for the last day of the month. The ClosingPeriod function in the following query returns the number of items on the last day of each month. The number of items for the month is the sum of the number of items for each day in the month. The closing period number of items is the number of items for the last day of the month.
The following query uses the CoalesceEmpty function to improve the appearance of your report by substituting a zero if the number of items for the last day in the month is null.
WITH
MEMBER [Price Analysis].[Measures].[Closing Period Number Of Items]
AS CoalesceEmpty(
(ClosingPeriod([Price Analysis].[Time].[Day of Calendar Month Level
(Price Analysis)],[Price Analysis].[Time].CurrentMember),
[Price Analysis].[Measures].[Number Of Items]),0 )
SELECT {[Price Analysis].[Measures].[Number Of Items],
[Price Analysis].[Measures].[Closing Period Number Of Items]}
ON AXIS(0),
{Descendants([Price Analysis].[Time].[All Time (Calendar)].[2002],
[Price Analysis].[Time].[Calendar Month Level (Price Analysis)])}
ON AXIS(1)
FROM [Price Analysis]
| Time | Number Of Items | Closing Period Number Of Items |
|---|---|---|
| 1 | 6872 | 275 |
| 2 | 8830 | 403 |
| 3 | 2845 | 0 |
| 4 | 3090 | 75 |
| 5 | 2383 | 105 |
| 6 | 3194 | 0 |
| 7 | 3932 | 167 |
| 8 | 3188 | 158 |
| 9 | 3127 | 186 |
| 10 | 2998 | 137 |
| 11 | 0 | |
| 12 | 0 |
Related functions
You can provide one set as an argument to the NonEmptyCrossjoin function to get a set with its empty tuples removed.