Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: LEVEL_NUMBER in Mdx QUery

  1. #1

    Default LEVEL_NUMBER in Mdx QUery

    i'm using an odbo driver implementation ( https://sourceforge.net/projects/xmlaconnect/ by Alexandra Iancu ) to use excel pivot table with mondrian .
    i have tried to use the function "Insert Slicer" but the mdx query generated from excel is wrong for Mondrian .

    with set __XLUniqueNames as { [CUSTOMER_D.CUSTOMER_CLASSECF1_H].[Tutte le Categorie Cliente] }
    set __XLDrilledUp as
    Generate(__XLUniqueNames,
    { IIF([CUSTOMER_D.CUSTOMER_CLASSECF1_H].currentmember.LEVEL_NUMBER <= 2,
    [CUSTOMER_D.CUSTOMER_CLASSECF1_H].currentmember,
    Ancestor([CUSTOMER_D.CUSTOMER_CLASSECF1_H].currentmember,
    [CUSTOMER_D.CUSTOMER_CLASSECF1_H].currentmember.LEVEL_NUMBER - 2)) } )
    member [Measures].__XLPath as
    Generate(
    Ascendants([CUSTOMER_D.CUSTOMER_CLASSECF1_H].currentmember),
    [CUSTOMER_D.CUSTOMER_CLASSECF1_H].currentmember.unique_name,
    "__XLPSEP")
    select { [Measures].__XLPath } on 0,
    __XLDrilledUp on 1
    from [FATTURATO]
    cell properties value

    The error returned from mondrian was :

    The Mondrian XML: Mondrian Error:No function matches signature Member.LEVEL_NUMBER;

    I consulted the documentation of mondrian and the property exists ( http://mondrian.pentaho.com/api/mond...l#LEVEL_NUMBER )

    Is there any way to modify this query so that it work ( in the xmla connector i can define alias of function so i can adapt the mdx query to mondrian ) .
    Alternatively how i should modify the mondrian code to implement this functionality .
    Thanks for any answer .









  2. #2
    Join Date
    Jan 2013
    Posts
    796

    Default

    The way to access properties in Mondrian is with the .Properties() function, like:

    .CurrentMember.Properties("LEVEL_NUMBER")

  3. #3

    Default

    OK it works , but the modification is more convoluted :

    with set __XLUniqueNames as { [CUSTOMER_D.CUSTOMER_CLASSECF1_H].[Tutte le Categorie Cliente] }
    set __XLDrilledUp as
    Generate(__XLUniqueNames,
    { IIF(CAST([CUSTOMER_D.CUSTOMER_CLASSECF1_H].currentmember.Properties("LEVEL_NUMBER") AS NUMERIC ) <= 2,
    [CUSTOMER_D.CUSTOMER_CLASSECF1_H].currentmember,
    Ancestor([CUSTOMER_D.CUSTOMER_CLASSECF1_H].currentmember,
    CAST([CUSTOMER_D.CUSTOMER_CLASSECF1_H].currentmember.Properties("LEVEL_NUMBER") AS NUMERIC ) - 2)) } )
    member [Measures].__XLPath as
    Generate(
    Ascendants([CUSTOMER_D.CUSTOMER_CLASSECF1_H].currentmember),
    [CUSTOMER_D.CUSTOMER_CLASSECF1_H].currentmember.Properties("MEMBER_UNIQUE_NAME"),
    "__XLPSEP")
    select { [Measures].__XLPath } on 0,
    __XLDrilledUp on 1
    from [FATTURATO]
    cell properties value

    thanks

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.