层次结构和维度属性之间的交叉是可能的吗？

Question

我当前正在使用MDX显示座席分支层次结构的后代，直到座席名称级别：

UNION (
{DESCENDANTS ([Agent].[Agent Branch Hierarchy].[Agent Business Division].&[Retail],5,SELF_AND_BEFORE) }
,{DESCENDANTS([Agent].[Agent Branch Hierarchy].[Agent Business Division].&[Digital],5, SELF_AND_BEFORE)}        
,{DESCENDANTS([Agent].[Agent Branch Hierarchy].[Agent Business Division].&[Telesales],5, SELF_AND_BEFORE)}  
,{DESCENDANTS([Agent].[Agent Branch Hierarchy].[Agent Business Division].&[Head Office],5, SELF_AND_BEFORE)} 
)

但是，我希望在座席名称旁边显示AgentEmployeeNumber。虽然AgentEmployeeNumber不是AgentBranch Hierarchy的一部分，但我似乎找不到一种将两者交叉连接在一起的方法，这可能吗？我在下面提供了代理维度的结构的一个片段：

​

​

Answer 1

您可以对(我假设)行使用交叉联接(简称为*)，如下所示：

SELECT
    {} // add whatever you want, I assume you have measures here
    ON COLUMNS,
    UNION (
        {DESCENDANTS ([Agent].[Agent Branch Hierarchy].[Agent Business Division].&[Retail],5,SELF_AND_BEFORE) }
        ,{DESCENDANTS([Agent].[Agent Branch Hierarchy].[Agent Business Division].&[Digital],5, SELF_AND_BEFORE)}        
        ,{DESCENDANTS([Agent].[Agent Branch Hierarchy].[Agent Business Division].&[Telesales],5, SELF_AND_BEFORE)}  
        ,{DESCENDANTS([Agent].[Agent Branch Hierarchy].[Agent Business Division].&[Head Office],5, SELF_AND_BEFORE)} 
    )
    *
    [Agent].[Agent Employee Number].[Agent Employee Number].Members
ON ROWS
FROM [Your Cube]

缺点是，对于非单一代理级别的Agent Branch Hierarchy成员，您将获得许多号码。绕过这一点的一种方法是计算度量，您可以将其放在列上：

WITH MEMBER Measures.[Employee Number] AS
     IIf(
         (EXISTING [Agent].[Agent Employee Number].[Agent Employee Number].Members).Count = 1,
         (EXISTING [Agent].[Agent Employee Number].[Agent Employee Number].Members).Item(0).Item(0).Name,
         NULL
     )
SELECT
    Measures.[Employee Number] // add whatever you want, I assume you have measures here
    ON COLUMNS,
    UNION (
        {DESCENDANTS ([Agent].[Agent Branch Hierarchy].[Agent Business Division].&[Retail],5,SELF_AND_BEFORE) }
        ,{DESCENDANTS([Agent].[Agent Branch Hierarchy].[Agent Business Division].&[Digital],5, SELF_AND_BEFORE)}        
        ,{DESCENDANTS([Agent].[Agent Branch Hierarchy].[Agent Business Division].&[Telesales],5, SELF_AND_BEFORE)}  
        ,{DESCENDANTS([Agent].[Agent Branch Hierarchy].[Agent Business Division].&[Head Office],5, SELF_AND_BEFORE)} 
    )
ON ROWS
FROM [Your Cube]

成员定义声明或多或少：“如果当前Agent Branch Hierarchy成员的雇员编号集只有一个元素，则将第一个(也是唯一一个)元素的名称作为度量值。请注意，计算的度量值可以返回字符串，并且不一定是数字。