HOW TO GET TOP 1000 RESULTS FROM MDX

• srathna77

  SSCommitted

  Points: 1982

  More actions

  January 5, 2009 at 11:10 am

  #217170

  SELECT

  NON EMPTY

  { [Measures].[Total With Tax],

  [Measures].[Estimated Value],

  [Measures].[Extended EV],

  [Measures].[Extended CP],

  [Measures].[Customer Price] }

  ON COLUMNS,

  NON EMPTY

  { TOPCOUNT(

  [Dim Order Detail].[Order Detail ID].[Order Detail ID].

  ALLMEMBERS ,100,[Dim Order Detail].[Shipping Date].[Shipping Date]

  .ALLMEMBERS ) }

  DIMENSION

  PROPERTIES

  MEMBER_CAPTION,

  MEMBER_UNIQUE_NAME ON ROWS

  FROM [Schweser Warehouse]

  CELL PROPERTIES VALUE,

  BACK_COLOR, FORE_COLOR,

  FORMATTED_VALUE, FORMAT_STRING,

  FONT_NAME, FONT_SIZE, FONT_FLAGS

  I Am getting this following error:

  Executing the query ...

  Query (10, 5) The TOPCOUNT function expects a string or numeric expression for the argument. A tuple set expression was used.

  Execution complete.

  Help Appreciated

  Thanks in advance

• denglishbi

  SSCrazy Eights

  Points: 8831

  More actions

  January 5, 2009 at 7:29 pm

  #921536

  What are you trying to return exactly? You want to TopCount based on a date? Or based on a particular value? Trying to figure out what you are looking at returning in order how to provide you a solution.

  The TopCount would be looking at a value. Like return the Top 100 customers based on Sales Amount. Or you could utilize a Rank and then return the Top Percent of Customers where the Rank function is then Ranking Customers based on Sales Amount.

  You have a set of dates as your filter/criteria for the TopCount function and that is not going to work.

  TopCount(MDX): Sorts a set in descending order and returns the specified number of elements with the highest values.

  TopCount(Set_Expression,Count [ ,Numeric_Expression ] )

  ----------------------------------------------------------------------------------------
  Dan English - http://denglishbi.wordpress.com