# [Mondrian] How to dynamically group multiple range on dimension

Wright, Jeff jeff.s.wright at truvenhealth.com
Wed Mar 16 08:00:03 EDT 2016

```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
```

More information about the Mondrian mailing list