Playing with Named Sets

Hi,

Considering these data, which shows turnover per Salesmen/region

SalesMan1

SalesMan2

SalesMan3

SalesMan4

A

20

30

45

65

B

40

60

90

135

C

45

65

100

150

D

30

45

70

105

E

50

75

115

150

F

15

25

40

60

I created different named sets such as :

CREATE SET CURRENTCUBE.[Without F Region]

AS {EXCEPT([Region].[Region Code].[All].Children,

[Region].[Region Code].&[F])};

CREATE SET CURRENTCUBE.[SalesMan1]

AS {EXCEPT([Sales Force].[SalesMan].[All].Children,

[Sales Force].[SalesMan].&[SalesMan1])};

Using those named sets I would like to define a measure that will show the turnover for region A B C D E and SalesMan1 ...

SalesMan1

SalesMan2

SalesMan3

SalesMan4

A

20

0

0

0

B

40

0

0

0

C

45

0

0

0

D

30

0

0

0

E

50

0

0

0

F

0

0

0

0

I tryed different syntax

SUM(([Without F Region],[SalesMan1]),[Turnover])

SUM(Crossjoin([Without F Region],[SalesMan1]),[Turnover])

but I cannot get it work. The total is correct, but using dimension, the result is not splited.

SalesMan1

SalesMan2

SalesMan3

SalesMan4

A

185

185

185

185

B

185

185

185

185

C

185

185

185

185

D

185

185

185

185

E

185

185

185

185

F

185

185

185

185

Any idea ?

Regards

Ayzan

The problem is that the named sets are evaluated before the context is set in the query.

If you are using AS2005 you could force the context to be re-evaluated with the EXISTING statement

eg

SUM(Crossjoin(EXISTING [Without F Region], EXISTING [SalesMan1]),[Turnover])

|||

Thank you for your reply.

It works perfectly.

Regards

Ayzan