Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: MDX: "EXISTING" Keyword -- Need Syntax Help

  1. #1
    Join Date
    May 2011
    Posts
    23

    Default MDX: "EXISTING" Keyword -- Need Syntax Help

    All,

    I'm attempting to create a calculated member and force it to be in the current context of my MDX query. Documentation says that I should be able to do it, but I'm getting syntax errors. Here's my query -- notice the EXSISTING call in the 1st member:

    Code:
      with 
               MEMBER [Measures].[Class Total] as 'Count( EXISTING [Class].Members)'
               MEMBER [Measures].[Net Income] as ([Measures].[Income] - [Measures].[Expense])
               MEMBER [Measures].[Scholarship Average] as  [Measures].[Scholarship] / [Measures].[Scholarship Count]
               select {
                 [Measures].[Class Total],
                 [Measures].[Income],
                 [Measures].[Expense],
                 [Measures].[Scholarship],
                 [Measures].[Scholarship Count],
                 [Measures].[Scholarship Average],
                 [Measures].[Net Income]
               } on Columns
    
               from [Classes]
    
               WHERE (
               [Time].[All Times],
               [Location].[Location 1],
               [Topic].[All Topics],
               [Health Educator].[All Health Educators],
               [Class].[All Classes]
               )
         '
    The code fails when run in BI server's query editor with:

    Code:
    Mondrian Error:No function matches signature 'EXISTING(<Set>)'
    I've tried removing the parentheses altogether, or just replacing them with curly braces, but no luck. Based on This documentation, I should be able to do this.

    Suggestions?

    Thanks!

    John
    Last edited by gordonjl; 07-08-2011 at 08:24 AM.

  2. #2
    Join Date
    May 2011
    Posts
    23

    Default

    I think I fixed this. it appears that Mondrian doesn't implement the EXISTING keyword. Instead, I needed to use EXCLUDEEMPTY (kind of the same, I guess). For posterity, here's the query that works:

    Code:
    with 
               MEMBER [Measures].[Class Total] as 'COUNT(Distinct([Class].Children), EXCLUDEEMPTY)'
               MEMBER [Measures].[Net Income] as ([Measures].[Income] - [Measures].[Expense])
               MEMBER [Measures].[Scholarship Average] as  [Measures].[Scholarship] / [Measures].[Scholarship Count]
               select {
                 [Measures].[Class Total],
                 [Measures].[Income],
                 [Measures].[Expense],
                 [Measures].[Scholarship],
                 [Measures].[Scholarship Count],
                 [Measures].[Scholarship Average],
                 [Measures].[Net Income]
               } on Columns
    
               from [Classes]
    
               WHERE (
               [Time].[All Times],
               [Location].[All Locations],
               [Topic].[Cancer],
               [Health Educator].[All Health Educators],
               [Class].[All Classes]
               )
    A big thank you to Andrew Gauvin and his mad-querying team at http://www.freeportmetrics.com/ for helping me with this!

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

    Default Existing

    You are correct. Mondrian does not currently support EXISTING.

    Log a feature request if it's something you would find useful.

    Julian

  4. #4
    Join Date
    Feb 2011
    Posts
    12

    Default

    I ran into this thread when trying to use the median function with the "existing" keyword, doing something similar to what is described here:
    http://social.msdn.microsoft.com/For...1-efc4538732b0
    In this case, EXCLUDEEMPTY won't help because there is no "count" function being used.

    Is there any other alternative to efficiently return the right set for the median function to work on?

    Thanks,
    -TW

Tags for this Thread

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.