MDX query for Microsoft Adventure Work DW 2012

Question

It's the first time I touch MDX and I would like to get the query

• Get the average Internet Sales Amount over different cities and different product categories over two fiscal years 2007 and 2008.

The following is my MDX query, but it failed to compile.

SELECT
{ [Geography].[City].CHILDREN } ON COLUMNS,
{ [Product].[Category].CHILDREN } ON ROWS
FROM [Adventure Works]
WHERE ( 
    [Measures].[Measures].[Internet Average Sales Amount], 
    [Date].[Fiscal Year].&[2007], 
    [Date].[Fiscal Year].&[2008]
)

The above query come with an error

Query (7, 1) The Fiscal Year hierarchy is used more than once in the Crossjoin function.

Answer 1

Try the following. : is the range operator. For more information click.

SELECT
{ [Geography].[City].CHILDREN } ON COLUMNS,
{ [Product].[Category].CHILDREN } ON ROWS
FROM [Adventure Works]
WHERE ( 
    [Measures].[Measures].[Internet Average Sales Amount], 
    [Date].[Fiscal Year].&[2007] : [Date].[Fiscal Year].&[2008]
)

It's been a while since I used MDX, but I think you will have to change more then that to get the desired result.

Answer 2

It's the first time I touch MDX, I am still confusing with SQL query.

For instance, if we are looking for Internet Sales Amount over different cities and different product categories.

According to the desired requirement, is the following MDX query COULD fulfill the requirement.

SELECT
    { [Geography].[City].CHILDREN } ON COLUMNS,
    { [Product].[Category].CHILDREN } ON ROWS
FROM [Adventure Works]
WHERE [Measures].[Internet Sales Amount]

I have a little bit confuse in SELECT and WHERE clause

What I expect the above statement is something like this, one axis shows all possible cities and another one shows all possible product category, and each cell is the aggregated Internet Sales Amount.

For instance.

       New York | Tokyo | ...
Car       $123  |  $50  | ...
Toys      $456  |  $100 | ...

I.e. New York has $123 Internet Sales Amount for Car

Is the MDX query fulfill my expectation?