<div dir="ltr"><div style="font-size:12.8px"><span style="font-size:12.8px"> Hi, All:<br><br> I rewrite this question as below to avoid misunderstanding. <br> <br> 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. </span></div><div style="font-size:12.8px"><span style="font-size:12.8px"> I have following two MDX query and get same result listing all the sum_count for different number.</span></div><div style="font-size:12.8px"><span style="font-size:12.8px"><br></span></div><div style="font-size:12.8px"><span style="font-size:12.8px">SELECT</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">NON EMPTY {[Measures].[sum_count]} ON COLUMNS,</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">NON EMPTY {[number_dim].[count_hiera].[number].Members } ON ROWS</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">FROM [my_cube]</span></div><div style="font-size:12.8px"><span style="font-size:12.8px"><br></span></div><div style="font-size:12.8px"><span style="font-size:12.8px"><br></span></div><div style="font-size:12.8px"><span style="font-size:12.8px">SELECT</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">NON EMPTY {[Measures].[sum_count]} ON COLUMNS,</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">NON EMPTY {</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">[number_dim].[count_hiera].[number].&[1] : [number_dim].[count_hiera].[number].&[100],</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">[number_dim].[count_hiera].[number].&[101] : [number_dim].[count_hiera].[number].&[400],</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">[number_dim].[count_hiera].[number].&[1000] : [number_dim].[count_hiera].[number].&[2000]</span></div><div style="font-size:12.8px"><span style="font-size:12.8px"> } ON ROWS</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">FROM [my_cube]</span></div><div style="font-size:12.8px"><span style="font-size:12.8px"><br></span></div><div style="font-size:12.8px"><span style="font-size:12.8px">number sum_count</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">21 40</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">99 15</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">127 25</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">350 30</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">1380 20</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">1453 20</span></div><div style="font-size:12.8px"><br></div><div style="font-size:12.8px"><span style="font-size:12.8px">----------------</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">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.</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">For example, we can group it to 2, 3, 4 or any number groups. </span></div><div style="font-size:12.8px"><span style="font-size:12.8px"><br></span></div><div style="font-size:12.8px"><span style="font-size:12.8px">------------------</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">number sum_count</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">1-100 55</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">101-400 55</span></div><div style="font-size:12.8px"><span style="font-size:12.8px">1001-2000 40</span></div><div style="font-size:12.8px"><span style="font-size:12.8px"><br></span></div><div style="font-size:12.8px"><span style="font-size:12.8px">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.<br><br></span><span style="font-size:12.8px">I might need some code similar to </span><div style="font-size:12.8px"><a href="http://www.ssas-info.com/analysis-services-faq/27-mdx/915-how-do-i-group-dimension-members-dynamically-in-mdx" target="_blank">http://www.ssas-info.com/analysis-services-faq/27-mdx/915-how-do-i-group-dimension-members-dynamically-in-mdx</a><br><br>Your any help is highly appreciated.<br><br>Sincerely,<br>Selina</div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Mar 15, 2016 at 5:51 PM, Selina Tech <span dir="ltr"><<a href="mailto:swucareer99@gmail.com" target="_blank">swucareer99@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Hi, Tom:<br><br> 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. <br><br>I might need some code similar to <div><a href="http://www.ssas-info.com/analysis-services-faq/27-mdx/915-how-do-i-group-dimension-members-dynamically-in-mdx" target="_blank">http://www.ssas-info.com/analysis-services-faq/27-mdx/915-how-do-i-group-dimension-members-dynamically-in-mdx</a><br><br> Your any help is highly appreciated. <br><br>Sincerely,<br>Selina</div></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Mar 15, 2016 at 5:27 PM, Tom Barber <span dir="ltr"><<a href="mailto:tom@analytical-labs.com" target="_blank">tom@analytical-labs.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><p dir="ltr">Hi</p>
<p dir="ltr">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.</p>
<p dir="ltr">Tom</p>
<div class="gmail_quote"><div><div>On 16 Mar 2016 00:15, "Selina Tech" <<a href="mailto:swucareer99@gmail.com" target="_blank">swucareer99@gmail.com</a>> wrote:<br type="attribution"></div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div><div dir="ltr"><div>Dear All:</div><div> I have a question on "How to dynamically group multiple range on dimension".</div><div><br></div><div> 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.</div><div><br></div><div>SELECT</div><div>NON EMPTY {[Measures].[sum_count]} ON COLUMNS,</div><div>NON EMPTY {[age_dim].[count_hiera].[age].Members } ON ROWS</div><div>FROM [my_cube]</div><div><br></div><div><br></div><div>SELECT</div><div>NON EMPTY {[Measures].[sum_count]} ON COLUMNS,</div><div>NON EMPTY {</div><div>[age_dim].[count_hiera].[age].&[20] : [age_dim].[count_hiera].[age].&[29],</div><div>[age_dim].[count_hiera].[age].&[30] : [age_dim].[count_hiera].[age].&[39],</div><div>[age_dim].[count_hiera].[age].&[40] : [age_dim].[count_hiera].[age].&[49]</div><div> } ON ROWS</div><div>FROM [my_cube]</div><div><br></div><div>age sum_count</div><div>21 40</div><div>25 15</div><div>27 25</div><div>35 30</div><div>38 20</div><div>45 20</div><div>47 10</div><div><br></div><div><br></div><div>----------------</div><div>However I like to get table below which the sum_count can be grouped by range of age dimension. Any Help would be great!</div><div><br></div><div>------------------</div><div>age sum_count</div><div>20-29 80</div><div>30-39 50</div><div>40-49 30</div><div><br></div></div>
<br></div></div>_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org" target="_blank">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" rel="noreferrer" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<br></blockquote></div>
<br>_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org" target="_blank">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" rel="noreferrer" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<br></blockquote></div><br></div>
</div></div></blockquote></div><br></div>