How to change thousand & decimal point separators

Ant Klad commented on Jan 28, 2019

Hi, I tried to set the separators in each column (as described in the forum) like : "ItemSumPrice"=>array(

			"type"=>"number",
			"decimals"=>2,
			"decimalPoint"=>",",
			"thousandSeparator"=>".",
			"cssStyle"=>"text-align:right;width:100px;white-space:nowrap",
            "label"=>"Τζίρος()",
			"footer"=>"sum",
		),

But nothing changed. It still shows the default.

KoolReport commented on Jan 29, 2019

You change "decimalPoint" to "dec_point" and "thousandSeparator" to "thousand_sep"

Ant Klad commented on Jan 29, 2019

Thanks! That worked.

Do you happen to know how I can get the number columns to align on the right? I have tried : "cssStyle"=>"text-align:right;width:80px;white-space:nowrap", but it does not help.

KoolReport commented on Jan 29, 2019

Please try to follow our example Column Align of Table.

Ant Klad commented on Jan 29, 2019

I tried it without success. Although it works with table it does not work with Grid.

Ant Klad commented on Jan 30, 2019

When seting "dec_point" and "thousand_sep" the column contents are sorted as strings instead of numbers and the sorting is wrong since 1.000,70 comes after 8,00 and 5,00. If we have for example the sequence : 1.000,70 8,00 5,00 and sort it we get : 8,00 5,00 1.000,70 because the numbers are sorted as strings.

KoolReport commented on Jan 30, 2019

Agree to this issue of DataTables. This was a known issue but we have not found solution yet.

Ant Klad commented on Jan 30, 2019

However it is unusable for me if I can't have corect separators and filtering. That was the reason I purchased KoolReport in the first place. If there is no solution I will have to ask for a refund.

KoolReport commented on Jan 30, 2019

No worry! We will find solution for you. I have just told dev.team to work on this.

Ant Klad commented on Jan 31, 2019

Do you have new about correct sorting? This is something I need urgently.

KoolReport commented on Jan 31, 2019

Yes we know you are in urgent, our dev.team are working on that. Please give us a little time.

KoolReport commented on Feb 1, 2019

Alright, here is solution, you define your columns as following:

"myNumberColumn"=>array(
    "type"=>"number",
    "formatValue"=>function($value){
        $pad = str_pad(number_format($value,0,"",""),11,"0",STR_PAD_LEFT);
        return "<span style='display:none'>$pad</span>".number_format($value,2,",",".");
    }
)

This will helps to solve the issue with sorting with your number format.

Ant Klad commented on Feb 1, 2019

I tried it but it is still not working properly when a thousands separator exists. It is confused with the thousands separator and It sorts like it is decimal separator. Example : 0,00 5,00 1.125,05 2.078,60 5.244,50 33,75 92,0 134,00

Ant Klad commented on Feb 1, 2019

Also this breaks the sums.

KoolReport commented on Feb 1, 2019

Could you please send me your column in csv files. Please send to support@koolreport.com

KoolReport commented on Feb 1, 2019

We will solve the sum after this

Ant Klad commented on Feb 1, 2019

I have just sent it with title : Data to sort

KoolReport commented on Feb 1, 2019

I have seen your data, I want to ask "Is this data from your database?" As I seen your number store in the format "5.244,50" which technically string type, not number type as the number format should be "5244.50" in database.

Our above solution should work for raw data number input to DataTables.

Ant Klad commented on Feb 1, 2019

This data is as I copied it from the browser and pasted it to excel. I did not export it directly from the db so they are strings. Ofcourse it the db they are numbers as you say.

I would also like to mention that I will need to export the report to xls using the xls export tool, so take this into account.

KoolReport commented on Feb 1, 2019

Hi, please add this as well:

DataTables::create(array(
    ...
    "options"=>array(
                "columnDefs"=>array(
                    array("type"=>"string","targets"=>1),
                ),
            )
));

Please change the "targets" to the position of your column. Remember it is the zero base position, the first column is 0.

Please let me know if it works.

Ant Klad commented on Feb 1, 2019

Thanks! Sorting is working now. The only problem is with the sums now that are NaN,undefined NaN,undefined

KoolReport commented on Feb 1, 2019

Yes we know that, the sum will die, we will find solution for sum. Please give me your current code for changing sum after search of your.

Ant Klad commented on Feb 1, 2019

It's OK I fixed the sums.

My only problem now is that is does not align the numbers to the right.

KoolReport commented on Feb 1, 2019

Great that you could fix the sum. About the align right, you add the "dt-body-right" for className of column definition.

DataTables::create(array(
    ...
    "options"=>array(
                "columnDefs"=>array(
                    array("type"=>"string","targets"=>1,"className"=>"dt-body-right"),
                ),
            )
));

Could you please share the code of your auto sum with search, I think it will benefit others. You may share in another topics of your as well.

Ant Klad commented on Feb 1, 2019

I just tried it but is seems it is not so easy... It did not work. The columns 4,5 remain left aligned.

"columnDefs"=>array(

                array("type"=>"string","className"=>"dt-body-right", "targets"=>4,5),
            ),

KoolReport commented on Feb 1, 2019

It should be "targets"=>array(4,5)

Ant Klad commented on Feb 1, 2019

I tried :

"columnDefs"=>array(
                    array("type"=>"string","className"=>"dt-body-right", "targets"=>array(4,5)),
                ),

but they are still left-alligned

KoolReport commented on Feb 2, 2019

It works with me, not sure what happens. Or if possible, you may create a new css class

.align-right
{
    text-align:right !important;
}

And then you replace the "dt-body-right" to "align-right"

Ant Klad commented on Feb 2, 2019

I found the solution. I removed a "cssStyle" from the line and then it worked. Thanks anyway!

KoolReport's Forum

How to change thousand & decimal point separators #629

Build Your Excellent Data Report