MDX result categorisation
I'm new to mdx and have been trying to solve the following problem for about a day now. Any help would be appreciated:
My Query:
select {[Measures].[Kunden]} ON COLUMNS,
NON EMPTY Hierarchize(Union({CurrentDateMember([dimZeit], "[\di\mZeit]
\.[yyyy]"), CurrentDateMember([dimZeit], "[\di\mZeit]\.[yyyy]").Children},
CurrentDateMember([dimZeit], "[\di\mZeit]\.[yyyy]\.[q]").Children))) ON ROWS
FROM Center
which gives the following result, as expected:
Zeit Kunden
2010 1561
- Q1 523
- Q2 470
- Q3 256
- Q4 312
- Nov. 312
Now, what I want to achieve is to split the column 'Kunden' into columns 'Kunden < 5 min' and 'Kunden > 5min' which means customers who have waited for开发者_开发百科 less or more than 5 minutes.
The closest I could get was the following:
WITH
MEMBER [Measures].[LT5] AS
Aggregate(
Filter([Measures].[Kunden], [Measures].[Wartezeit] < 3000))
select {[Measures].[LT5]} ON COLUMNS,
NON EMPTY Hierarchize(Union({CurrentDateMember([dimZeit], "[\di\mZeit]\.[yyyy]"),
CurrentDateMember([dimZeit], "[\di\mZeit]\.[yyyy]").Children},
CurrentDateMember([dimZeit], "[\di\mZeit]\.[yyyy]\.[q]").Children)) ON ROWS
FROM Center
The result is:
Zeit Kunden
2010 -
- Q1 75
- Q2 23
- Q3 86
- Q4 71
- Nov. 71
I understand the cause for this is, because the aggregated [Measure].[Wartezeit] for the whole year 2010 is above 3000 seconds. But I'd like to see the amount of customers with a waiting time below 3000 seconds, so it should be 75+23+86+71 = 255 for 2010.
Solved it by creating a degenerated dimension on the wartezeit column that looks like this in mondrian:
<Dimension name="dauer">
<Hierarchy hasAll="true">
<Level name="dauer" column="dauer" uniqueMembers="true">
<KeyExpression>
<SQL dialect="generic">
<![CDATA[(case when dauer < 300 then 'LT5'
when dauer < 600 then 'LT10'
else 'GT60'
end)]]></SQL>
</KeyExpression>
</Level>
</Hierarchy>
</Dimension>
The select clause of my query is now a simple crossjoin:
({[Measures].[Kunden]} * {[dauer].[LT5], [dauer].[LT10], [dauer].[GT60]})
精彩评论