Hi,

I have some difficulties with this MDX query.

I have a cube Sales, with the following dimensions :

Dimension : Period (All),Year,Month
Dimension : Custommers parent-child (All, .....)
Dimension : Stores (All),Store code

and the following measures :

- sales type 1,
- sales type 2,
- profit,
- profit rate (calculated member)
-> profit rate = profit/([sales type 1]+[sales type 2])

The end-user select in drop-down list
- a customer
- a beginnig period
- a ending period

I want to display a table
- Stores on rows
- profit, sales and profit rate details on columns by month and a sum over
the period

like this :

All period 01/05 02/05 03/05 04/05 05/05
--------------------------------------------------------------
store1 sales | 6000 1000 1000 1000 2000 1000
profit | 600 100 100 100 200 100
profit rate | 10% 10% 10% 10% 10% 10%

store2 sales |
profit |
profit rate |
......... |
storeN sales|



WITH

MEMBER [Measures].[sales] AS
'Measures.[sales type 1] + Measures.[sales type 2]'

MEMBER [Measures].[sum_profit] AS
'Sum(
{
[Period].[Year].&[2005].&[1]:[Period].[Year].&[2005].&[6]
},
Measures.[profit])'

MEMBER [Measures].[sum_sales] AS
'Sum(
{[Period].[Year].&[2005].&[1]:[Period].[Year].&[2005].&[6]}
,
Measures.[sales]
)'

MEMBER [Measures].[rate] AS
'Measures.[sum_profit] / Measures.[sum_sales]',
FORMAT_STRING = 'Percent'

MEMBER [Period].[All Period] AS
'Iif(Measures.CurrentMember.Properties("Caption") <> "profit",
Iif(Measures.CurrentMember.Properties("Caption")) <> "profit rate",
[Measures].[sum_sales],
[Measures].[rate]
),
Measures.[sum_profit])'

Select
{
[Period].[All Period],
[Period].[Year].&[2005].&[1]:[Period].[Year].&[2005].&[6]
}
ON Columns,

Non Empty CrossJoin(
Stores.[Store code].Members,
{Measures.[sales], Measures.[profit], Measures.[profit rate]}
) On Rows
From Sales
Where {Custommers.&[4098]}

The problem :
The value of member ([All Period],Measures.[Sales]) is twice too large ??

Many thanks for your help.

Damien

Is the value of member ([All Period],Measures.[Profit]) also twice too
large; and what is the member name at the "All" level of the [Period]
dimension?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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