Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Number formatting issues in CDE

  1. #1
    Join Date
    Nov 2014
    Posts
    150

    Default Number formatting issues in CDE

    Hi.


    I've being experiencing some difficulties with number formatting in CDE. By default, the decimal separator is the dot (.) and the thousands separator is the comma (,). The thing is, for my locale, it should be the contrary. But I couldn't change that. For instance, I've changed the language attribute for table component. Everything was translated except for this. In charts I couldn't find a way to change this setting.


    Can anybody help me on this? I'm using Pentaho 6.0.1.


    Thanks in advance.
    Best Regards,

    Hugo Cerqueira
    Business Intelligence / Data Warehouse Analyst

  2. #2
    Join Date
    Jan 2014
    Posts
    189

    Default

    The table addIn uses the library sprintf for formatting numbers whereas the format used by CCC charts is documented at http://www.webdetails.pt/ctools/char...berFormat.html and http://www.webdetails.pt/ctools/char...rmatStyle.html

    To configure the format style globally try adding the following snippet as a resource:
    Code:
    def.configure(pvc.data.format.defaults, {
        number: {
            mask: "#,0.00",
            style: {
                decimal: ",",
                group: "."
            }
        },
        date: "%m %Y"
    });
    which I copied from http://www.webdetails.pt/ctools/char...tProvider.html

  3. #3
    Join Date
    Nov 2014
    Posts
    150

    Default

    Hi, crusso,

    Thanks for the reply. However, I tried adding a javascript resource to the dashboard with this code, and nothing changed. Is there anything else I should do?

    Also, I didn't understand what I have to do to change sprintf behavior, so that the tables present the numbers in my locale.
    Best Regards,

    Hugo Cerqueira
    Business Intelligence / Data Warehouse Analyst

  4. #4
    Join Date
    Jan 2014
    Posts
    189

    Default

    I don't see why it didn't work, the problem is either somewhere on top of the chair or on top of the keyboard.

    I just tested this myself: I added a javascript snippet to a dashboard

    Code:
    def.configure(cdo.format.defaults, { // pvc.data === cdo
        number: {
            mask: "#,0.00",
            style: {
                decimal: "X",
                group: "U"
            }
        }
    });
    and the numbers on the tooltips of CCC charts were formatted accordingly.

    As for the table, my suggestion is that you configure the "formattedText" to use another number formatting library, e.g. the new all-and-mighty Utils.numberFormat, intended to replace the proven-but-old sprintf library. In your table's preExecution put:

    Code:
    this.setAddInOptions("colType", "formattedText", {
      textFormat: function(v, st, opt) {        
        return st.colFormat ? Utils.numberFormat(v, st.colFormat, "pt") : v;
      }
    });

    Note that you will need to use another syntax for number masks, e.g. "#.#,#", so don't forget editing the your dashboard. See https://help.pentaho.com/Documentati...s.numberFormat

    Sadly the mask format is not documented there, but by default it's the same format CCC uses (defined by the cdo / pvc.data module)
    Last edited by crusso; 06-08-2016 at 03:44 AM.

  5. #5
    Join Date
    Nov 2014
    Posts
    150

    Default

    I had no progress so far, until I tried this:

    In the chart pre execution property, I've put the following code:

    Code:
    function() {
        this.chartDefinition.format = {
            number: {
                mask: "#,##0",
                style: {
                    decimal: ",",
                    group: "."
                }
            },
            percent: {
                style: {
                    decimal: ",",
                    group: "."
                }
            }
        };
    }
    And it worked. If I try to adjust the number formatting globally, as you suggested previously, it still doesn't work for numbers. It works for percent numbers, though, which is very odd.

    I'm still struggling with the tables, though. Using the new function didn't work for me, either. I'm trying to find alternatives.
    Best Regards,

    Hugo Cerqueira
    Business Intelligence / Data Warehouse Analyst

  6. #6
    Join Date
    May 2016
    Posts
    2

    Default

    Hello Hugo

    For tables you can use this code in JSResource

    /** start addIn numericBR **/
    var numericBR = {
    name: "numericBR",
    label: "Numeric BR",
    defaults: {
    decimal: true,
    textFormat: function(v, st, opt) {
    var nStr= v.toFixed(2)+'';
    x = nStr.split('.');
    x1 = x[0];
    x2 = x.length > 1 ? ',' + x[1] : '';
    var rgx = /(\d+)(\d{3})/;
    while (rgx.test(x1)) {
    x1 = x1.replace(rgx, '$1' + '.' + '$2');
    }
    return x1 + (opt.decimal ? x2 : '');
    }
    },
    init: function(){
    $.fn.dataTableExt.oSort[this.name+'-asc'] = $.fn.dataTableExt.oSort['numeric-asc'];
    $.fn.dataTableExt.oSort[this.name+'-desc'] = $.fn.dataTableExt.oSort['numeric-desc'];
    },

    implementation: function(tgt, st, opt){
    var text = opt.textFormat.call(this, st.value, st, opt);
    $(tgt).empty().append("<span style='text-align:right;'>"+text+"</span>");
    }

    };
    Dashboards.registerAddIn("Table", "colType", new AddIn(numericBR));
    /** end addIn numericBR **/

    And in the table Column Formats include numericBR


  7. #7
    Join Date
    Nov 2014
    Posts
    150

    Default

    Thanks a lot, roseghi.

    That's exactly what I needed, it's working. I had to create 2 addins, the second one for integer values, and it works as well.
    Best Regards,

    Hugo Cerqueira
    Business Intelligence / Data Warehouse Analyst

  8. #8
    Join Date
    Feb 2016
    Posts
    21

    Default

    Quote Originally Posted by roseghi View Post
    Hello Hugo

    For tables you can use this code in JSResource

    /** start addIn numericBR **/
    var numericBR = {
    name: "numericBR",
    label: "Numeric BR",
    defaults: {
    decimal: true,
    textFormat: function(v, st, opt) {
    var nStr= v.toFixed(2)+'';
    x = nStr.split('.');
    x1 = x[0];
    x2 = x.length > 1 ? ',' + x[1] : '';
    var rgx = /(\d+)(\d{3})/;
    while (rgx.test(x1)) {
    x1 = x1.replace(rgx, '$1' + '.' + '$2');
    }
    return x1 + (opt.decimal ? x2 : '');
    }
    },
    init: function(){
    $.fn.dataTableExt.oSort[this.name+'-asc'] = $.fn.dataTableExt.oSort['numeric-asc'];
    $.fn.dataTableExt.oSort[this.name+'-desc'] = $.fn.dataTableExt.oSort['numeric-desc'];
    },

    implementation: function(tgt, st, opt){
    var text = opt.textFormat.call(this, st.value, st, opt);
    $(tgt).empty().append("<span style='text-align:right;'>"+text+"</span>");
    }

    };
    Dashboards.registerAddIn("Table", "colType", new AddIn(numericBR));
    /** end addIn numericBR **/

    And in the table Column Formats include numericBR

    Hi roseghi,

    Can I change the color of the text using this js? If yes, can I change it according to the value?
    Ex.:
    <0.9 'red';
    <1.0 'yellow';
    >1.0 'green';

    Thanks,

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.