Add Financial Formatting to Pivots/Indicators

Comments

11 comments

  • Avatar
    ganesh dixit (Edited )

    How can we remove -ve formatting on  pivot table body and retain it in  grandtotal?

    Please give a resolution on it. I need it urgently.

    Best Regards,

    Ganesh

    1
    Comment actions Permalink
  • Avatar
    Takashi Binns

    Hi Ganesh,

    In order to affect the grand totals only, a small adjustment needs to be made.  Open the negativesFormatter.js file and find the pivotFormatter function.  Replace that function definition with the code below, and it should work.

    // Function to format pivot tables
    function pivotFormatter(widget,event){

    // Get the widget element
    var widgetElement = (prism.$ngscope.appstate == 'dashboard') ? $('widget[widgetid="' + widget.oid + '"]') : $('pivot');

    // Make sure there's at least some default setttings
    if (typeof widget.options[labelProperty] === "undefined") {
    widget.options[labelProperty] = $.extend({},options);
    }

    // loop through the pivot table's value metadata items
    var valueItems = widget.metadata.panel("values").items;
    $.each(valueItems,function(){

    // Get the fidx
    var fidx = this.field.index;

    // Is this value formatted as a currency or percent?
    var isCurrency = (this.format && this.format.mask && this.format.mask.currency),
    isPercent = (this.format && this.format.mask && this.format.mask.percent),
    gtRow = $('td.p-grand-total-row-head',widgetElement).parent();

    // Run the code to show zeros instead of blank cells
    if (widget.options[labelProperty]["Show Zeros"]){

    // Find all the table cells, that have a null value
    /*
    var cells = $('td[fidx=' + fidx + ']:has(.p-value)',widgetElement).filter(function(){
    return $(this).attr('val') === "N\\A" ;
    })
    */
    var cells = $('td[fidx=' + fidx + ']:has(.p-value)',gtRow).filter(function(){
    return $(this).attr('val') === "N\\A" ;
    })

    // Determine the new text to include
    var newText = "0";
    if (isCurrency) {
    newText = "$0";
    } else if (isPercent) {
    newText = "0%";
    }

    // Write in a zero for these cells
    cells.text(newText);
    }

    // Run the code to format data values
    if (widget.options[labelProperty]["Format Negatives"]){

    // Only applies to currency and percentage formatting
    if (isCurrency || isPercent){

    // Find all the table cells, that have a value less than 0
    /*
    var cells = $('td[fidx=' + fidx + ']:has(.p-value)',widgetElement).filter(function(){
    return $(this).attr('val') < 0;
    })
    */
    var cells = $('td[fidx=' + fidx + ']:has(.p-value)',gtRow).filter(function(){
    return $(this).attr('val') < 0;
    })

    // Get a series type
    var sType = isCurrency ? 'currency' : 'percent';

    // Loop through each cell and run the formatter
    $.each(cells, function(){
    valueFormatter(this, sType, $(this).text());
    })

    }
    }
    })
    }

     

    1
    Comment actions Permalink
  • Avatar
    Andrew Block

    Takashi will this plugin affect the performance as far as load times or when changes are made in date selection using this plugin?

    0
    Comment actions Permalink
  • Avatar
    Takashi Binns

    Hi Andrew,

    You shouldn't see any performance issues with this plugin.  It just changes the formatting, after getting the data from the Elasticube.

    -Takashi

    0
    Comment actions Permalink
  • Avatar
    Linh Cao

    Hi Takashi,

    If I add a Columns dimension it will be applied to every measure in the pivot widget.
    I see that in your pivot the year column (2011, 2012) isn't applied to last 3 value.


    Could you please show me how to do this.

    Thank a lot !
    Linh

    0
    Comment actions Permalink
  • Avatar
    Michael Becker

    Hi Linh,

    if you click on the menu for columns section in Pivot, you can turn on Grand Totals for it and it will create what you are seeing on the screen.

    Hope it helps.

     

    Regards,

    Michał

    0
    Comment actions Permalink
  • Avatar
    Linh Cao

    Thanks so much, Michael !

    0
    Comment actions Permalink
  • Avatar
    Aware Manager (Edited )

    Can this be applied to table and table with aggregation widgets? And values that aren't currency (at least for the show zero portion of this solution)?

    0
    Comment actions Permalink
  • Avatar
    Aware Manager

    Also, it appears this plug-in is the cause of some email report errors in 6.7

    0
    Comment actions Permalink
  • Avatar
    Jessica Orlando

    Does anyone have issues with keeping the formatting when downloading to PDF? My negative formatting disappears- it would be great if this was kept while downloading! 

    0
    Comment actions Permalink
  • Avatar
    David Rogers

    Could this be enhanced to include it on regular numeric fields?  We often report variances of non-financial/percentage numbers and prefer to show negatives using parentheses.  Thanks for your consideration.

    0
    Comment actions Permalink

Please sign in to leave a comment.