[Mondrian] How to dynamically group multiple range on dimension

[Wright, Jeff]

Are you just missing Aggregate()? Something like

With member [number_dim].[lt100] as ‘Aggregate(
  [number_dim].[All number_dims].[1] : [number_dim].[All number_dims].[100] )’
Member [number_dim].[lt400] as ‘Aggregate(
  [number_dim].[All number_dims].[101] : [number_dim].[All number_dims].[400] )’
Member [number_dim].[lt2000] as ‘Aggregate(
  [number_dim].[All number_dims].[1001] : [number_dim].[All number_dims].[2000] )’
SELECT
NON EMPTY {[Measures].[sum_count]} ON COLUMNS,
NON EMPTY { [number_dim].[lt100], [number_dim].[lt400], [number_dim].[lt2000] } ON ROWS
FROM [my_cube]

--jeff

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On Behalf Of Selina Tech
Sent: Tuesday, March 15, 2016 9:24 PM
To: Mondrian developer mailing list <mondrian at pentaho.org>
Subject: Re: [Mondrian] How to dynamically group multiple range on dimension

 Hi, All:

 I rewrite this question as below to avoid misunderstanding.

 I have a dimension called number_dim which is a random number from 0 to MAX integer and a measure called sum_count which is sum of all count.
 I have following two MDX query and get same result listing all the sum_count for different number.

SELECT
NON EMPTY {[Measures].[sum_count]} ON COLUMNS,
NON EMPTY {[number_dim].[count_hiera].[number].Members } ON ROWS
FROM [my_cube]


SELECT
NON EMPTY {[Measures].[sum_count]} ON COLUMNS,
NON EMPTY {
[number_dim].[count_hiera].[number].&[1] : [number_dim].[count_hiera].[number].&[100],
[number_dim].[count_hiera].[number].&[101] : [number_dim].[count_hiera].[number].&[400],
[number_dim].[count_hiera].[number].&[1000] : [number_dim].[count_hiera].[number].&[2000]
 } ON ROWS
FROM [my_cube]

number   sum_count
21           40
99           15
127         25
350         30
1380       20
1453       20

----------------
I was asked to get table below which the sum_count can be grouped by range of number_dim dimension. the group number could be changed at MDX.
For example, we can group it to 2, 3, 4 or any number groups.

------------------
number   sum_count
1-100       55
101-400     55
1001-2000   40

With my understanding, I can not add a new level on number_dim to group by level, because the number of groups could be changed, and how to mapping number to group is dynamical.

I might need some code similar to
http://www.ssas-info.com/analysis-services-faq/27-mdx/915-how-do-i-group-dimension-members-dynamically-in-mdx

Your any help is highly appreciated.

Sincerely,
Selina

On Tue, Mar 15, 2016 at 5:51 PM, Selina Tech <swucareer99 at gmail.com<mailto:swucareer99 at gmail.com>> wrote:
Hi, Tom:

         your reply was very encouraging. However I might mislead this question. The age dimension is just an example. I deal with a dimension with integer could be any number from 0 to MAX of integer. I am asked to group integer by 5 groups.  I need to dynamically to group them.

I might need some code similar to
http://www.ssas-info.com/analysis-services-faq/27-mdx/915-how-do-i-group-dimension-members-dynamically-in-mdx

       Your any help is highly appreciated.

Sincerely,
Selina

On Tue, Mar 15, 2016 at 5:27 PM, Tom Barber <tom at analytical-labs.com<mailto:tom at analytical-labs.com>> wrote:

Hi

I'd normally create another "bucket" column in my dimension that is populated by the value you want 20-29 or whatever. Then just create a 2nd hierarchy in the schema that uses that column instead of the actual year.

Tom
On 16 Mar 2016 00:15, "Selina Tech" <swucareer99 at gmail.com<mailto:swucareer99 at gmail.com>> wrote:
Dear All:
  I have a question on "How to dynamically group multiple range on dimension".

 I have a dimension called age_dim and a measure called sum_count which is sum of all count.  I have following two MDX query and get same result listing all the sum_count for different ages.

SELECT
NON EMPTY {[Measures].[sum_count]} ON COLUMNS,
NON EMPTY {[age_dim].[count_hiera].[age].Members } ON ROWS
FROM [my_cube]


SELECT
NON EMPTY {[Measures].[sum_count]} ON COLUMNS,
NON EMPTY {
[age_dim].[count_hiera].[age].&[20] : [age_dim].[count_hiera].[age].&[29],
[age_dim].[count_hiera].[age].&[30] : [age_dim].[count_hiera].[age].&[39],
[age_dim].[count_hiera].[age].&[40] : [age_dim].[count_hiera].[age].&[49]
 } ON ROWS
FROM [my_cube]

age   sum_count
21     40
25     15
27     25
35     30
38     20
45     20
47     10


----------------
However I like to get table below which the sum_count can be grouped by range of age dimension. Any Help would be great!

------------------
age   sum_count
20-29     80
30-39     50
40-49     30


_______________________________________________
Mondrian mailing list
Mondrian at pentaho.org<mailto:Mondrian at pentaho.org>
http://lists.pentaho.org/mailman/listinfo/mondrian

_______________________________________________
Mondrian mailing list
Mondrian at pentaho.org<mailto:Mondrian at pentaho.org>
http://lists.pentaho.org/mailman/listinfo/mondrian


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20160316/e305e1e9/attachment-0001.html