pentaho - How to get distinct values of Year level in MDX?

Question

I have a cube Work with two dimensions :

1. Dimension Date with 3 levels : Day, month and year.
2. Dimension Task with 2 levels : Task A and Task B. I want to show tasks (A and B) by year, the problem is that when I execute my query I got duplicate values of the year. I tried using distinct but it didn't work.

Here is my query :

select {[Date].[Annee].Members} ON COLUMNS, {[Task][Task1].Members, [Task].[Task2].Members} ON ROWS from [Work]

And the result I got is : Execution result

Which is like doing : Select [Date].[1].[1].[2014], [Date].[1].[1].[2015], [Date].[1].[2].[2014], [Date].[1].[2].[2015] and so on

Query

Where the first 1 is the day and the second 1 is the month, it returns results for years of every month and day. That's why I got duplicate years but I can't find how to resolve it.

I just want the results to be more general, I want to have values of tasks done by year.

Thank you in advance!

question from:https://stackoverflow.com/questions/65945789/how-to-get-distinct-values-of-year-level-in-mdx

Answer 1

Waitting for answers