TopCount

[johnbain]

Hi All,
From what I've read and seen in my own testing, it appears that the Topcount funtion can use only one dimension for the measure dimension. Adding a second dimension effectively nullifies the measure dimension and the Topcount simeply returns the first x number of elementsin the set. Is that true? If yes then there must be a workaround to produce the top ten customers based on several dimensions. For example I want to see a top ten list for customers for Sales in the 'Actual' scenario, for the year '2012' for the brand 'XYZ'.

SubSetCreatebyMDX('TopCustomer',
'{TopCount
({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Customer] )}, 2)},
10, [OCRrevenue].([Actual])
)}'
);

Thinking ahead I am assuming the the solution will be to use a filter where scenario = 'Actual', Year = '2012' and brand = 'XYZ'. If that is the case , how do include the filter statment in the above syntax?

Any help would be greatly appreciated.
Thanks - John

[tomok]

The thing with a TOPCOUNT based subset is the dimension the TOPCOUNT is based on can be the only dynamic variable. For all the other dimensions in the cube you have to name a specific member for each. So, in your case you would have to hard code the 'Actual', '2012' and 'XYZ' into the subset. Then, if you wanted a subset based on '2011' you would have to have an entirely new subset which somewhat limits the flexibility. There is a way to put the element choices for the other dimensions into a string cube and reference them in the MDX but that makes the coding pretty complex. If you are interested I could post an example of what that would look like.

[johnbain]

Thanks for the reply Tomok. I have decided against trying to keep the Topcount in a subset. This application will have Executive Viewer attached to it. I see that this functionality is built into EV.
Thanks

[Duncan P]

Just as a matter of interest how exactly should the top count based on multiple dimensions be defined? I can't get my head round it.