MDX Error handling Cheat Sheet
Contributors: Vidas Matelis, Thomas Ivarsson
MDX Error handling Cheat Sheet
| How do I check for division by zero and null to avoid -1.#INF in cells? WITH MEMBER [Measures].[ZeroValuePerUnit] AS 0MEMBER [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 ROWSFROM [Adventure Works]WHERE([Date].[Calendar].[Calendar Year].&[2004]); | How do I check for the top level in a dimension to avoid division by an nonexsting parent(-1.#INF)? WITH Member Measures.ParentRatio As IIF([Product].[Product Categories].CurrentMember.Parent IS NULL, Null, [Measures].[Internet Sales Amount]/([Measures].[Internet Sales Amount],[Product].[Product Categories].CurrentMember.Parent)), Format_String = '###.#%'Select {[Measures].[Internet Sales Amount],Measures.ParentRatio} On Columns, NON EMPTY Descendants([Product].[Product Categories],[Product].[Product Categories].[Subcategory],Self_And_Before) On Rows From [Adventure Works]Where [Date].[Calendar].[Calendar Year].&[2003]; |
| How can I reference dimension members/measures in MDX statements when sometimes they might not exists? WITH MEMBER [Measures].[2007 Sales] AS IIF(IsError([Date].[Calendar].[Calendar Year].&[2007]), NULL, ([Date].[Calendar].[Calendar Year].&[2007], [Measures].[Sales Amount]))SELECT [Measures].[2007 Sales] ON 0FROM [Sales Summary]; | |
Please contribute to this document. You can send e-mail with your query or just leave here comment. We will add query to the list.
Tags: mdx