原创

SSAS的MDX中的计算成员和命名集

计算成员和命名集,相当于sql中的变量和with List as(select ...)语句。CTE都是在一个查询里面动态生成对应的对象,供下面的语句使用。


计算成员:

查看cube中已存在的计算成员,以及allmember,或者AddCalculatedMembers


语法:

计算成员。  With member measures.XXXXX as 计算公式, format_string='fixed(保留两位小数)   percent(百分比)等',solve_order=默认0(计算列运行的顺序,有时候计算列有相互影响)


计算成员注意事项:

1.计算成员不能成为另一个计算成员的子成员

2.如果要增加最终表格的列,那么应该把计算成员设置在measures集上

3.如果要增加最终表格的行,类似于给表格最下面加一个总计之类的。应该把计算成员建立在要显示的维度层次下。

    示例:

with member measures.BikePlusOne as [Measures].[Reseller Sales Amount] + 1

member
[Product].[Category].ClothingAndBikes as [Product].[Category].&[4] + [Product].[Category].&[1]

select {[Measures].[Reseller Sales Amount],measures.BikePlusOne} on 0,
{[Product].[Category].allmembers} on 1
from [BF_Cube1]

效果如下

  


命名集:

语法:With set xxxx as 成员集
示例:
with member 
measures.BikePlusOne as [Measures].[Reseller Sales Amount] + 1

member
[Product].[Category].ClothingAndBikes as [Product].[Category].&[4] + [Product].[Category].&[1]

set categories as {[Product].[Category].allmembers}

select {[Measures].[Reseller Sales Amount],measures.BikePlusOne} on 0,
categories on 1
from [BF_Cube1]

效果如下

  

非常重要的一个函数

CurrentMember

返回遍历过程中指定层次结构的当前成员

它的属性有很多,比如

name,成员的名称

uniqueName,返回唯一标识路径

  

 


with
member measures.test as
[Product].[Category].currentmember.name

select{
measures.test
} on 0,
[Product].[Category].[Category].members on 1
from [BF_Cube1]

效果:


IIF函数示例

IIF是一个三元运算符
示例如下:
with
member measures.[Parent Member Name] AS
IIF(
[Product].[Product Categories].CurrentMember.Properties("Level_Number",TYPED(此处是强制转换,真实代码的括号不要)) = 0,
'根节点',[Product].[Product Categories].CurrentMember.Parent.Name
)

member measures.lvNum as [Product].[Product Categories].CurrentMember.Properties("Level_Number")

select
{
(measures.[Parent Member Name]),
measures.lvNum
} on 0,
{
[Product].[Product Categories].AllMembers
} on 1
from [BF_MDX]


效果如下:


    

练习1:显示销售额占父类销售额的百分比

示例:

with
member measures.[Parent Member Name] AS
IIF(
[Product].[Product Categories].CurrentMember.Properties("Level_Number",TYPED) = 0,
'根节点',[Product].[Product Categories].CurrentMember.Parent.Name
)

member measures.lvNum as [Product].[Product Categories].CurrentMember.Properties("Level_Number")

member measures.[Percent of Amount] as
(measures.[Reseller Sales Amount])/([Product].[Product Categories].CurrentMember.Parent,measures.[Reseller Sales Amount]),format_string='percent'

select
{
(measures.[Parent Member Name]),
measures.lvNum,
measures.[Reseller Sales Amount],
measures.[Percent of Amount]
} on 0,
{
[Product].[Product Categories].AllMembers
} on 1
from [BF_MDX]

效果:



练习2:

完善练习1的度量值的iif函数(去掉null和inf)

with
member measures.[Parent Member Name] AS
IIF(
[Product].[Product Categories].CurrentMember.Properties("Level_Number",TYPED) = 0,
'根节点',[Product].[Product Categories].CurrentMember.Parent.Name
)

member measures.lvNum as [Product].[Product Categories].CurrentMember.Properties("Level_Number")

member measures.[Percent of Amount] as
IIF([Product].[Product Categories].CurrentMember.properties('Level_Number',typed) = 0
or measures.[Reseller Sales Amount] = null
,0,
(measures.[Reseller Sales Amount])/([Product].[Product Categories].CurrentMember.Parent,measures.[Reseller Sales Amount])),format_string='percent'

select
{
(measures.[Parent Member Name]),
measures.lvNum,
measures.[Reseller Sales Amount],
measures.[Percent of Amount]
} on 0,
{
[Product].[Product Categories].AllMembers
} on 1
from [BF_MDX]

效果:

练习3,行小计有两种方法计算:

1)

select {
[Measures].[Internet Sales Amount]
} on 0,
non empty{
([Product].[Category].children) * ([Product].[Subcategory].children + [Product].[Subcategory].[All Products])
}on 1
from [BF_MDX]

效果如下:

  

2)使用计算成员,作用域增加新的总计

with 
member [Product].[Subcategory].[All Products].total as
(
[Product].[Subcategory].currentmember.parent
)


select {
[Measures].[Internet Sales Amount]
} on 0,
non empty{
([Product].[Category].children) * ([Product].[Subcategory].children + [Product].[Subcategory].[All Products].total)
}on 1
from [BF_MDX]

效果和第一个一样

 

一些更为强大的扩展

  

示例,取前面4个字符串:

with member measures.testName as
vbamdx!left([Product].[Category].currentmember.name,'4')

select {} on 0,
[Product].[Category].children on 1
from [BF_MDX]

效果如下:

  




正文到此结束
本文目录