搜索用户层次结构的所有成员-为相应的集合返回上，下层的相关成员 [英] Search AllMembers of User Hierarchy - for corresponding Set return a related members from upper and lower levels

问题描述

我们有一个具有3个级别的user hierarchy(Level1是较高的级别，Level3是较低的级别)

We have a user hierarchy with 3 levels (Level1 being the upper level - Level3 the lower)

使用FILTER函数，我声明了一个字符串，该字符串可以位于任何(或几个)级别的任何成员的member_name之内.这将返回一个集合.

Using the FILTER function I state a string, this string could be within the member_name of any member from any (or several) of the levels. This returns a set.

然后，我希望该层次结构中与集合中每个项目相关的所有成员；上下两层.

I would then like all members from the hierarchy related to each item in the set; both upper and lower levels.

我还尝试过(未成功)在结果中包括始终为Level2的列作为参考，同时还包括排序列.

I've also tried (unsuccessfully) to include a column that is always Level2 in the results as a reference and also an ordering column.

WITH 
    MEMBER [Measures].[LevelName] AS
        [DimensionX].[HierX].Level.Name
    MEMBER [Measures].[LevelNumber] AS
        [DimensionX].[HierX].Level.Ordinal
    MEMBER [Measures].[MemName] AS //<<<<<not working as I want level 2 in the result set
        [DimensionX].[HierX].currentmember.membervalue
    SET [Set_Target] AS
        {
        FILTER(
            [DimensionX].[HierX].AllMembers,
                (
                InStr(
                    1, 
                    [DimensionX].[HierX].currentmember.name, 
                    "jesus") <> 0
                ) 
            )
        }
SELECT
//   ORDER(
//      Exists(
//      [AttributeHierX].members, 
//      [Set_Target]
//      ),
//    [AttributeHierX].[AttributeHierX].MEMBERVALUE
//      )
//*
    ORDER(
        DESCENDANTS(
            [Set_Target],
            [DimensionX].[HierX].[Level1],
            SELF_BEFORE_AFTER
            ),
        [Measures].[LevelNumber],
        BASC
    ) as X
    ON 1,
    {
    [Measures].[MemName], 
    [Measures].[LevelName],
    [Measures].[LevelNumber] 
    } ON 0
FROM [CubeX] 

推荐答案

我想AdventureWorks上的类似查询可以提供您想要的东西:

I suppose a similar query on AdventureWorks delivers what you want:

WITH 
   MEMBER [Measures].[LevelName] AS
       [Employee].[Employee Department].CurrentMember.Level.Name
   MEMBER [Measures].[LevelNumber] AS
       [Employee].[Employee Department].CurrentMember.Level.Ordinal
   MEMBER [Measures].[MemName] AS
       [Employee].[Employee Department].CurrentMember.Name
   SET [Set_TargetEmp] AS
        {
        FILTER(
            [Employee Department].AllMembers,
                (
                InStr(
                    1, 
                    [Employee].[Employee Department].currentmember.name, 
                    "WC4") <> 0
                ) 
            )
        }
SELECT
    {
    [Measures].[MemName], 
    [Measures].[LevelName],
    [Measures].[LevelNumber] 
    } ON 0,
    Hierarchize(
        Generate([Set_TargetEmp] as e,
            Ascendants(e.Current)
            +
            Descendants(e.Current, e.Current.Level, SELF_AND_AFTER)
        )
    )
    ON 1
FROM [Adventure Works] 

我使用Hierarchize按层次对结果进行排序，因为这似乎是检查结果的最简单方法.您可能要更改它.由于-与Descendants方法相反-Ascendants不允许将集合作为第一个参数，因此我使用Generate沿集合迭代.它的默认行为(没有All的第三个参数)，它消除了重复项，我认为这是您需要的行为.

I used Hierarchize to sort the result by hierarchy, as that seemed the most easy way for me to check the result. You may want to change that. As - in contrast to the Descendants method - Ascendants does not allow a set as the first argument, I used Generate to iterate along the set. Its default behavior (without a third argument of All), it eliminates duplicates, which I assumed is the behavior you need.

这篇关于搜索用户层次结构的所有成员-为相应的集合返回上，下层的相关成员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！