Example of MDX in foodmart in SQL 2000

1. Calculate the average sales price for each time period:

With
Member [measures]. [AVG sales price]
'[Measures]. [store sales]/[measures]. [sales count]'
Select {[measures]. [store sales], [measures]. [sales count], [measures]. [AVG sales price]} on columns,
{[Time]. [1, 1997], [time]. [1998]} on rows
From sales

2. product sales overview for all years

Select crossjoin ({[time]. [year]. Members}, {[measures]. [store sales], [measures]. [sales count]}) on columns,
{[Product]. [All products]. [Drink], [product]. [All products]. [Drink]. [beverages], [product]. [All products]. [Drink]. [dairy]} on rows
From sales

3. Calculate the proportion of each product to the total sales volume per year (SHARES)

With
Member [measures]. [percent]
'[Measures]. [store sales]/([measures]. [store sales], [product]. [All products])',
Format_string = '0. 00%'
Select
Crossjoin ([time]. [year]. Members, {[measures]. [store sales], [measures]. [percent]}) on columns,
{[Product]. [All products]. [Drink], [product]. [All products]. [food], [product]. [All products]. [non-consumable]} on rows
From sales

 

4. Calculate the period-over-period growth of sales in each period (period-over-period)

With
Member [measures]. [sales increase]
'[Measures]. [store sales]-([measures]. [store sales], [time]. [1997])'
Select
Crossjoin ({[product]. [All products]}, {[measures]. [store sales], [measures]. [sales increase]})
On columns,
{[Time]. [year]. Members}
On rows
From sales

5. Calculate the popularity of the product (this is calculated based on the number of products sold)

With
Member [measures]. [welcome degree]
'[Measures]. [sales count]/([measures]. [sales count], [product]. currentmember. Parent )',
Format_string = '0. 00%'
Select
Crossjoin ([time]. [year]. Members, {[measures]. [store sales], [measures]. [welcome degree]}) on columns,
{[Product]. [All products]. [Drink]. [alcoholic beverages], [product]. [All products]. [Drink]. [beverages], [product]. [All products]. [Drink]. [dairy], [product]. [All products]. [food]. [baked goods], [product]. [All products]. [food]. [baking goods], [product]. [All products]. [food]. [breakfast foods], [product]. [All products]. [food]. [canned foods], [product]. [All products]. [food]. [dairy], [product]. [All products]. [food]. [frozen foods]} on rows
From sales

6. Average Phase sales (volume)

-- AVG (descendants ([time]. currentmember, [time]. [months]), [measures]. [quantity]) = sum (descendants ([time]. currentmember, [time]. [months]), [measures]. [quantity])/count (descendants ([time]. currentmember, [time]. [months])

With
Member [measures]. [period quantity]
'Sum (descendants ([time]. [2, 1997], [time]. [month]), [measures]. [sales count])/count (descendants ([time]. [2, 1997], [time]. [month])'
Select
Crossjoin ([time]. [year]. Members, {[measures]. [sales count], [measures]. [period quantity]})
On columns,
{[Product]. [All products]. [Drink]. [alcoholic beverages], [product]. [All products]. [Drink]. [beverages], [product]. [All products]. [Drink]. [dairy], [product]. [All products]. [Drink], [product]. [All products]. [food]. [baked goods], [product]. [All products]. [food]. [baked goods], [product]. [All products]. [food]. [baking goods], [product]. [All products]. [food]. [breakfast foods], [product]. [All products]. [food]. [canned foods], [product]. [All products]. [food], [product]. [All products]. [non-consumable]. [Chain usel], [product]. [All products]. [non-consumable]. [checkout], [product]. [All products]. [non-consumable]} on rows
From sales

7. Top 10 products sold each year

Select
{[Measures]. [store sales]} on 0,
Generate ([time]. [year]. members, topcount ([time]. [year]. members * [product]. members, 10, [measures]. [store sales]) on 1
From sales

Bytes ---------------------------------------------------------------------------------------------------

Not modified successfully:

Select

Non empty

{

{[Measures]. [sales]}

}

} On columns,

Non empty

{

{Toggledrillstate ({[time]. [All years]. [2003]} * {[product]. [All products]. [classic cars]. [classic metal creations]. [1952 Alpine Renault 1300], [product]. [All products]. [classic cars]. [unimax art galleries]. [1992 Ferrari 360 spider Red]},

{[Time]. [All years]. [2004]} * {[product]. [All products]. [classic cars]. [Second gear diecast]. [2001 Ferrari Enzo], [product]. [All products]. [classic cars]. [unimax art galleries]. [1992 Ferrari 360 spider Red]},

{[Time]. [All years]. [2005]} * {[product]. [All products]. [classic cars]. [unimax art galleries]. [1992 Ferrari 360 spider Red], [product]. [All products]. [Motorcycles]. [Red start diecast]. [2003 Harley-David son Eagle drag bike] }}, {[time]. [All years]. [2, 2003]})}

} On rows

