CODEWITHSUNDEEP
sql-serverssasmdxmdxstudiomdxclient
Antonio Labra
Antonio Labra

Reputation: 2010

Get Top 5 by Different years MDX

I have a top 5 of Customers by year and I want to create a query that gets this information per year in different years at the same time, I mean:

select 
[Measures].[Ventas] 
on columns,
non empty
topcount
(
[Dim Cliente].[Company Name].Children,5,[Measures].[Ventas]
) 
on rows
from 
[DWH Northwind]
where 

[Dim Tiempo].[Año].&[1996]

TOP 5 1996

TOP 5 1996

Could I get Top 5 of 1996 and 1997 together separated per year?

Upvotes: 1

Views: 188

Answers (2)

Marc Polizzi
Marc Polizzi

Reputation: 9375

You could use the Generate function as following:

select 
  [Measures].[Ventas] on columns,

  non empty Generate(
    { [Dim Tiempo].[Año].&[1995], [Dim Tiempo].[Año].&[1996] } as yy,
    topcount (yy.currentMember * [Dim Cliente].[Company Name].Children,5,[Measures].[Ventas]) 
  ) on rows

from [DWH Northwind

This way you could retrieve the TOP 5 for each available years the same way:

select 
  [Measures].[Ventas] on columns,

  non empty Generate(
    [Dim Tiempo].[Año].members as yy,
    topcount (yy.currentMember * [Dim Cliente].[Company Name].Children,5,[Measures].[Ventas]) 
  ) on rows

from [DWH Northwind

Hope that helps.

Upvotes: 4

MoazRub
MoazRub

Reputation: 2911

Try this 

select 
[Measures].[Ventas] 
on columns,
non empty
{
topcount
(([Dim Tiempo].[Año].&[1996],[Dim Cliente].[Company Name].Children),5,[Measures].[Ventas]) 
,
topcount
(([Dim Tiempo].[Año].&[1997],[Dim Cliente].[Company Name].Children),5,[Measures].[Ventas]) 
}
on rows
from 
[DWH Northwind]
where

Upvotes: 2

Related Questions