Report Portal

How do I check for division by zero and null to avoid -1.#INF in cells

How do I check for division by zero and null to avoid -1.#INF in cells.

WITH
MEMBER [Measures].[ZeroValuePerUnit] AS 0
MEMBER [Measures].[DivideByZero] As [Measures].[Internet Sales Amount]/[Measures].[ZeroValuePerUnit] ,Format_String = '#.#0'
MEMBER [Measures].[CheckDivByZero] AS IIF([Measures].[ZeroValuePerUnit] = 0, Null,  [Measures].[Internet Sales Amount]/[Measures].[ZeroValuePerUnit]),Format_String = '#.#0'
SELECT {[Measures].[Internet Sales Amount], [Measures].[ZeroValuePerUnit], [Measures].[DivideByZero], [Measures].[CheckDivByZero]} ON COLUMNS,
 [Customer].[Country].Children ON ROWS
FROM [Adventure Works]
WHERE([Date].[Calendar].[Calendar Year].&[2004]);

Result will be:

 Internet Sales AmountZeroValuePerUnitDivideByZeroCheckDivByZero
Australia$2,563,884.29 01.#INF(null)
Canada$673,628.21 01.#INF(null)
France$922,179.04 01.#INF(null)
Germany$1,076,890.77 01.#INF(null)
United Kingdom$1,210,286.27 01.#INF(null)
United States$3,324,031.16 01.#INF(null)

 

Tags: mdx, faq

 

2007-2015 VidasSoft Systems Inc.