<!-- /* Font Definitions */ @font-face {font-family:宋体; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:SimSun; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 680460288 22 0 262145 0;} @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} @font-face {font-family:"/@宋体"; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 680460288 22 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; mso-pagination:none; font-size:10.5pt; mso-bidi-font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:宋体; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-font-kerning:1.0pt;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} /* Page Definitions */ @page {mso-page-border-surround-header:no; mso-page-border-surround-footer:no;} @page Section1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:42.55pt; mso-footer-margin:49.6pt; mso-paper-source:0; layout-grid:15.6pt;} div.Section1 {page:Section1;} -->
1. 计算每个时间段的平均销售价格:
WITH
MEMBER [Measures].[Avg Sales Price] As
[Measures].[Sales] / [Measures].[Quantity]
select {[Measures].[Sales], [Measures].[Quantity],[Measures].[Avg Sales Price]} ON COLUMNS,
{[Time].[All Years].[2003], [Time].[All Years].[2004], [Time].[All Years].[2005]} ON ROWS
from [SteelWheelsSales]
2. 所有年度各个产品销售一览
select CrossJoin ({[Time].[Years].Members},{[Measures].[Sales],[Measures].[Quantity]}) ON COLUMNS,
{[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 ROWS
from [SteelWheelsSales]
3. 计算每年各种产品占销售总额的比例(占有率)
with
member [Measures].[Percent] as
'[Measures].[Sales] / ([Measures].[Sales],[Product].[All Products])',
Format_string = '0.00%'
select
crossJoin([Time].[Years].Members,{[Measures].[Sales],[Measures].[Percent] }) ON COLUMNS,
{[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 ROWS
from [SteelWheelsSales]
4. 计算各时期销售额的环比增长(环比)
member [Measures].[Sales Increase] as
'[Measures].[Sales] - ([Measures].[Sales],[Time].[Years].CurrentMember.PrevMember)'
select
CrossJoin({[Product].[All Products]},{[Measures].[Sales],[Measures].[Sales Increase]})
ON COLUMNS,
{[Time].[Years].Members }
ON ROWS
from [SteelWheelsSales]
5. 计算销售额的同比增长(同比)
with
member [Measures].[SameCompare] as
'[Measures].[Sales] - ([Measures].[Sales],
ParallelPeriod ([Time].[Years],1,[Time].[Years].CurrentMember))'
select
CrossJoin({[Product].[All Products]},{[Measures].[Sales],[Measures].[SameCompare] })
ON COLUMNS,
{Descendants([Time].[Years].CurrentMember,[Time].[Quarters],Self_and_before)}
ON ROWS
from [SteelWheelsSales]
6. 计算产品的受欢迎程度(这里是根据各种产品卖出的数量计算)
with
member [Measures].[Welcome Degree] as
'[Measures].[Quantity] / ([Measures].[Quantity],[Product].CurrentMember.Parent)',
Format_string = '0.00%'
select
crossJoin([Time].[Years].Members,{[Measures].[Sales],[Measures].[Welcome Degree] }) ON COLUMNS,
{[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 ROWS
from [SteelWheelsSales]
7. 阶段平均销售额(量)
--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].CurrentMember,[Time].[Months]),[Measures].[Quantity]) / Count(Descendants([Time].CurrentMember,[Time].[Months]))'
select
crossJoin([Time].[Years].Members,{[Measures].[Quantity],[Measures].[Period Quantity] })
ON COLUMNS,
{[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 ROWS
from [SteelWheelsSales]
8. 每年销售排名前 10 的产品
select
{[Measures].[Sales]} on 0,
generate([Time].[Years].Members,TopCount([Time].[Years].CurrentMember*[Product].[Product].Members,10,[Measures].[Sales])) on 1
from [SteelWheelsSales]
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]
9. 每年销售排名后 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]