First off, if there's a more appropriate place for this question,
please let me know
I'm having a bit of trouble constructing an MDX
query to satisfy my (client's) needs...
What I want to do is group a dimension's members on-the-fly in some
flexible way. Suppose, for example, we have a standard query like
this:
Code:
SELECT
NON EMPTY {[Measures]} ON COLUMNS,
NON EMPTY {[MyDim]} ON ROWS
FROM [Cube]
And this returns the expected result set:
Code:
MyDim Measure1 Measure2
1 12 46
2 13 65
3 15 34
4 17 98
5 11 24
6 10 99
7 4 12
8 22 33
Now what I really want to do is "group" these MyDim values arbitrarily
- say in groups of 3 - and have this returned:
Code:
MyDim Measure1 Measure2
1-3 40 145
4-6 38 221
7-8 26 45
The important thing is that the grouping could be 3, 2, 8 ... anything
(user specified), so I don't really want to name aggregate members
like this:
with member [1-3] as 'Aggregate([MyDim].[1]:[MyDim].[3])
with member [4-6] as 'Aggregate([MyDim].[4]:[MyDim].[6])
....or whatever the right syntax is
What I'd like is something like this:
Code:
with set [Grouped] as 'Group([MyDim],[MyDim].CurrentMember/3)'
SELECT
NON EMPTY {[Measures]} ON COLUMNS,
NON EMPTY {[Grouped]} ON ROWS
FROM [Cube]
.... even if that (probably) gives me something like this:
Code:
Grouped Measure1 Measure2
1 40 145
2 38 221
3 26 45
..... that would be close enough
What's my best plan of attack for this? Do I have a hope in h*ll of
getting this?
Many thanks in advance for any help y'all can provide.
Antony.
MDX Query Help -
08-24-2009
, 06:52 PM
Linear Mode
