I have a simple problem in perspective to sql but as i am new to mdx i
am unable to write mdx for this.

I have three measures [class], [rollno],[marks]

[class] [rollno] [marks]
-------------------------------
class1 1 20
class1 2 22
class1 3 12

class2 4 21
class2 5 18
class2 6 20

class3 7 23
class3 8 12
class3 9 14

class4 10 12
class4 11 12
class4 12 24

now i need topers from each class
class1 2 22
class2 4 21
class3 7 23
class4 12 24

and then top three from toppers

class4 12 24
class3 7 23
class1 2 22

please help,

thanks
sanjeev

What are the dimensions and base measures of the cube - class and rollno
look more like dimensions than measures?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Sorry [RollNo] is a dimention,
but class is a calcuted member using with, it has some numeric values.
and there is one more dimention [fund] that i used in where clause

SQL version :
select top 3 class,rollno,marks
from #table1 A,
(select class, marks = max(marks)
from #table1
group by class ) B
where A.class = b.class
and A.marks = B.marks
order by A.marks desc

Assuming that [class] only has a single value for a given member of
[Rollno], then the approach discussed in this earlier thread may help:

http://groups-beta.google.com/group/...rver.olap/brow
se_thread/thread/1a5b25fbc03ad959/766057652d6c6ebb#766057652d6c6ebb
SET CUST_LIST1 AS 'Order( Head([Customers].[Name].Members, 20),
[Customers].CurrentMember.Properties ("Member Card"), BASC)'
MEMBER [Measures].Card as
'[Customers].CurrentMember.properties("Member Card")'

MEMBER [Measures].PreviousInList as 'Rank( [Customers].CurrentMember,
CUST_LIST1)-2'
MEMBER [Measures].PreviousInList_Card as 'iif(PreviousInList < 0,
"None",
SubSet( CUST_LIST1,PreviousInList, 1).Item(0).properties("Member
Card"))'

MEMBER [Measures].YesOrNo as 'iif(PreviousInList < 0, 1,
iif(CStr([Measures].Card) <> Cstr([Measures].PreviousInList_Card), 1,
0))'

SET CUST_LIST2 as 'filter(CUST_LIST1, ([Customers].CurrentMember,
[Measures].YesOrNo) = 1)'

SELECT
{[Unit Sales], Card, PreviousInList, PreviousInList_Card,
YesOrNo } ON COLUMNS,
{ CUST_LIST2 } ON ROWS
FROM Sales
...

A modified version of this query for your scenario is:

SET ROLL_LIST AS
'Order(Order([Rollno].[Rollno].Members,
[Measures].[marks], BDESC),
[Measures].[class], BASC)'

MEMBER [Measures].PreviousInList as 'Rank( [Rollno].CurrentMember,
ROLL_LIST)-2'
MEMBER [Measures].PreviousInList_Class as
'iif(PreviousInList < 0,
"None",
(SubSet(ROLL_LIST, PreviousInList, 1).Item(0),
[Measures].[class]))'
MEMBER [Measures].YesOrNo as
'iif(PreviousInList < 0, 1,
iif(CStr([Measures].[class])
<> Cstr([Measures].PreviousInList_Class),
1, 0))'

SET [Toppers] as
'filter(ROLL_LIST, ([Rollno].CurrentMember,
[Measures].YesOrNo) = 1)'

SELECT
{[Measures].[class], [Measures].[marks]} ON COLUMNS,
TopCount([Toppers], 3, [Measures].[marks]) ON ROWS
FROM MarksCube

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

I tried this but there is a strange problem it uses 100 % CPU when i
add ROLL_LIST set,
as in my another problem i face similar problem with bottomcount.

please guide

Does simply returning ROLL_LIST work, like:

SET ROLL_LIST AS
'Order(Order([Rollno].[Rollno].Members,
[Measures].[marks], BDESC),
[Measures].[class], BASC)'

SELECT
{[Measures].[class], [Measures].[marks]} ON COLUMNS,
ROLL_LIST ON ROWS
FROM MarksCube

Also, if there is a way to make [Class] a dimension, rather than just a
measure, the query should be simplified.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***