Report Portal

How do I check for the top level in a dimension to avoid division by an nonexsting parent(-1.#INF)

 How do I check for the top level in a dimension to avoid division by an nonexsting parent(-1.#INF)

Query Example:

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];

Result will be:

 Internet Sales AmountParentRatio
All Products$9,791,060.30 (null)
Accessories$293,709.71 3.00%
Bike Racks$16,440.00 5.60%
Bike Stands$18,921.00 6.40%
Bottles and Cages$23,280.27 7.90%
Cleaners$3,044.85 1.00%
Fenders$19,408.34 6.60%
Helmets$92,583.54 31.50%
Hydration Packs$16,771.95 5.70%
Tires and Tubes$103,259.76 35.20%
Bikes$9,359,102.62 95.60%
Mountain Bikes$3,989,638.48 42.60%
Road Bikes$3,952,029.21 42.20%
Touring Bikes$1,417,434.93 15.10%
Clothing$138,247.97 1.40%
Caps$7,956.15 5.80%
Gloves$14,228.69 10.30%
Jerseys$70,370.46 50.90%
Shorts$30,445.65 22.00%
Socks$2,229.52 1.60%
Vests$13,017.50 9.40%

 

 

Tags: mdx, faq, non empty

 

2007-2015 VidasSoft Systems Inc.