MDX - Combining members of the same dimension

[spuzzi]

How do you combine members of the same dimension, such that each member will appear in a separate column?

For example, in order to display Year and Month in separate columns.

The CrossJoin() function does not allow you to combine members of the same dimension, and the Union() function merges all members into a single column...

 

[issahar]

Hi Spuzzi!

I hope, I understood your question...

After this query I can see all the month columns and after
that all the years

SELECT {Descendants([Time],Month), Descendants([Time],Year)} ON COLUMNS
FROM MyLittleCube

Please, notify me if it helped you.
Issahar

Softwatch LTD

 

[spuzzi]

Your solution gives a separate column for each month and year value, which isn't what I'm looking for (apologies for the vague wording of the original question).

I actually want there to be a column called 'Year', containing rows of year values, and a separate column called 'Month' containing rows of month values. This way it is possible to see the year/month hierarchy.

For example, the table might look as follows:

Year Month Married Single

1997 8 102 243
9 355 123
10 244 56
11 923 144
12 545 991
1998 1 122 9
2 134 35 etc.

Cheeers,
Spuzzi.

 

[issahar]

Hi, Spuzzi!

OK. For this I don't have the solution.
But, but the way, one suggestion I have...
You can do this job programmatically:
If you will get the cells set by months,
every month axes member will have UniqueName property
where it has it's full path, e.g. in my Olap database
Month 2 or year 1997 will have the next UniqueName: "[Time].[1997].[Q1].[2]"
You can easily split this string into array and get the Year value from it..
I use this trick when I want to group montly values in groups of years in my reporting tool...
If you use MDX Sample utility supplied with the Olap installation - you can see member properies by doubleclicking the member in the result grid...


My best regards,
Issahar

I will be interested to get your opinion about these suggestings.

 

[issahar]

One more little thing...
May be this will help you...

Next query will still give you results in the separate columns but after the year column you will get the monthly columns according the order....

SELECT Generate({Descendants([Time],Year)},{[Time].CurrentMember,Descendants([Time].CurrentMember,Month)}) ON COLUMNS
FROM MyLittleCube

My best regards, Issahar