TM1 TOPCOUNT MDX query not working

[wigglyrat]

Hi:

I am working to create an MDX query in that brings back the top 10 'BDE's'. The details are as follows

Cube: zz BDE
Dimension: zz BDE (this is the dimension where I want the top 10 from)
Measure: Gross Net Written Premium (this is in a dimension called 'zz Measures')

So far I have tried

Code: Select all

{TOPCOUNT({TM1FILTERBYLEVEL( {TM1SUBSETALL( [zz BDE] )}, 0)}, 10, [zz BDE].([zz Measures].[Gross Net Written Premium]))} 

But this does not return any results.

Can anyone please clarify which fields go into the last part of this formula to pull back this info?

Many thanks in advance.

[Edward Stuart]

The MDX Primer is your friend and it can be found here:

http://bihints.com/book/export/html/68

A classic Top 10 command:
{ TOPCOUNT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, 10, [Test].([Posting Measures].[Amount]) )}
By omitting a sort order it sorts in the default order (which has the values descending in value and breaks any hierarchies present).

[lotsaram]

I doubt your cube has only the BDE and measure dimension! You need to specify the element at each dimension other than the one being sorted on.