在MDX中获取父级子级中当前成员的级别x值
问题描述:
在名为Employees的维度中显示员工父子级别,该维度显示组织的管理结构。这个层次结构是[Employees]。[管理]。 还有另一个层次结构,列出组织的所有员工。这是一个单一的层次结构,它是[雇员]。[所有员工]。在MDX中获取父级子级中当前成员的级别x值
我有一个查询,看起来像这样:
With
Member measures.[FullTimeSalary] as measures.[Salary] * measures.[FullTimeFactor]
Select {measures.[FullTimeSalary]} on 0,
Non empty
{
[Employess].[All Employees].[All].Children
}
On 1
From MyCube
Where ([Time].[Month].&[201501])
现在,如果我扩大父子层次结构(在[员工] [管理]层次。)我可以看到每个不同层次的这个结构([等级02],[等级03],[等级04]等)以及我现在需要做的是创建一个新的计算度量值measure [SupervisingManager],它将当前成员值恢复到[等级03]的层次结构。
我已经试过
member measures.[SupervisingManager] as [Employees].[Managerial].[Level 03].currentmember.member_name
但只是返回 “#错误”,并使用
member measures.[SupervisingManager] as [Employees].[Managerial].currentmember.member_name
返回该currentmember。我也试验过
measures.[SupervisingManager] as [Employees].[Managerial].currentmember.parent.member_name
但这个问题是,当前成员可以位于层次结构中的任何位置。我能想到的唯一方法是做一个大规模的case语句,获取当前成员的序号值并使用合适的.parent.parent逻辑。有没有更好的方法来做到这一点?
答
可能沿着这些路线的东西会帮助:
WITH
MEMBER measures.[FullTimeSalary] AS
measures.[Salary] * measures.[FullTimeFactor]
MEMBER measures.[SupervisingManager] AS
IIF
(
[Employees].CurrentMember.Parent.Level.Name = 'Level 03'
,[Employees].CurrentMember.Parent.Member_Caption
,'n/a'
)
SELECT
{
measures.[FullTimeSalary]
,measures.[SupervisingManager]
} ON 0
,NON EMPTY
{[Employess].[All Employees].[All].Children} ON 1
FROM MyCube
WHERE
[Time].[Month].&[201501];