Basic MDX question

I understand the very basics of MDX. I know how to, for example, get
some set of measures as columns with some dimension on the Rows.
However, I need to do something more complex and I just can't get my
head around it.
What I'm trying to do is compare some given measure for various periods.
For example, say you have a measure [Sales] and a dimenion called
[Company Regions], the query to get the sales numbers for the company
regions basically looks like this:
with member [Date Dim].[Date Range] as
'Aggregate( {[Date Dim].[2004].[M05]:[Date Dim].[2004].[M06]}'
select [Measures].[Sales] ON COLUMNS,
Descendants([Company Regions], [Lowest Level], SELF_AND_BEFORE)
from myCube
where ([Date Dim].[Date Range])
That gives me the sales numbers for the months May and June.
What I need to do is that same basic query but I want to compare the
Date Range with last year. I just can't grasp how to make it show up in
the columns. I've read about cousin() and parallelperiods() but all the
examples return the period itself, not some measure in that period.
Can someone point me in the right direction?
Zach
Try it with a calculated member:
For example:
WITH MEMBER [Measures].[Sales_LastYear] as 'Sum([Date Dim].[2003],
[Measures].[Sales])'
MEMBER [Measures].[Sales_ThisYear] as 'Sum( {[Date Dim].[2004].[M05],[Date
Dim].[2004].[M06]}, [Measures].[Sales])'
MEMBER [Measures].[Sales_Difference] as ' [Measures].[Sales_ThisYear] -
[Measures].[Sales_LastYear]'
SELECT
{[Measures].[Sales_LastYear],[Measures].[Sales_ThisYear],[Measures].[Sales_D
ifference]} on COLUMNS,
Descendants([Company Regions], [Lowest Level], SELF_AND_BEFORE) on ROWS
from myCube
Michael
"Zach Wells" <no_zwells_spam@.ain1.com> schrieb im Newsbeitrag
news:%2366CkJyUEHA.3336@.TK2MSFTNGP11.phx.gbl...
> I understand the very basics of MDX. I know how to, for example, get
> some set of measures as columns with some dimension on the Rows.
> However, I need to do something more complex and I just can't get my
> head around it.
> What I'm trying to do is compare some given measure for various periods.
> For example, say you have a measure [Sales] and a dimenion called
> [Company Regions], the query to get the sales numbers for the company
> regions basically looks like this:
> with member [Date Dim].[Date Range] as
> 'Aggregate( {[Date Dim].[2004].[M05]:[Date Dim].[2004].[M06]}'
> select [Measures].[Sales] ON COLUMNS,
> Descendants([Company Regions], [Lowest Level], SELF_AND_BEFORE)
> from myCube
> where ([Date Dim].[Date Range])
> That gives me the sales numbers for the months May and June.
> What I need to do is that same basic query but I want to compare the
> Date Range with last year. I just can't grasp how to make it show up in
> the columns. I've read about cousin() and parallelperiods() but all the
> examples return the period itself, not some measure in that period.
> Can someone point me in the right direction?
> Zach

Bad results from my MDX Adventure Works query

I have an Adventure Works MDX query that I want to return all the male employees and their total reseller-sales (including the people below them)

Select [Measures].[Reseller Sales-Sales Amount] on Columns,
non empty
Exists(
[Employee].[Employees].AllMembers
,[Employee].[Gender].&[M]
) on Rows
from [Analysis Services Tutorial]

which, when run, returns

Reseller Sales-Sales Amount
All Employees $80,450,596.98
Ken J. Sánchez $80,450,596.98
Brian S. Welcker $80,450,596.98
Amy E. Alberts $15,535,946.26
Ranjit R. Varkey Chudukatil $4,509,888.93
Stephen Y. Jiang $63,320,315.35
David R. Campbell $3,729,945.35
Garrett R. Vargas $3,609,447.22
Jos Edvaldo. Saraiva $5,926,418.36
Michael G. Blythe $9,293,903.01
Shu K. Ito $6,427,005.56
Stephen Y. Jiang $1,092,123.86
Tete A. Mensa-Annan $2,312,545.69
Tsvi Michael. Reiter $7,171,012.75
Syed E. Abbas $1,594,335.38
Syed E. Abbas $172,524.45

so there are three problems with this result, and I think they all have 1 solution. First, I don't want the 'All Employees' row. Second, there is a female in my results, seemingly because this female is the supervisor of some of the males. I asked for no females in my query. Third, Syed shows up twice, because he is a supervisor and a salesman himself. What I want are these results..

Ken J. Sánchez $80,450,596.98
Brian S. Welcker $80,450,596.98
Ranjit R. Varkey Chudukatil $4,509,888.93
Stephen Y. Jiang $63,320,315.35
David R. Campbell $3,729,945.35
Garrett R. Vargas $3,609,447.22
Jos Edvaldo. Saraiva $5,926,418.36
Michael G. Blythe $9,293,903.01
Shu K. Ito $6,427,005.56
Stephen Y. Jiang $1,092,123.86
Tete A. Mensa-Annan $2,312,545.69
Tsvi Michael. Reiter $7,171,012.75
Syed E. Abbas $1,594,335.38

Notice that there are no "All Employees", the woman is gone, and Syed is only a supervisor, and not an underling also. What MDX query would give me these results?

Thanks,

Todd Wilder

Referring to my response to your earlier post, this query seems to return the results you want - except for the order:

Select [Measures].[Reseller Sales Amount] on Columns,
non empty Generate(exists([Employee].[Employee].[Employee],
[Employee].[Gender].&[M]),
{LinkMember([Employee].[Employee].CurrentMember,
[Employee].[Employees])}) on Rows
from [Adventure Works]

|||Your queries dont seem to return anything on my cubes - your measure is named slightly different then mine and I don't have any tuples like [Employee].[Employee].[Employee]. Where did your cube come from?|||

Are you aware of the Adventure Works standard sample cube?

http://msdn2.microsoft.com/en-us/library/ms143804.aspx

>>

SQL Server 2005 Books Online

Running Setup to Install AdventureWorks Sample Databases and Samples

Updated: 17 July 2006

The AdventureWorks (OLTP), AdventureWorksDW (data warehouse), and Adventure Works DW (analysis services) sample databases, as well as the companion samples, are not installed by default in SQL Server 2005. You can download these from SQL Server 2005 Samples and Sample Databases at the Microsoft Download Center, or you can use the following procedures to install the sample databases and samples during or after setup. Additional instructions for deploying the Adventure Works DW analysis services project are also provided.

...

>>