Hi all,

I am trying to use TopCount to display the top 20 selling products during
Q1, 1997. I have noticed that TopCount works as intended if I put
[Time].[1997].[Q1] in the slicer, but not if it is in one of the axis
clauses. Is this intended behaviour?

Eg, the query below actually lists the top-selling items for [Time].[1997].

I want to use the time dimension on the columns axis, so I can't put the
correct time member into the slicer. Is there any workaround for this
problem?

Thanks,

Peter

select
non empty
{[Time].[1997].[Q1]}
on columns,
non empty
{TopCount(Descendants([Product].levels(0).members, [Product].[Product
Name]), 20, [Measures].[Unit Sales])}
on rows
from [Sales] where
([Measures].[Unit Sales])

Try replacing

[Measures].[Unit Sales] in the Rows expression, by

([Measures].[Unit Sales] ,[Time].[1997].[Q1]

That should work
HTH
Brian
www.geocities.com/brianaltmann/olap.htm

"Brian Altmann" <anonymous (AT) discussions (DOT) microsoft.com> wrote

I still get exactly the same result, and it is obvious that it is not the
right one because the products aren't sorted by unit sales in the cellset.
Is there anything else I can try?

Thanks,

Peter

I just tried Brian's suggestion and it worked for me
- the Unit Sales did get listed in descending order.
Here's the modified MDX query that I used:

non empty
{[Time].[1997].[Q1]}
on columns,
non empty
{TopCount(Descendants([Product].levels(0).members,
[Product].[Product Name]),
20, ([Measures].[Unit Sales], [Time].[1997].[Q1]))}
on rows
from [Sales] where
([Measures].[Unit Sales])

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Thank you. My bad, I was changing the column clause and not the row clause.
It works now.

Peter