MDX query for bottomcount function

2
votes
SELECT 
  [Measures].[Internet Sales Amount] ON COLUMNS
 ,BottomCount
  (
    NonEmpty([Customer].[Customer].[Customer].MEMBERS)
   ,10
   ,[Measures].[Internet Sales Amount]
  ) ON ROWS
FROM [Adventure Works]
WHERE 
  [Date].[Calendar].[Calendar Year].&[2005];

The above query is showing me result having last 10 customer names with NULL measure value. I am using Adventure works Cube.

As per my understanding "bottomcount" is not working fine with where clause in "mdx" query. I want to fetch last 10 customer names for 2005 with measure value and hopefully i am looking for solution without using keyword "DESCENDANTS".

Please let me know in case, I am doing something wrong.

2
ssasmdx

2 Answers

2
votes

Give this a try:

bottomcount(
    nonempty(
        [Customer].[Customer].[Customer].Members
        ,[Measures].[Internet Sales Amount]
    )
    ,10
    ,[Measures].[Internet Sales Amount]
)

You were getting the Customer names with NULL because in OLAP you are doing the cartesian product between the 2 dimensions (Measures is a special dimension, but it still acts as a dimension) it does not matter that you don't have values it will still crossjoin the members in your 2 sets and unless you request just the nonempty ones it will still give you the NULLs.

1
votes

And if you'd like their full internet sales amount then move some of the logic into the WITH clause:

WITH 
  SET bot AS 
    NonEmpty
    (
      [Customer].[Customer].[Customer].MEMBERS
     ,(
        [Measures].[Internet Sales Amount]
       ,[Date].[Calendar].[Calendar Year].&[2005]
      )
    ) 
SELECT 
  [Measures].[Internet Sales Amount] ON COLUMNS
 ,BottomCount
  (
    bot
   ,10
   ,[Measures].[Internet Sales Amount]
  ) ON ROWS
FROM [Adventure Works];