Combine TOPCOUNT, and BOTTOMCOUNT resultset in MDX

Question

I am new to MDX, and have a simple requirement which would be easy to do with SQL, but I would like to accomplish this with MDX.

I would like to combine the result, of 2 queries, so when quering the cube, I only send over 1 query.

 
select topcount( [Fact].[Year].children, 1,[Measures].[MoneyIn]) on columns
from [Cube]

and also this MDX query, to get the least money in, for a period of years.

 
select bottomcount( [Fact].[Year].children, 1,[Measures].[MoneyIn]) on columns
from [Cube]

Is there an easy way to accomplish this with MDX? Ideally I would have something :

MaxValue MinValue
10k      -10k

Thanks! All help appreciated!

Rick Rick · Accepted Answer · 2013-01-04T13:18:27

You could write your query a few different ways. This puts the counts in one column:

SELECT [Measures].[MoneyIn] ON COLUMNS,
{TOPCOUNT([Fact].[Year].children,1, [Measures].[MoneyIn]),
BOTTOMCOUNT([Fact].[Year].children,1, [Measures].[MoneyIn])} ON ROWS
FROM [Cube]

This is closer to your output but without the named columns

SELECT [Measures].[MoneyIn] *
       {TOPCOUNT([Fact].[Year].children,1, [Measures].[MoneyIn]),
        BOTTOMCOUNT([Fact].[Year].children,1, [Measures].[MoneyIn])
       } ON COLUMNS
FROM [Cube]

Combine TOPCOUNT, and BOTTOMCOUNT resultset in MDX

1 Answers