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 Amount ParentRatio 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%