CODEWITHSUNDEEP
ssasmdx
SQL.injection
SQL.injection

Reputation: 2647

select the top 10 products of a given category

I am trying to select the top 10 products by internet sales from the Adenture Works cube (the cube I managed to compile with the Analysis Services Tutorial), I managed to do the cube perfectly, now I need to add a filter to add a specific product line and a given month and I don't know how to build the query...

I have this working:

with 
set productNames as head(order({[Product].[Model Name].children}, [Measures].[Internet Sales-Unit Price], desc), 10)
select [Measures].[Internet Sales-Unit Price] on 0, 
productNames on 1 
from [Analysis Services Tutorial]

I added the filter and the result is the same...

with 
set productNames as head(order(
        filter({[Product].[Model Name].children},[Product].[Product Model Lines].[Product Line].&[R]  )
        , [Measures].[Internet Sales-Unit Price], desc), 10)
select [Measures].[Internet Sales-Unit Price] on 0, 
productNames on 1 
from [Analysis Services Tutorial];

Note: I can't use the hierarchy, because on my production database (which is not adventure works) I don't have hierarchies setup for the given problem.

Upvotes: 0

Views: 526

Answers (1)

The Dumb Radish
The Dumb Radish

Reputation: 896

Take a look at this query:

SELECT
[Measures].[Internet Sales Amount] ON COLUMNS
,   TOPCOUNT([Product].[Product Line].&[R]*[Product].[Model Name].[Model Name],10,[Measures].[Internet Sales Amount]) ON ROWS
FROM
[Adventure Works]

The FILTER function is designed to filter a set of members that conform to a particular condition e.g. having Sales greater than £1000. It is not suitable for filtering a set by other dimensions or attributes. For this you can use the CROSSJOIN as I have done above (the *) and also the NONEMPTY function.

I hope that helps,

Ash

Upvotes: 2

Related Questions