CODEWITHSUNDEEP
mdxcubemondrian
user1984778
user1984778

Reputation: 325

MDX: group by with LastPeriods group by, Mondrian Schema: levelType hours

I'm new to MDX and Mondrian and have two time related questions:

1.)

The MDX command

SELECT NON EMPTY {[Country].[Country].Members} ON COLUMNS, [Time].[2012].[Q1 2012].[2].[2012-02-08]:[Time].[2012].[Q4 2012].[11].[2012-11-08] ON ROWS FROM [MyCube] WHERE {[Measures].[Sales]}

prints the result grouped by days:

2012-02-08 | 2873 | 9829 | ...
2012-02-09 | ...

But I want to define the date range in days and get the result grouped by months:

2012-02 | 34298| ...
2012-03 | ...

2.)

The Mondrian schema documentation lists the time level types TimeYears, TimeQuarters, TimeMonths and TimeDays. Is it possible to define hours too?

Thanks a lot.

Upvotes: 2

Views: 619

Answers (1)

ic3
ic3

Reputation: 7680

1) The range function in MDX returns members of the level you're using. In your case :

 [Time].[2012].[Q1 2012].[2].[2012-02-08]:[Time].[2012].[Q4 2012].[11].[2012-11-08]

You're using days so that's why you're getting all days. Use month instead of days in your range function. In case you do not want the data before the 8th, an option would be using a subselect to filter :

 SELECT
    NON EMPTY {[Country].[Country].Members} ON COLUMNS,
    [Time].[Your month level].members} ON ROWS
 FROM (
     SELECT 
        {[Measures].[Sales]} ON 0,
        [Time].[2012].[Q1 2012].[2].[2012-02-08]:[Time].[2012].[Q4 2012].[11].[2012-11-08] ON 1,
     FROM [MyCube] )

2) Don't know for Mondrian, but in any case you can create a time dimension based on an 'existing' table

Upvotes: 1

Related Questions