0

I have a dimension with the following levels:

  • Years

  • Months

  • Days

I want to get some data by months regardless of the year, i.e. weddings in January.

If I have:

January 2011 - 43

January 2012 - 20

January 2013 - 30

What I want is:

January - 93

Is it possible?

Thanks

Edit:

I have tried this query (with sales, no weddings):

WITH member [Time].[example] AS 'AGGREGATE({[Time].[Months].[Jan]})'
SELECT
NON EMPTY {Hierarchize({[Measures].[Sales]})} ON COLUMNS,
NON EMPTY {[Time].[example]} ON ROWS
FROM [SteelWheelsSales]

but I only get the first January.

vjsp
  • 1
  • 1

1 Answers1

0

Yes, this depends on how you structured your calendar dimension. In this case, you would have a month string field, and it would have

  1. January
  2. February

... So on... Note that the month string field, does not have the year included.

This field would be aggregatable set in the SSAS dimension. When creating your MDX query you would put month on rows, and weddings on columns. Using this approach you should get your desired result.

So doing the following should get you all January sales :

SELECT
NON EMPTY {[Measures].[Sales]} ON COLUMNS,
NON EMPTY {[Time].[Year] * [Time].[Jan]} ON ROWS
FROM [SteelWheelsSales]
Mez
  • 4,666
  • 4
  • 29
  • 57
  • With [Time].[Months].[Jan] I access the first January. If I use Sum function with [Time].[2011].[Jan], [Time].[2012].[Jan], [Time].[2013].[Jan] I can get the correct result, but if I have new data from 2014, I have to modify the query. Is it possible to get this without specify the years? Something like [Time].[Years].[Jan] or [Time].[all years].[Jan]? – vjsp Jul 08 '14 at 17:37
  • This depends on how the dimension is setup in SSAS - specifically the key columns... Did you use the calendar provided by the wizard, or you did a calendar of your own? Seems like you want a custom one... By doing a custom one, you can group by the denormalised month string values, which in this case is "January"... – Mez Jul 08 '14 at 19:02
  • Really, I'm using Pentaho. I have created my own time dimension. – vjsp Jul 08 '14 at 21:37
  • Aggregating by the string month names should give you the same result though. – Mez Jul 09 '14 at 12:25
  • Thanks, but it doesn´t works. I get this error: MondrianException: Mondrian Error:No function matches signature ' * ' – vjsp Jul 10 '14 at 12:58