Uploaded image for project: 'Pentaho Analysis - Mondrian'
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-1180

Add support for the standard MDX Keyword EXISTING

    XMLWordPrintable

    Details

    • Type: Story
    • Status: Closed
    • Severity: Medium
    • Resolution: Fixed
    • Affects Version/s: 3.3.0 GA (4.1.0 GA Suite Release), 3.4.1 GA (4.5.0 GA Suite Release)
    • Fix Version/s: 7.0.0 GA
    • Component/s: None
    • Labels:
      None
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.

      Description

      MDX standard specifies the EXISTING keyword that can be used in WITH MEMBER calculations to use the existing content to limit a sets membership. For example:

      with member [Measures].[Employees] as
      'count( EXISTING [Employee].Members'
      select

      { [Measures].[Employees] }

      on columns,

      { [employmentDate.yearMonth].[2010]:[employmentDate.yearMonth].[2012] }

      on rows
      from [Workforce]

      Now you can work around this little snafu using the Count( {}, EXCLUDEEMPTY ) trick like so:

      with member [Measures].[Employees] as
      'Count(Distinct( [Employee].Members),EXCLUDEEMPTY) - Count(Distinct([Measures].[Terminations]),EXCLUDEEMPTY)'
      select

      {[Measures].[Employees],[Measures].[Head Count],[Measures].[Terminations],[Measures].[New Hires]}

      on columns,

      {[employmentDate.yearMonth].[2010]:[employmentDate.yearMonth].[2012]}

      on rows
      from [Workforce]

      But, not all uses of EXISTING can be replaced by using the Count( {}, EXCLUDEEMPTY ) trick. For example:

      SELECT

      { ([Measures].[Reseller Sales Amount]) }

      ON COLUMNS,
      Generate(

      {[Product].[Category].[Category].Members}

      ,
      TopCount( EXISTING

      {[Product].[Product].[Product].Members}, 5, ([Measures].[Reseller Sales Amount]) ),
      ALL) ON ROWS
      FROM [Step-by-Step]

      Or

      WITH MEMBER [Measures].[Products] AS
      Count( EXISTING {[Product].[Product].[Product].Members}

      )
      MEMBER [Measures].[Reseller Products] AS
      Count(Filter( EXISTING

      {[Product].[Product].[Product].Members}

      , ([Measures].[Reseller Sales Amount]) >= ([Measures].[Internet Sales Amount]) ) )
      SELECT

      { ([Measures].[Products]), ([Measures].[Reseller Products]) }

      ON COLUMNS,

      { [Product].[Category].Members }

      ON ROWS
      FROM [Step-by-Step]

        Attachments

          Activity

            People

            Assignee:
            ldeng Li Deng
            Reporter:
            chubbard Charlie Hubbard
            Votes:
            7 Vote for this issue
            Watchers:
            11 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: