开发者

MDX result categorisation

开发者 https://www.devze.com 2023-01-25 03:11 出处:网络
I\'m new to mdx and have been trying to solve the following problem for about a day now. Any help would be appreciated:

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]}) 
0

精彩评论

暂无评论...
验证码 换一张
取 消