我使用SQL Server 2008 R2,我使用SSRS,我使用Adventure Work
数据库。
我为2003年和2004年进入前十的10个城市写了这个MDX查询。
with
set [Best Cities in CY 2003/2004] as
intersect(
order(
topcount(
[Customer].[Customer Geography].[City],
10,
(
[Measures].[Internet Sales Amount],
[Date].[Calendar].[Calendar Year].[CY 2003]
)
),
[Measures].[Internet Sales Amount],
bdesc
),
order(
topcount(
[Customer].[Customer Geography].[City],
10,
(
[Measures].[Internet Sales Amount],
[Date].[Calendar].[Calendar Year].[CY 2004]
)
),
[Measures].[Internet Sales Amount],
bdesc
)
)
Select [Measures].[Internet Sales Amount] on columns,
[Best Cities in CY 2003/2004] on rows
From [Adventure Works]
Where
{
[Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Calendar Year].[CY 2004]
}
但我想列出与前一年相比,互联网销售额下降了35%的城市,以及同年排名前十的城市。
我怎样才能得到这个结果?
您将使用Filter
进行以下操作:
with
set [Best Cities in CY 2003/2004] as
filter(
intersect(
topcount(
[Customer].[Customer Geography].[City],
10,
(
[Measures].[Internet Sales Amount],
[Date].[Calendar].[Calendar Year].[CY 2003]
)
),
topcount(
[Customer].[Customer Geography].[City],
10,
(
[Measures].[Internet Sales Amount],
[Date].[Calendar].[Calendar Year].[CY 2004]
)
)
),
([Measures].[Internet Sales Amount], [Date].[Calendar].[Calendar Year].[CY 2004])
/
([Measures].[Internet Sales Amount], [Date].[Calendar].[Calendar Year].[CY 2003])
- 1.0
< -0.35
)
Select [Measures].[Internet Sales Amount] on columns,
[Best Cities in CY 2003/2004] on rows
From [Adventure Works]
Where
{
[Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Calendar Year].[CY 2004]
}