Implement Existing Keyword

[chubbard]

It's basically impossible to do anything moderately complex without the EXISTING keyword. I can't believe Mondrian is as old as it is and it's never been done.

MDX standard specifies the EXISTING keyword that can be used in WITH MEMBER calculations to use the existing context to limit a sets membership as opposed to using the cube's context. 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]

[lucboudreau]

We're already on it.

https://github.com/pentaho/mondrian/pull/720

[chubbard]

That's great. I'm blocked with out this. When can we try it out?

[lucboudreau]

You can build it right now by cloning this pull request.. and building a jar.

[chubbard]

Looks like it's still an open pull request and the build failed so it looks like it is still waiting to be merged.

[chubbard]

Has this been ported to 4.x? Will it?