1. 计算每个时间段的平均销售价格：
WITH
MEMBER [Measures].[Avg Sales Price] As
'[Measures].[Store Sales] / [Measures].[Sales Count]'
select {[Measures].[Store Sales], [Measures].[Sales Count],[Measures].[Avg Sales Price]} ON COLUMNS,
{[Time].[1997], [Time].[1998]} ON ROWS
from Sales
2. 所有年度各个产品销售一览
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. 计算每年各种产品占销售总额的比例（占有率）
with
member [Measures].[Percent] as
'[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. 计算各时期销售额的环比增长（环比）
with
member [Measures].[Sales Increase] as
'[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、计算产品的受欢迎程度（这里是根据各种产品卖出的数量计算）
with
member [Measures].[Welcome Degree] as
'[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、 阶段平均销售额（量）
--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] as
'Sum(Descendants([Time].[1997],[Time].[Month]),[Measures].[Sales Count]) / Count(Descendants([Time].[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].[Carousel],[Product].[All Products].[Non-Consumable].[Checkout],[Product].[All Products].[Non-Consumable]} ON ROWS
from Sales
7、 每年销售排名前 10 的产品
select
{[Measures].[Store Sales]} on 0,
generate([Time].[Year].Members,TopCount([Time].[Year].Members*[Product].Members,10,[Measures].[Store Sales])) on 1
from Sales
---------------------------------------------------------------------------------------------------
未修改成功：
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-Davidson Eagle Drag Bike]}}, {[Time].[All Years].[2003]})}
} ON ROWS
FROM [SteelWheelsSales]
SELECT
NON EMPTY
{
{ {[Measures].[Sales]}
}
} ON COLUMNS ,
NON EMPTY
{
union ( 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-Davidson Eagle Drag Bike]})
} ON ROWS
FROM [SteelWheelsSales]
每年销售排名后 10 的产品
sselect
{[Measures].[Sales]} on 0,
generate([Time].[Years].Members,BottomCount([Time].[Years].CurrentMember*[Product].[Product].Members,10,[Measures].[Sales])) on 1
from [SteelWheelsSales]
10.2004 年既买过 Classic Cars 又买过 Motorcycles 的客户
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 ,
{[Customers].[Customer].CurrentMember},{})) ON ROWS
from [SteelWheelsSales]
where [Time].[Years].[2004]
10. 各个国家销售产品的综合查询
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 年增长分析
with
member [Measures].[Sales Increase Percent] as
'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] as
'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] as
'([Measures].[Sales],[Time].CurrentMember.PrevMember)'
member [Measures].[Last Quantity] as
'([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]