如何使用MDX编写同比和环比
在做BI项目中,用户会经常提出类似下面这样的需求:
1. 本月比上月的销售额增长了多少?百分比是多少?
2. 本季度第一个月比上季度第一个月的销售额增长了多少?百分比是多少?
其实这两个需求就是我们常说的同比和环比问题,它们用关系数据库的SQL语句难于实现,而用BI中的MDX来做却轻而易举。以下例子采用的是SQL Server2005的Analysis Services中的示例库。
环比需求:本月比上个月的销售额增长了多少个百分点?
MDX语句如下:
WITH
MEMBER Measures.
[
Amount Change Rate
]
AS
[
Measures
]
.
[
Internet Sales Amount
]
/
(
[
Measures
]
.
[
Internet Sales Amount
]
,
[
Date
]
.
[
Fiscal
]
.CurrentMember.PrevMember)
-
1
, FORMAT_STRING
=
'
0%
'
SELECT
{
[
Measures
]
.
[
Internet Sales Amount
]
, Measures.
[
Amount Change Rate
]
}
ON
0
,
[
Date
]
.
[
Fiscal
]
.
[
Month
]
.Members
ON
1
FROM
[
Adventure Works
]
结果如下:
上面的输出结果有一个问题,就是第一行数据会发生除0的情况,为了防止这种情况,MDX修改如下:
WITH
MEMBER Measures.
[
Amount Change Rate
]
AS
IIF(ISEMPTY((
[
Measures
]
.
[
Internet Sales Amount
]
,
[
Date
]
.
[
Fiscal
]
.CurrentMember.PrevMember)), "",
[
Measures
]
.
[
Internet Sales Amount
]
/
(
[
Measures
]
.
[
Internet Sales Amount
]
,
[
Date
]
.
[
Fiscal
]
.CurrentMember.PrevMember)
-
1
), FORMAT_STRING
=
'
0%
'
SELECT
{
[
Measures
]
.
[
Internet Sales Amount
]
, Measures.
[
Amount Change Rate
]
}
ON
0
,
[
Date
]
.
[
Fiscal
]
.
[
Month
]
.Members
ON
1
FROM
[
Adventure Works
]
如果发现0为空,则显示空字符串。输出结果如下:
同比需求:本季度每个月比上个季度同期销售额增长了多少个百分点?
WITH
MEMBER Measures.
[
Change Amount Rate
]
AS
[
Measures
]
.
[
Internet Sales Amount
]
/
(
[
Measures
]
.
[
Internet Sales Amount
]
,
Cousin(
[
Date
]
.
[
Fiscal
]
.CurrentMember,
[
Date
]
.
[
Fiscal
]
.CurrentMember.Parent.PrevMember)
)
-
1
, FORMAT_STRING
=
'
0%
'
SELECT
{
[
Measures
]
.
[
Internet Sales Amount
]
, Measures.
[
Change Amount Rate
]
}
ON
0
,
[
Date
]
.
[
Fiscal
]
.
[
Month
]
.Members
ON
1
FROM
[
Adventure Works
]
运行结果如下:
和环比相同,这里也出现了除0的情况,所以把MDX做如下修改:
WITH
MEMBER Measures.
[
Change Amount Rate
]
AS
IIF(
(
[
Measures
]
.
[
Internet Sales Amount
]
,
Cousin(
[
Date
]
.
[
Fiscal
]
.CurrentMember,
[
Date
]
.
[
Fiscal
]
.CurrentMember.Parent.PrevMember)
)=0
, "",
[
Measures
]
.
[
Internet Sales Amount
]
/
(
[
Measures
]
.
[
Internet Sales Amount
]
,
Cousin(
[
Date
]
.
[
Fiscal
]
.CurrentMember,
[
Date
]
.
[
Fiscal
]
.CurrentMember.Parent.PrevMember)
)
-
1
), FORMAT_STRING
=
'
0%
'
SELECT
{
[
Measures
]
.
[
Internet Sales Amount
]
, Measures.
[
Change Amount Rate
]
}
ON
0
,
[
Date
]
.
[
Fiscal
]
.
[
Month
]
.Members
ON
1
FROM
[
Adventure Works
]
1. 本月比上月的销售额增长了多少?百分比是多少?
2. 本季度第一个月比上季度第一个月的销售额增长了多少?百分比是多少?
其实这两个需求就是我们常说的同比和环比问题,它们用关系数据库的SQL语句难于实现,而用BI中的MDX来做却轻而易举。以下例子采用的是SQL Server2005的Analysis Services中的示例库。
环比需求:本月比上个月的销售额增长了多少个百分点?
MDX语句如下:
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
结果如下:
![hb_result.JPG](https://i-blog.csdnimg.cn/blog_migrate/04054d2f7606ab384a770d86f53b3eb5.jpeg)
上面的输出结果有一个问题,就是第一行数据会发生除0的情况,为了防止这种情况,MDX修改如下:
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
如果发现0为空,则显示空字符串。输出结果如下:
![hb_result1.JPG](https://i-blog.csdnimg.cn/blog_migrate/e71012e02624f9957aa2a5b1c67118da.jpeg)
同比需求:本季度每个月比上个季度同期销售额增长了多少个百分点?
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
运行结果如下:
![tb_result.JPG](https://i-blog.csdnimg.cn/blog_migrate/7a07db2f5a7c60eece29fefc7be14690.jpeg)
和环比相同,这里也出现了除0的情况,所以把MDX做如下修改:
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
输出结果如下:
总结同比和环比是BI项目中经常要做得内容,采用MDX语句可以轻松的实现这个需求。