Multidimensional Expression (MDX) – part 2

Many developer start MDX query learning with MDX tutorial, learning tuples, namedsets, axis, select query syntax etc….. But still after that, many queries remain to resolve and need hands-on experience of some real time project when one really starts applying functions, tweaking or nesting queries and parameters.
In this session will start with some complex query with adventure works database.
Hope this helps all to resolve last movement MDX requirements when developers don’t have time to learn all the syntax, structure and theory:

How to find bottom 10 customers with lowest sales in 2003 that were not null?
Simple using BOTTOMCOUNT will return customers with null sales and we can use NONEMPTY or FIlTER to remove null data.

SYNTAX:

SELECT {[Measures].[Internet Sales Amount] } ON COLUMNS,
BOTTOMCOUNT(NONEMPTY(DESCENDANTS([Customer].[Customer Geography].[All Customers]
, [Customer].[Customer Geography].[Customer])
, ([Measures].[Internet Sales Amount]))
, 10
,( [Measures].[Internet Sales Amount])
) ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Calendar].Calendar Year].&[2003])

Result will be:
Internet Sales Amount
Ariana Peterson $2.29
Olivia Brown $2.29
Abigail L. Bennett $2.29
Natalie L. Bryant $2.29
Madison D. Lee $2.29
Lauren Miller $2.29
Stephanie B. Murphy $2.29
Cameron L. Rodriguez $2.29
Melanie Peterson $2.29
Alfredo Romero $2.29