[Mondrian] International number formats

[Luc Boudreau]

Hello,

I'm currently working on adding a nice feature to Mondrian; supporting
different formatting for thousand separators for different countries. An
easy example would be the Indian formatting for currency. A ten million
dollars must be displayed as :

$1,00,00,000.00

As you can see, the first 3 whole numbers are grouped, while the remaining
digits are grouped by two.

Mondrian code was applying a modulo of 3 to the position of the digits and
didn't consider the actual position of the thousand separator in the format
string you passed, thus making any such formatting impossible. I found a
nice way to support this, but there are repercussions that I would like to
discuss.

First, the test suite has some measures in the Sales cube that have the
format string "#,#". The old code would interpret this as "no decimals, and
commas to split the thousands every 3 digits". In the new code, that would
be interpreted as "no decimals, a separator every digit". In my opinion, the
new code makes a much better job at interpreting that format string. Once
you get down to really thinking about what #,# means, and you consider other
formats like "#,###", it definitely makes sense.

That "#,#" appears in:

<Measure name="Count" column="employee_id" aggregator="count"
formatString="#,#"/>
<Measure name="Number of Employees" column="employee_id"
aggregator="distinct-count" formatString="#,#"/>

Some tests are coded to look for those format strings.

mondrian.xmla.XmlaCognosTest<eclipse-javadoc:%E2%98%82=mondrian-2.0/testsrc%5C/main%3Cmondrian.xmla%7BXmlaCognosTest.java%E2%98%83XmlaCognosTest>.testCognosMDXSuiteHR_001()

mondrian.xmla.XmlaCognosTest<eclipse-javadoc:%E2%98%82=mondrian-2.0/testsrc%5C/main%3Cmondrian.xmla%7BXmlaCognosTest.java%E2%98%83XmlaCognosTest>.testCognosMDXSuiteHR_002()


I propose to change them to "#,###" and make my implementation of the format
string the official one for Mondrian. Here are some examples of how I would
like the format strings to be interpreted. The base number is 1234567.8.

#,### 1,234,567
######,##,##.00 123,45,67.80
#,# 1,2,3,4,5,6,7
##,##,##,###.# 12,34,567.8


_____________________________
Luc Boudreau

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

[Julian Hyde]

We are caught between a rock and a hard place here. We want to provide this
functionality for Indian users (without it, doing financial analysis is
incredibly difficult -- imagine if you were a US accountant and the amount
12345678 USD was formatted as '$12,34,56,78' -- you'd be really confused),
but we also need to be compatible with current standard MDX behavior,
current Mondrian behavior, and SSAS behavior (which effectively defines
standard MDX behavior).

In SSAS 2005, and current Mondrian, format(1000000, "#,#") returns
"1,000,000". We can't just change that. The impact to existing reports would
be too great otherwise.

(I acknowledge that the VB format function may do otherwise... see for
example <http://www.vbforums.com/showthread.php?t=548012>
http://www.vbforums.com/showthread.php?t=548012 . But SSAS doesn't seem to
implement it, and our first duty is to be faithful to SSAS behavior and
Mondrian's current behavior. If SSAS isn't compatible with VB, that's a
problem for Microsoft's developers!)

I suggest that we use the following rule: we observe use the spacing of the
the format string only if there are two or more commas. Therefore:

* format(1234567, "#,#") should return "1,234,567", as today
* format(1234567, "##,#") should return "1,234,567"
* format(1234567, "#,##,###") should return "12,34,567"
* format(1234567890, "#,##,###") should return "1,23,45,67,890"

Lastly, I recommend that everyone building applications uses the named
format strings such as "Currency", "General Number", "Fixed", "Standard",
"Percent" rather than explicit strings like "#,##,###.00". The named formats
will expand the appropriate format string for the locale, and your app is
more easily localizable.

Julian


_____

From: mondrian-bounces (AT) pentaho (DOT) org [mailto:mondrian-bounces (AT) pentaho (DOT) org] On
Behalf Of Luc Boudreau
Sent: Thursday, July 29, 2010 6:36 AM
To: Mondrian developer mailing list
Subject: [Mondrian] International number formats


Hello,

I'm currently working on adding a nice feature to Mondrian; supporting
different formatting for thousand separators for different countries. An
easy example would be the Indian formatting for currency. A ten million
dollars must be displayed as :


$1,00,00,000.00


As you can see, the first 3 whole numbers are grouped, while the remaining
digits are grouped by two.

Mondrian code was applying a modulo of 3 to the position of the digits and
didn't consider the actual position of the thousand separator in the format
string you passed, thus making any such formatting impossible. I found a
nice way to support this, but there are repercussions that I would like to
discuss.

First, the test suite has some measures in the Sales cube that have the
format string "#,#". The old code would interpret this as "no decimals, and
commas to split the thousands every 3 digits". In the new code, that would
be interpreted as "no decimals, a separator every digit". In my opinion, the
new code makes a much better job at interpreting that format string. Once
you get down to really thinking about what #,# means, and you consider other
formats like "#,###", it definitely makes sense.

That "#,#" appears in:

<Measure name="Count" column="employee_id" aggregator="count"
formatString="#,#"/>
<Measure name="Number of Employees" column="employee_id"
aggregator="distinct-count" formatString="#,#"/>

Some tests are coded to look for those format strings.


mondrian.xmla.XmlaCognosTest
<eclipse-javadoc:%E2%98%82=mondrian-2.0/testsrc%5C/main%3Cmondrian.xmla%7BXm
laCognosTest.java%E2%98%83XmlaCognosTest> .testCognosMDXSuiteHR_001()
mondrian.xmla.XmlaCognosTest
<eclipse-javadoc:%E2%98%82=mondrian-2.0/testsrc%5C/main%3Cmondrian.xmla%7BXm
laCognosTest.java%E2%98%83XmlaCognosTest> .testCognosMDXSuiteHR_002()


I propose to change them to "#,###" and make my implementation of the format
string the official one for Mondrian. Here are some examples of how I would
like the format strings to be interpreted. The base number is 1234567.8.

#,### 1,234,567
######,##,##.00 123,45,67.80
#,# 1,2,3,4,5,6,7
##,##,##,###.# 12,34,567.8


_____________________________
Luc Boudreau



_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian