March 4, 2011 at 1:00 pm
Hi all. I'm trying to get a query to work in MDX and can't get it to work the way I want it to. I know just enough MDX to get myself into trouble so I'm hoping it is just something obvious that I am overlooking.
I have an analysis services cube that contains site registration and confirmation counts broken down by several sets of demographic information. One of these is age.
The problem is that I am trying to get the results to come out by age range instead of by each age individually. Something like this:
Range Registered Confirmed
< 18 25 18
18 - 25 37 21
26 - 36 53 44
and so on.
My first attempt was:
with set [Under 18] as ([Ages].[Age].&[0] : [Ages].[Age].&[17])
set [18-25] as ([Ages].[Age].&[18] : [Ages].[Age].&[25])
set [26-35] as ([Ages].[Age].&[26] : [Ages].[Age].&[35])
set [36-45] as ([Ages].[Age].&[36] : [Ages].[Age].&[45])
set [46-55] as ([Ages].[Age].&[46] : [Ages].[Age].&[55])
set [56-65] as ([Ages].[Age].&[56] : [Ages].[Age].&[65])
set [Over 65] as ([Ages].[Age].&[66] : [Ages].[Age].&[100])
select non empty (
{[Measures].[Total Registered], [Measures].[Total Confirmed]}
) on Columns,
non empty (
{[Under 18],[18-25],[26-35],[36-45],[46-55],[56-65],[Over 65]}
) on Rows
from Demographics
but this doesn't maintain the ranges; it just gave me
Total Registered Total Confirmed
14 42
15 4 3
16 43
17 107
18 4318
19 18237
20 32935
and so on. I then tried:
with member measures.[Under 18 - Total Registered] as sum([Ages].[Age].&[0] : [Ages].[Age].&[17],[Measures].[Total Registered])
member measures.[Under 18 - Total Confirmed] as sum([Ages].[Age].&[0] : [Ages].[Age].&[17],[Measures].[Total Confirmed])
member measures.[18-25 - Total Registered] as sum([Ages].[Age].&[18] : [Ages].[Age].&[25],[Measures].[Total Registered])
member measures.[18-25 - Total Confirmed] as sum([Ages].[Age].&[18] : [Ages].[Age].&[25],[Measures].[Total Confirmed])
member measures.[26-35 - Total Registered] as sum([Ages].[Age].&[26] : [Ages].[Age].&[35],[Measures].[Total Registered])
member measures.[26-35 - Total Confirmed] as sum([Ages].[Age].&[26] : [Ages].[Age].&[35],[Measures].[Total Confirmed])
member measures.[36-45 - Total Registered] as sum([Ages].[Age].&[36] : [Ages].[Age].&[45],[Measures].[Total Registered])
member measures.[36-45 - Total Confirmed] as sum([Ages].[Age].&[36] : [Ages].[Age].&[45],[Measures].[Total Confirmed])
member measures.[46-55 - Total Registered] as sum([Ages].[Age].&[46] : [Ages].[Age].&[55],[Measures].[Total Registered])
member measures.[46-55 - Total Confirmed] as sum([Ages].[Age].&[46] : [Ages].[Age].&[55],[Measures].[Total Confirmed])
member measures.[56-65 - Total Registered] as sum([Ages].[Age].&[56] : [Ages].[Age].&[65],[Measures].[Total Registered])
member measures.[56-65 - Total Confirmed] as sum([Ages].[Age].&[56] : [Ages].[Age].&[65],[Measures].[Total Confirmed])
member measures.[Over 65 - Total Registered] as sum([Ages].[Age].&[66] : [Ages].[Age].&[100],[Measures].[Total Registered])
member measures.[Over 65 - Total Confirmed] as sum([Ages].[Age].&[66] : [Ages].[Age].&[100],[Measures].[Total Confirmed])
select non empty (
{
[Under 18 - Total Registered], [Under 18 - Total Confirmed],
[18-25 - Total Registered], [18-25 - Total Confirmed],
[26-35 - Total Registered], [26-35 - Total Confirmed],
[36-45 - Total Registered], [36-45 - Total Confirmed],
[46-55 - Total Registered], [46-55 - Total Confirmed],
[56-65 - Total Registered], [56-65 - Total Confirmed],
[Over 65 - Total Registered], [Over 65 - Total Confirmed]
}
) on Columns
from Demographics
Which works but is a bit unwieldy.
I know I can rebuild my Ages dimension with the age ranges as another level in the hierarchy. The problem here is that our various client sites don't agree on what age ranges they want to see and so we need to be able to dynamically build the MDX for it.
Can someone point me to what I'm overlooking? Thanks.
March 4, 2011 at 1:31 pm
Best as I can tell, you're really really close 🙂
What you're looking for is almost a merge of these two approaches - you need a calc members in the Age dimension.
Each calc member will be like
WITH MEMBER [Ages].[Age].[MyRange_1] AS ([Ages].[Age].&[0] : [Ages].[Age].&[17])
Now, the only thing I haven't tested is whether you can use the Range operator (like above) or if you need to actually explicitly call out the members and add them, like [Ages].[Age].&[0] + .....[1] + .....[2] etc, obviously swapping out the .... for the full path.
Then just drop these calc members into your report.
HTH,
Steve.
March 4, 2011 at 1:52 pm
That did it! Yes, I had to build out the entire range as in
with member [Ages].[Age].[Under 18] as ([Ages].[Age].&[0] + [Ages].[Age].&[1] + [Ages].[Age].&[2] + [Ages].[Age].&[3] + [Ages].[Age].&[4] + [Ages].[Age].&[5] + [Ages].[Age].&[6] + [Ages].[Age].&[7] + [Ages].[Age].&[8] + [Ages].[Age].&[9] + [Ages].[Age].&[10] + [Ages].[Age].&[11] + [Ages].[Age].&[12] + [Ages].[Age].&[13] + [Ages].[Age].&[14] + [Ages].[Age].&[15] + [Ages].[Age].&[16] + [Ages].[Age].&[17])
using the : didn't work, but at least it behaves the way I wanted.
Thanks much!
March 7, 2011 at 1:53 pm
OK, one of my coworkers figured it out. The magic word was the aggregate command. This works
with member [Ages].[Age].[Under 18] as aggregate([Ages].[Age].&[0] : [Ages].[Age].&[17])
member [Ages].[Age].[18-25] as aggregate([Ages].[Age].&[18] : [Ages].[Age].&[25])
member [Ages].[Age].[26-35] as aggregate([Ages].[Age].&[26] : [Ages].[Age].&[35])
member [Ages].[Age].[36-45] as aggregate([Ages].[Age].&[36] : [Ages].[Age].&[45])
member [Ages].[Age].[46-55] as aggregate([Ages].[Age].&[46] : [Ages].[Age].&[55])
member [Ages].[Age].[56-65] as aggregate([Ages].[Age].&[56] : [Ages].[Age].&[65])
member [Ages].[Age].[Over 65] as aggregate([Ages].[Age].&[66] : [Ages].[Age].&[100])
select non empty (
{[Measures].[Total Registered], [Measures].[Total Confirmed]}
) on Columns,
non empty (
{[Under 18],[18-25],[26-35],[36-45],[46-55],[56-65],[Over 65]}
) on Rows
from Demographics
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply