Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Selecting a known child of an unknown parent

  1. #1
    Join Date
    Nov 2007
    Posts
    15

    Default Selecting a known child of an unknown parent

    Hello.
    I have a simple hierarchy "HIERARCHY" with 2 levels, call them LEVEL1 and LEVEL2.
    I need to write a query that selects some members from LEVEL1, some from LEVEL2, and then hierarchize them.

    For example:
    select
    [Measures].[Cost] on columns,
    hierarchize( { [HIERARCHY].[member1], [HIERARCHY].[member2], [HIERARCHY].[???].[memberX] } ) on rows
    from myCube

    "member1" and "member2" are existing values for LEVEL1.
    "memberX" is an existing value for LEVEL2, child of an unknown member of LEVEL1 ("member1", "member2", or a different member).

    I don't know how to refer to "memberX", what should I put in the mdx instead of the 3 question marks?

    Thanks.

  2. #2
    Join Date
    Nov 1999
    Posts
    1,618

    Default

    Replace

    Code:
    [HIERARCHY].[???].[memberX]
    with

    Code:
    Filter([HIERARCHY].LEVEL2.Members, [HIERARCHY].CurrentMember.Name = "memberX")
    Julian

  3. #3
    Join Date
    Nov 2007
    Posts
    15

    Default

    Thanks for your reply, your solution has worked.

    But...

    I tried to improve my query to make it return not only the LEVEL2 member, but its ascendant too.
    Here is the new query:
    Code:
    select
    [Measures].[Cost] on columns,
    hierarchize(
      ascendants(
        Filter(
          [HIERARCHY].LEVEL2.Members,
          [HIERARCHY.CurrentMember.Name = "memberX"
        ).item(0)  //I know memberX is unique
      )
    )
    on rows
    from myCube
    It almost works. The user sees all the hierarchy that brings to my "memberX" member, with all levels already drilled down, but drilling up and down any level of the hierarchy lets the user see every member in every level and not only "memberX", and I'd like to avoid it.
    How can I prevent the user from seeing members he hasn't the rights to access to?

    Thanks.

  4. #4
    Join Date
    Nov 2007
    Posts
    15

    Default

    I'm working to find a solution to the last problem I proposed.
    I haven't found the right way yet, but I'm progressing a bit.

    I'm trying to create a mock LEVEL1 member which contains every LEVEL2 members I need.
    Here's the query:
    Code:
    with member [HIERARCHY].[MOCK_LEVEL1] as
      aggregate( //I'm aggregating because in the real problem I have more than one "memberX"
        Filter(
          [HIERARCHY].LEVEL2.Members,
          [HIERARCHY.CurrentMember.Name = "memberX"
        )
      )
    select
    [Measures].[Cost] on columns,
    [HIERARCHY].[MOCK_LEVEL1],
    Filter(
      [HIERARCHY].LEVEL2.Members,
      [HIERARCHY.CurrentMember.Name = "memberX"
    )
    on rows
    from myCube

    Doing this way, the resulting table shows MOCK_LEVEL1 having a Cost measure as the sum of all my filtered LEVEL2 members, and under it there are all my LEVEL2 members I need to show.
    It seems to work fine but it doesn't: the engine doesn't know that MOCK_LEVEL1 is the parent of all my defined LEVEL2 members; this means that when I drill down any level from the jpivot table, the order of the members is not mantained and it's possibile that MOCK_LEVEL1 is shown after the LEVEL2 members of which it should be the parent of.

    It just seems the tale isn't over yet..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.