top n filter in universe based on two measures(MDX expression)

[Former Member]

Hi,

I have to do filter at universe level based on two measures. for example, i have

BusAr year value

ddddfd 1997 435433.

I can able to do filter using mdx expression for busniess area wise top 10 values in universe. using the following syntax

<FILTER EXPRESSION="Rank([0BUS_AREA].CurrentMember, Order([0BUS_AREA].CurrentMember.Level.Members, [Measures].[D7G51I90GYJ8POGFSYVFPX24F], BDESC))">

now i want to filter using year wise and business area wise top 10 filter in universe. where to add the second member yearwise in Rank () function.

how to achieve this.

Regards,

Shanthakumar.

[Former Member]

Hi,

I am not sure you want to make a Top n filter on 2 different measures.

But if you want to make a Top n filter on 2 ore more different dimensions, here is the code.

IIf(IsEmpty([Measures].[Sales Amount]),null,
  Rank(   StrToTuple( "(" + Generate(Head(Axis(1),Axis(1).Item(0).Count) AS RN,
  "Axis(1).Item(0).Item(" +
  CStr(RN.CurrentOrdinal - 1) +
  ").Hierarchy.CurrentMember",
  ",") + ")" ),
  Axis(1),   [Measures].[Sales Amount]  )    )

Please note that the code is taking into account all dimensions involved in the rows axis, so the more dimensions yu add in the query the top n will increase the number of values.

I have successfully tested it on MSAS but it seems that on SAP the "+" character is not allowed...

Maybe there are some SAP notes to add.

Didier

[Former Member]

Hi,

Thanks for the reply. but i can't able to understand the code. can u give any link(document) regarding this.

Regards,

Shanthakumar.

[Former Member]

Hi,

I found the solution of this [MSDN forum|http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/d66f744f-8ca8-4d9a-88ab-2a8dd1eb2d22/]

Didier