pentaho - How to extract month, year and date from Date level in MDX

Question

It is my first time working with MDX in JPivot, I have a Date level (Format: YYYY-MM-dd) and I have to do some queries based on the year or month of this date. I did not find a way to extract this separately.

This is my query :

select {[Person].[job].Members} ON COLUMNS, {[WorkDate].[Date].Members} ON ROWS from [Work]

Is there a way to do it?

Thank you in advance!

question from:https://stackoverflow.com/questions/65910762/how-to-extract-month-year-and-date-from-date-level-in-mdx

Answer 1

If the [Workdate] hierarchy has Year and Month levels, you can access those by using

[Workdate].CurrentMember.Parent.Parent.Name

(assuming Year is the grand-parent level of Date)

or

Ancestor( [Workdate].CurrentMember, [Workdate].[Year]).Name

If you don't have a Year/Month/Date hierarchy then you'll need to break the date string into bits using [Workdate].CurrentMember.Name and string functions.