MDX:Handling division by zero errors

WITH MEMBER [Date].[Calendar Year].[2006VS2007] AS [Date].[Calendar].[Calendar Year].&[2007] / [Date].[Calendar].[Calendar Year].&[2006], FORMAT_STRING = ‘Percent’ SELECT { [Date].[Calendar Year].[Calendar Year].&[2006], [Date].[Calendar Year].[Calendar Year].&[2007], [Date].[Calendar Year].[2006VS2007]  } * [Measures].[Internet Sales Amount] ON 0, non empty {([Product].[Product Categories].children ) } ON 1 FROM [Adventure Works]   If you notice where Value of Internet Sales is Null for Base Year…

MDX: Nonempty v/s NonEmpty

Nonempty Returns the set of tuples that are not empty from a specified set, based on the cross product of the specified set with a second set. NONEMPTY(set_expression1 [,set_expression2]) set_expression1 A valid Multidimensional Expressions (MDX) expression that returns a set. set_expression2 A valid Multidimensional Expressions (MDX) expression that returns a set. In this example, getting…

Top 10 products with Lowest Reseller Order count of at least 400.

This shows how to use Head, Order, Filter & TopCount Function.   Reseller Order Count Classic Vest, S 514 Short-Sleeve Classic Jersey, XL 495 Hitch Rack – 4-Bike 468 AWC Logo Cap 460 Long-Sleeve Logo Jersey, L 451 Water Bottle – 30 oz. 444 AWC Logo Cap 442 Women’s Tights, L 437 Long-Sleeve Logo…

MDX Descendant Function

This example shows how to use Descendant function with Desc_Flag as self_before_after which means this will show all members at level – Subcategory_Category_Product. and to prove that we are getting correct result I am using Member Property – Level which shows Level Name to which Member Belongs With Member Measures.ProductStatus As [Product].[Status].Member_Key Member Measures.Levela as…

MDX: EXISTING

By default, sets are evaluated within the context of the cube that contains the members of the set. The Existing keyword forces a specified set to be evaluated within the current context instead.   with member [Measures].[k] as ([Product].[Category].currentmember, [Measures].[Measures].[Reseller Sales Amount] ) Select [Product].[Category].[All].Children on 1 , Measures.k on 0 from [Adventure Works]…

YTD Based on Current System Date

with member [Date].[Calendar].[currentyear] as aggregate(ytd((strtomember(“[Date].[Calendar].[Date].&[“+format(now(),”yyyyMMdd”)+”]”)))) member [Date].[Calendar].[prevyear] as aggregate(ytd(parallelperiod([Date].[Calendar].[Calendar Year] ,1,(strtomember(“[Date].[Calendar].[Date].&[“+format(now(),”yyyyMMdd”)+”]”))))) select { [Date].[Calendar].[currentyear] ,[Date].[Calendar].[prevyear] }on 0, [Measures].[Internet Sales Amount] on 1 from [Adventure Works] Well, my customer wanted to pass fix start date member and get the result so changed query as per Customer’s requirement. From your application you can pass value but…