Analysis Service计算[期间增长率]的若干注意
定义:[期间增长率]=([本期间度量值]-[上一个期间度量值])/
1。Analysis Service使用计算来实现[期间增长率]的计算
直接双击,可以得到一个计算值[期间增长]表达式如下:
Case
// Test for current coordinate being on (All) member.
When [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember.Level Is
[<<Target Dimension>>].[<<Target Hierarchy>>].[(All)]
Then "NA"
// Test to avoid division by zero.
When IsEmpty
(
(
ParallelPeriod
(
[<<Target Dimension>>].[<<Target Hierarchy>>].[<<Target Level>>],
<<Number of Periods>>,
[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember
),
[Measures].[<<Target Measure>>]
)
)
Then Null
Else (
( [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember,
[Measures].[<<Target Measure>>] )
-
(
ParallelPeriod
(
[<<Target Dimension>>].[<<Target Hierarchy>>].[<<Target Level>>],
<<Number of Periods>>,
[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember
),
[Measures].[<<Target Measure>>]
)
)
/
(
ParallelPeriod
(
[<<Target Dimension>>].[<<Target Hierarchy>>].[<<Target Level>>],
<<Number of Periods>>,
[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember
),
[Measures].[<<Target Measure>>]
)
End
// This expression evaluates the difference between the value of the numeric
// expression in the previous period and that of the current period, as a
// percentage of the previous period's value.
// Test for current coordinate being on (All) member.
When [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember.Level Is
[<<Target Dimension>>].[<<Target Hierarchy>>].[(All)]
Then "NA"
// Test to avoid division by zero.
When IsEmpty
(
(
ParallelPeriod
(
[<<Target Dimension>>].[<<Target Hierarchy>>].[<<Target Level>>],
<<Number of Periods>>,
[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember
),
[Measures].[<<Target Measure>>]
)
)
Then Null
Else (
( [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember,
[Measures].[<<Target Measure>>] )
-
(
ParallelPeriod
(
[<<Target Dimension>>].[<<Target Hierarchy>>].[<<Target Level>>],
<<Number of Periods>>,
[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember
),
[Measures].[<<Target Measure>>]
)
)
/
(
ParallelPeriod
(
[<<Target Dimension>>].[<<Target Hierarchy>>].[<<Target Level>>],
<<Number of Periods>>,
[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember
),
[Measures].[<<Target Measure>>]
)
End
// This expression evaluates the difference between the value of the numeric
// expression in the previous period and that of the current period, as a
// percentage of the previous period's value.
2。不要直接计算[期间增长率],因为如果直接计算[期间增长率
在使用Reporting Service时,在聚合的时候会可能有问题。比如小计的时候也可
所以我一般计算[上一个期间度量值],在Reporting Service里面使用
期间增长率=(Sum(本期间度量值)-sum
3。期间增长,一般针对[年],也可以针对其他的区间
4。[期间增长率]针对单个[度量值]
5。[期间增长率]针对[时间维度]的一个[层次结构]
图中的1号层次结构可以兼容2号,就是说,只要在公式中定义了1号层次结构,2号结构也可以在计算中使用。
6。时间维度是一个特殊的维度,需要单独定制和指定。
这又向导可以使用,另外,关于时间维度我另外打算写一篇来介绍。
7。我给出我的一个计算的表达式例子。
Case
// Test for current coordinate being on (All) member.
When [UDTime].[LevelNameChs].CurrentMember.Level Is
[UDTime].[LevelNameChs].[(All)]
Then "NA"
// Test to avoid division by zero.
When IsEmpty
(
(
ParallelPeriod
(
[UDTime].[LevelNameChs].[年 名称 中文],
1,
[UDTime].[LevelNameChs].CurrentMember
),
[Measures].[完成额]
)
)
Then Null
Else (
(
ParallelPeriod
(
[UDTime].[LevelNameChs].[年 名称 中文],
1,
[UDTime].[LevelNameChs].CurrentMember
),
[Measures].[完成额]
)
)
End
// Test for current coordinate being on (All) member.
When [UDTime].[LevelNameChs].CurrentMember.Level Is
[UDTime].[LevelNameChs].[(All)]
Then "NA"
// Test to avoid division by zero.
When IsEmpty
(
(
ParallelPeriod
(
[UDTime].[LevelNameChs].[年 名称 中文],
1,
[UDTime].[LevelNameChs].CurrentMember
),
[Measures].[完成额]
)
)
Then Null
Else (
(
ParallelPeriod
(
[UDTime].[LevelNameChs].[年 名称 中文],
1,
[UDTime].[LevelNameChs].CurrentMember
),
[Measures].[完成额]
)
)
End
posted on 2006-09-26 10:20 无为而为-凡事从积极的态度做起 阅读(1907) 评论(2) 编辑 收藏 举报