CODEWITHSUNDEEP
mdxpentahomdx-query
wna
wna

Reputation: 1

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

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!

Upvotes: 0

Views: 795

Answers (1)

nsousa
nsousa

Reputation: 4544

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.

Upvotes: 1

Related Questions