KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Set thousandseparator on excel report? #187

Open dimasmaliq opened this topic on on Jan 9, 2018 - 8 comments

dimasmaliq commented on Jan 9, 2018

can we set thousandseparator to "." on excel exporting? the default thousandseparator "," changed the value of my data.

here is my pivot table, the data should be looks like this (see grand total)

dimasmaliq commented on Jan 11, 2018

up :/

David Winterburn commented on Jan 11, 2018

Hi there,

To do that, before saving your data to a data store, please change the column meta like this:

$node->pipe(new ColumnMeta(array(
      "PNS"=>array(
        "type" => "number",
	"thousandSeparator" => ",",
	"decimalPoint" => ".",
	"decimals" => 0,
      ),
    )))->pipe($this->dataStore('myDataStore'));

Please let us know if this works for you. Thanks!

dimasmaliq commented on Jan 12, 2018

"PNS" and "Non PNS" isn't a column, it was value of "status" column in my pivot table:

->pipe(new Pivot(array('dimensions'=>array('column'=>'status','row'=>'lingkup,'),'aggregates'=>array('sum'=>'jml'))))
->pipe($this->dataStore("bpk"));
David Winterburn commented on Jan 17, 2018

HI there,

In the case of pivot, just change the column meta of the aggregated/sum column before processing with pivot. Then in the the pivot table that format will be used:

$node->pipe(new ColumnMeta(array(
      "jml"=>array(
        "type" => "number",
	"thousandSeparator" => ",",
	"decimalPoint" => ".",
	"decimals" => 0,
      ),
    )))
->pipe(new Pivot(array('dimensions'=>array('column'=>'status','row'=>'lingkup,'),'aggregates'=>array('sum'=>'jml'))))
->pipe($this->dataStore("bpk"));
dimasmaliq commented on Jan 17, 2018

its works on excel, but its changes the thousand separator on my web reporting too. how to set difference thousand separator between my web reporting and my excel exports?

David Winterburn commented on Jan 18, 2018

Well, if you use the same result datastore for both web and excel export then the format certainly is the same. The simplest way you could do is to create 2 datastores with different meta like this:

$node->pipe(new ColumnMeta(array(
      "jml"=>array(
        "type" => "number",
	"thousandSeparator" => ",",
	"decimalPoint" => ".",
	"decimals" => 0,
      ),
    )))
->pipe(new Pivot(array('dimensions'=>array('column'=>'status','row'=>'lingkup,'),'aggregates'=>array('sum'=>'jml'))))
->pipe($this->dataStore("bpkExcel"));

$node->pipe(new Pivot(array('dimensions'=>array('column'=>'status','row'=>'lingkup,'),'aggregates'=>array('sum'=>'jml'))))
->pipe($this->dataStore("bpkWeb"));

Then in the excel export file, use the bpkExcel for exporting instead of bpkWeb. Please try this and let us know if there's a problem. Thanks!

dimasmaliq commented on Jan 18, 2018

why didn't I think of that :" thank you very much

dimasmaliq commented on Jan 19, 2018

sorry guys, i have another question. is it possible to set thousandseparator on 'subtotal' column on pivot? if not, is it possible to hide this 'subtotal' column?

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
help needed
solved

Excel