mondrian mdx filter a set on year level in a date dimension

Question

mdx newbie. I have a query which has a date dimension. Years are integers and are in the level CalYear. I'm using Mondrian (via Pentaho)

How do I show years after 2012? The data years with integer values 2012, 2013, 2014. It is a Time dimension; this level is a Year.

If I start from this query:

SELECT
     [Measures].[SalesMonth] ON COLUMNS,
    {[YearMonth.CalYearMonth].CalYear.members} ON ROWS
    FROM [mt_report]

I get a result of three rows.

I try this:

SELECT
    [Measures].[SalesMonth] ON COLUMNS,
    Filter({[YearMonth.CalYearMonth].CalYear.members},
[YearMonth.CalYearMonth].CalYear.CurrentMember.Value > 0) ON ROWS
    FROM [mt_report]

and it returns all data (same as the first query)

but

   SELECT
     [Measures].[SalesMonth] ON COLUMNS,
    Filter({[YearMonth.CalYearMonth].CalYear.members},
[YearMonth.CalYearMonth].CalYear.CurrentMember.Value > 9999) ON ROWS
    FROM [mt_report]

but this also returns the same.

This works ...

Filter({[YearMonth.CalYearMonth].[CalYear].Members},
[YearMonth.CalYearMonth].[CalYear].CurrentMember.name > '2012')

ic3 ic3 · Accepted Answer · 2014-06-15T07:50:50

In MDX you can use the range operator, with a NULL as a second parameter :

If [YearMonth].[2012] is the member for 2012 :

SELECT
  [YearMonth].[Year].[2013]:NULL on 0
...

The same using 2012 and using the nextMember MDX function.

SELECT
  [YearMonth].[Year].[2012].nextMember:NULL on 0
...

mondrian mdx filter a set on year level in a date dimension

1 Answers