Hi,

I have a question that is going to seem counter-intuitive to the purpose of cubes but I am going to ask it any way.

Let's say I have a very simple cube that has one fact table and two dimension tables.  The fact table has one fact, units sold, and the first dimension table is a time dimension that has several attributes, year, half-year, quarter, month, week and date.  I have a natural user hierarchy on the time dimension table with proper attribute relations, aggregations, etc.  The second dimensional table has product information, SKU and category.  This dimension also has a natural hierarchy of category and SKU. 

Normally, front-end tools allow you to drill down from year, to half-year, quarter, month and finally, week.  You could see total sales by category first for year.  Then you could expand a year and see total sales for half-year, etc.

What I want to do is write a query that will list the categories on the rows and list the aggregates on the columns, i.e. year, half-year, quarter, month and week.

How do I write the MDX query to do this?

In particular, I want the most recent year next to the most recent half-year, next to the most recent quarter, next to the most recent month, next to the most recent week.

Thanks for your help!

Yes, this is exactly what I am looking for.

Your query does work but you've hard-coded the date. My solution below is ugly (and there's likely a cleaner way) but it is a more generic version that works with Adventure Works.

select
   {[Product].[Product Categories].[Category].

The Tail will get the most recent members, and the "1" means it will only get the last one. The filter is necessary if you have dates out into the future.

--Craig