From [steelwheelssales]

Select

Non empty

{

{[Measures]. [sales]}

}

} On columns,

Non empty

{

Union ({toggledrillstate ({[time]. [All years]. [2003]}, {[time]. [All years]. [2003]})}

* {[Product]. [All products]. [classic cars]. [unimax art galleries]. [1992 Ferrari 360 spider Red],

[Product]. [All products]. [classic cars]. [classic metal creations]. [1952 Alpine Renault 1300]},

{[Time]. [All years]. [2004]} * {[product]. [All products]. [classic cars]. [Second gear diecast]. [2001 Ferrari Enzo],

[Product]. [All products]. [classic cars]. [unimax art galleries]. [1992 Ferrari 360 spider Red]}),

{[Time]. [All years]. [2005]} * {[product]. [All products]. [classic cars]. [unimax art galleries]. [1992 Ferrari 360 spider Red], [product]. [All products]. [Motorcycles]. [Red start diecast]. [2003 Harley-David son Eagle drag bike]})

} On rows

From [steelwheelssales]

Top 10 products per year

Sselect

{[Measures]. [sales]} on 0,

Generate ([time]. [years]. members, bottomcount ([time]. [years]. currentmember * [product]. [product]. members, 10, [measures]. [sales]) on 1

From [steelwheelssales]

Customers who have bought both classic cars and motorcycles in 10.2004

Select {[product]. [All products]. [classic cars], [product]. [All products]. [Motorcycles], [product]. [All products]. [planes], [product]. [All products]. [ships], [product]. [All products]. [trains], [product]. [All products]. [trucks and buses], [product]. [All products]. [vintage cars]} on columns,

Generate ([Customers]. [Customer]. Members,

IIF ([product]. [All products]. [classic cars], [measures]. [quantity])> 0 and ([product]. [All products]. [Motorcycles], [measures]. [quantity])> 0,

{[MERs]. [Customer]. currentmember}, {}) on rows

From [steelwheelssales]

Where [time]. [years]. [2004]

10. Comprehensive query of products sold in various countries

Select {[measures]. [quantity], [measures]. [sales]} on columns,

{[Markets]. [country]. Members} on rows

From [steelwheelssales]

Select

{[Measures]. [quantity], [measures]. [sales]} on columns,

{[Markets]. [All markets]. [# null]. [Germany], [markets]. [All markets]. [EMEA]. [Sweden]} on rows

From [steelwheelssales]

11.2004-year growth analysis

With

Member [measures]. [sales increase percent]

'Iif ([measures]. [sales], [time]. currentmember. prevmember)> 0, ([measures]. [sales]-([measures]. [sales], [time]. currentmember. prevmember)/([measures]. [sales], [time]. currentmember. prevmember), null )',

Format_string = '0. 00%'

Member [measures]. [quantity increase percent]

'Iif ([measures]. [quantity], [time]. currentmember. prevmember)> 0, ([measures]. [quantity]-([measures]. [quantity], [time]. currentmember. prevmember)/([measures]. [quantity], [time]. currentmember. prevmember), null )',

Format_string = '0. 00%'

Member [measures]. [last sales]

'([Measures]. [sales], [time]. currentmember. prevmember )'

Member [measures]. [last quantity]

'([Measures]. [quantity], [time]. currentmember. prevmember )'

Select

{[Measures]. [sales], [measures]. [last sales], [measures]. [sales increase percent], [measures]. [quantity], [measures]. [last quantity], [measures]. [quantity increase percent]}

On columns,

Generate ({[time]. [months]. Members}, {[time]. [years]. [2004]})

On rows

From [steelwheelssales]

Select

{[Measures]. [quantity], [measures]. [sales]} on columns,

{Crossjoin ({[time]. [years]. Members}, {[markets]. [All markets]. [Na]. [USA]})} on rows

From [steelwheelssales]