Skip to content

Instantly share code, notes, and snippets.

@pstoellberger
Last active August 29, 2015 14:20
Show Gist options
  • Save pstoellberger/fabb68d363772c021307 to your computer and use it in GitHub Desktop.
Save pstoellberger/fabb68d363772c021307 to your computer and use it in GitHub Desktop.
mondrian UDF
For queries like:
select Measures.[Price Range] on columns,
Product.Products.Members on rows
from [Sales Cube]
.....
<CalculatedMember name="Price Range" formatString="#,##0.00 %" formula="PriceRange(Measures.Price)" dimension="Measures" visible="true">
</CalculatedMember>
</Cube>
<UserDefinedFunction name="PriceRange">
<Script language="JavaScript">
function getParameterTypes() {
return new Array(new mondrian.olap.type.NumericType());
}
function getReturnType(parameterTypes) {
return new mondrian.olap.type.StringType();
}
function execute(evaluator, arguments) {
var n = arguments[0].evaluateScalar(evaluator);
return bucket(n);
}
function bucket(n) {
if (n == null ||&#160;typeof n == &quot;undefined&quot;) return null;
else if ( n &lt; 0.3) return &#39;&lt; 0.30&#39;;
else if ( n &gt;= 0.3 &amp;&amp; n &lt; 0.5) return &#39;0.30-0.49&#39;;
else if ( n &gt;= 0.5 &amp;&amp; n &lt; 0.8) return &#39;0.50-0.79&#39;;
else if ( n &gt;= 0.8 &amp;&amp; n &lt; 1) return &#39;0.80-0.99&#39;;
else if ( n &gt;= 1 &amp;&amp; n &lt; 1.3) return &#39;1.00-1.29&#39;;
else if ( n &gt;= 1.3 &amp;&amp; n &lt; 1.5) return &#39;1.30-1.49&#39;;
else if ( n &gt;= 1.5 &amp;&amp; n &lt; 1.8) return &#39;1.50-1.79&#39;;
else if ( n &gt;= 1.8 &amp;&amp; n &lt; 2) return &#39;1.80-1.99&#39;;
else if ( n &gt;= 2 &amp;&amp; n &lt; 2.5) return &#39;2.00-2.49&#39;;
else if ( n &gt;= 2.5 &amp;&amp; n &lt; 3) return &#39;2.50-2.99&#39;;
else if ( n &gt;= 3 &amp;&amp; n &lt; 3.5) return &#39;3.00-3.49&#39;;
else if ( n &gt;= 3.5 &amp;&amp; n &lt; 4) return &#39;3.50-3.99&#39;;
else if ( n &gt;= 4 &amp;&amp; n &lt; 4.5) return &#39;4.00-4.49&#39;;
else if ( n &gt;= 4.5 &amp;&amp; n &lt; 5) return &#39;4.50-4.99&#39;;
else if ( n &gt;= 5 &amp;&amp; n &lt; 5.5) return &#39;5.00-5.49&#39;;
else if ( n &gt;= 5.5 &amp;&amp; n &lt; 6) return &#39;5.50-5.99&#39;;
else if ( n &gt;= 6 &amp;&amp; n &lt; 6.5) return &#39;6.00-6.49&#39;;
else if ( n &gt;= 6.5 &amp;&amp; n &lt; 7) return &#39;6.50-6.99&#39;;
else if ( n &gt;= 7 &amp;&amp; n &lt; 7.5) return &#39;7.00-7.49&#39;;
else if ( n &gt;= 7.5 &amp;&amp; n &lt; 8) return &#39;7.50-7.99&#39;;
else if ( n &gt;= 8 &amp;&amp; n &lt; 9) return &#39;8.00-8.99&#39;;
else if ( n &gt;= 9 &amp;&amp; n &lt; 10) return &#39;9.00-9.99&#39;;
else if ( n &gt;= 10 &amp;&amp; n &lt; 11) return &#39;10.00-10.99&#39;;
else if ( n &gt;= 11 &amp;&amp; n &lt; 12) return &#39;11.00-11.99&#39;;
else if ( n &gt;= 12 &amp;&amp; n &lt; 13) return &#39;12.00-12.99&#39;;
else if ( n &gt;= 13 &amp;&amp; n &lt; 14) return &#39;13.00-13.99&#39;;
else if ( n &gt;= 14 &amp;&amp; n &lt; 15) return &#39;14.00-14.99&#39;;
else if ( n &gt;= 15 &amp;&amp; n &lt; 16) return &#39;15.00-15.99&#39;;
else if ( n &gt;= 16 &amp;&amp; n &lt; 17) return &#39;16.00-16.99&#39;;
else if ( n &gt;= 17 &amp;&amp; n &lt; 18) return &#39;17.00-17.99&#39;;
else if ( n &gt;= 18 &amp;&amp; n &lt; 19) return &#39;18.00-18.99&#39;;
else if ( n &gt;= 19 &amp;&amp; n &lt; 20) return &#39;19.00-19.99&#39;;
else if ( n &gt;= 20 &amp;&amp; n &lt; 30) return &#39;20.00-29.99&#39;;
else return &#39;&gt;= 30&#39;
return null;
}
</Script>
</UserDefinedFunction>
</Schema>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment