Search code examples
mdxpentahocdf

Pentaho CDF - MDX query: Showing data between months (parameters)

I have two parameters: 'from month' and 'to month'. I would like to show data between those months. This is my situation:

with member [Measures].[Ordercount Y-1] as '([Year].PrevMember, [Measures].[Ordercount])'
member [Measures].[Growth] as IIF([Measures].[Ordercount Y-1] >0,
[Measures].[Ordercount]/[Measures].[Ordercount Y-1] *100,0)

select {[Measures].[Growth]} ON COLUMNS, 
NON EMPTY {[Year].[" +year+ "]} ON ROWS 
from [Ordercube]

Its a dialchart, I want to show the % of sales compared to last year in combination with a range between months.

In SQL it would be easy: Where month >= frommonth and month <= tomonth. Since you can only slice once in a MDX query I don't know what to do. I hope someone can help me.

Thanks in advance

Solution

  • Actually, you'd find that SQL wouldn't be quite as easy if the months weren't both in the same year :)

    Either way, what you're looking for is something like this:

    select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
NON EMPTY [Markets].Children ON ROWS
from [SteelWheelsSales]
where {([Time].[2003].[QTR1] : [Time].[2004].[QTR2])}

    This query was written against pentaho's data warehouse. I haven't the faintest clue what your data wharehouse looks like so I don't know what to use in the time dimension for your query, but it's the ([Time].[2003].[QTR1] : [Time].[2004].[QTR2]) syntax you're looking for, I think.

    (disclaimer: I'm one of the CDF core developers, but my MDX sucks)

    EDIT: In this particular case (Range Operator Reference) the reference site isn't particularly explicit, but the MSDN reference site for MDX is pretty good, so here's the general MDX Reference Site.