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

Pivoting large result sets

Heres the scenario:
We have a database that stores values for different characteristics taken at different times. The data is stored in the following format:

POSTED_UTS CHAR_ID RESULT_VALUE_FLOAT 2005-08-09 14:30:03.907 1859 1.08 2005-08-09 14:30:03.907 1860 1.07 2005-08-09 14:30:03.937 1861 0.01 2005-08-09 14:30:03.937 1859 0.01 2005-08-09 14:30:03.937 1860 0.01 2005-08-09 14:30:03.937 1861 0.01 2005-08-09 14:30:03.953 1756 0.01 2005-08-09 14:30:03.953 1757 0.01 2005-08-09 14:30:03.953 1859 0.01 2005-08-09 14:30:03.953 1859 0.01

The result set for a two hour time span returns >41,000 rows, the result set for a one week time span returns >2.9 million rows. We have multiple data sources, each with its own set of characteristics, each source takes readings at different times and then stores those readings in the above schema. The problem we are running into is, our end users want the data in the following format:

Posted UTS Char 1 Char 2 Char 3 Char 4 . . . Char <n> 2005-08-09 14:00:00.000 2.3 3.4 NULL NULL . . . <value n> 2005-08-09 14:00:30.000 2.3 3.4 5 66.8875 . . . <value n> 2005-08-09 14:00:00.000 NULL 3.4 NULL NULL . . . <value n> 2005-08-09 14:00:00.000 5.6 NULL NULL NULL . . . <value n>

Needless to say, when we pivot on a two hour block, it does take all that long (just over a minute), but when we try to pivot over a one week block, it takes considerably longer, much longer than anyone likes.

Is there a better way of doing this? Would storing the data in the same schema as we want to report in be wiser?

System Information: 2x XEON (Hyperthreaded to 4) 3Ghz
3GB ram
SQL Server 2005
Reporting Services 2005

Thanks for any help.

Wayne E. Pfeffer

The only fast solution I can think of is to use an ssas cube to pivot this

Philippe