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]

效果如下: