MS SQL Data Warehousing

Author

Simple query

mkazmierski@gmail.com

2005-10-30, 9:23 am

Hi,

I've just started with Analysis Services. I have had created simple
query on 'FoodMart 2000':

SELECT{ [Measures].[Unit Sales]} ON COLUMNS,
[Promotions].[Promotion Name].Members ON ROWS
FROM Sales

Now I just want to modify it to get not all, but only first ten
Promotion Names with the highest unit sales. Could you help me with
that (if you could also point some good resources about mdx I would be
grateful).

Regards, Mark

mike

2005-11-01, 8:23 pm

WITH
SET [TOP 10] AS 'TOPCOUNT({[Promotions].[Promotion Name].Members}, 10,
[Measures].[Unit Sales])'
SELECT{ [Measures].[Unit Sales]} ON COLUMNS,
[TOP 10] ON ROWS
FROM Sales

"mkazmierski@gmail.com" wrote:

> Hi,
>
> I've just started with Analysis Services. I have had created simple
> query on 'FoodMart 2000':
>
> SELECT{ [Measures].[Unit Sales]} ON COLUMNS,
> [Promotions].[Promotion Name].Members ON ROWS
> FROM Sales
>
> Now I just want to modify it to get not all, but only first ten
> Promotion Names with the highest unit sales. Could you help me with
> that (if you could also point some good resources about mdx I would be
> grateful).
>
> Regards, Mark
>
>

Darren Gosbell

2005-11-02, 3:23 am

Named sets (eg. "WITH SET...') are indispenseable for complex queries, I
just want to point out that you can also define the top 10 set "inline",
which is what I would normally do for a simple query. :)

SELECT
{ [Measures].[Unit Sales]} ON COLUMNS,
{ TOPCOUNT({[Promotions].[Promotion Name].Members}, 10,
[Measures].[Unit Sales])} ON ROWS
FROM Sales

--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell

mkazmierski@gmail.com

2005-11-04, 8:23 pm

Hey!

Thx! It works perfectly :). I also discover bottomCount but again have
a little problem with it. I want to get the last three names of
promotions which is non empty (when I try to use non empty clause I got
an error). Thx.

Regards, Mark

Darren Gosbell

2005-11-08, 9:23 am

The "NON EMPTY" clause is only valid at the start of a axis definition
(like rows and columns). What you need to do is used the filter function
to filter out the members with empty results for the measure in
question.

e.g.

SELECT
{ [Measures].[Unit Sales]} ON COLUMNS,
{ BottomCOUNT(
FILTER({[Promotions].[Promotion Name].Members}
,Not IsEmpty(Measures.[Unit Sales]))
, 3, [Measures].[Unit Sales])} ON ROWS
FROM Sales

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1131133590.165452.127860@g43g2000cwa.googlegroups.com>,
mkazmierski@gmail.com says...
> Hey!
>
> Thx! It works perfectly :). I also discover bottomCount but again have
> a little problem with it. I want to get the last three names of
> promotions which is non empty (when I try to use non empty clause I got
> an error). Thx.
>
> Regards, Mark
>
>

Sponsored Links