# Number formatting in Reports (Ignition 8.1.2)

**URL:** <https://forum.inductiveautomation.com/t/number-formatting-in-reports-ignition-8-1-2/93524>\
**Category:** Ignition\
**Tags:** ignition81, reporting\
**Created:** [July 22, 2024, 11:38am UTC](https://forum.inductiveautomation.com/t/number-formatting-in-reports-ignition-8-1-2/93524 "2024-07-22T11:38:39Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Olli\_Burr](https://avatars.discourse-cdn.com/v4/letter/o/9de0a6/32.png) [@Olli\_Burr](https://forum.inductiveautomation.com/u/Olli_Burr)\
**Post date:** [July 22, 2024, 11:38am UTC](https://forum.inductiveautomation.com/t/number-formatting-in-reports-ignition-8-1-2/93524/1 "2024-07-22T11:38:39Z")

</div>

Hi everyone,

I'm facing an issue with the "Reports" module in Ignition Perspective 8.1.2. I need to create a report that uses the German number format for billing purposes --\> "2.500,00 €" (German standard) instead of "2,500.00 €" (US standard).

To set the number format for a particular TextShape to German, I've been using the **Properties** tab and selecting **Number Format** as "`#,##0.00;(#,##0.00`)" (as highlighted in blue in the attached image).

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/2/e2ec37920768c1d02755b5106b8ffe893b615fa4.png)

However, when the report is created, it reverts back to the US format. This is because our Gateway is set to German but the server is set to English, and the report is being sent to the server, causing it to switch back to the US format. Unfortunately, the server language must remain in English.

This is the correct Format in **Preview** (Designer):  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/f/4f0a9c17f76c5e99ed7076cfa8eaea9a9dcf9acc.png)

This is the Format when executed on the Gateway:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/5/b5b703612861f365937fb863b4a645a4c46ecc7b.png)

Does anyone have any ideas on how to bypass this issue? I'm considering using the "0" format and treating the input as a string, then converting it to the German format using code like in **Bindings**. However, I'm not sure how to add a binding to a **TextShape**.

If anyone has experienced a similar issue or has a more convenient way to ensure the format doesn't change when the report is created, I would greatly appreciate your insight.

Thanks in advance!

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [July 22, 2024, 4:41pm UTC](https://forum.inductiveautomation.com/t/number-formatting-in-reports-ignition-8-1-2/93524/2 "2024-07-22T16:41:18Z")

</div>

Unfortunately, the number formatting in reports is locked to the locale of the gateway overall, and there's (currently) no way to override it.

I think the "best" overall approach for a workaround would be to use a scripting datasource to rewrite any numeric columns you want to localize and perform the number -\> string conversion using the appropriate locale.

---

<div class="post-metadata">

**Author:** ![Olli\_Burr](https://avatars.discourse-cdn.com/v4/letter/o/9de0a6/32.png) [@Olli\_Burr](https://forum.inductiveautomation.com/u/Olli_Burr)\
**Post date:** [July 23, 2024, 6:45am UTC](https://forum.inductiveautomation.com/t/number-formatting-in-reports-ignition-8-1-2/93524/3 "2024-07-23T06:45:14Z")

</div>

> [@paul-griffith](#):
>
> I think the "best" overall approach for a workaround would be to use a scripting datasource to rewrite any numeric columns you want to localize and perform the number -\> string conversion using the appropriate locale.

Sorry, but could you please provide a more detailed explanation, or provide a online resource, of your suggested approach using the script data source? I am having difficulty understanding and implementing it...

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [July 23, 2024, 4:37pm UTC](https://forum.inductiveautomation.com/t/number-formatting-in-reports-ignition-8-1-2/93524/4 "2024-07-23T16:37:08Z")

</div>

Here's an entirely self contained example, though I'd recommend making some changes for "production" use (detailed below):  
[report\_localization.zip](https://forum.inductiveautomation.com/uploads/short-url/6BokcQ23FaCXep4ykr8CrELUg6U.zip) (12.9 KB)

In this case I'm just using the "static CSV" input type for simplicity. Everything that matters happens in the script data source:  
First, I'm defining this function. This is slightly modified from my example posted [here](https://forum.inductiveautomation.com/t/can-i-modify-an-entire-column-from-a-dataset-with-system-dataset-setvalue/87043/3); all it does is update an entire column of a dataset in one operation, calling the provided `function` parameter on each value in the column. The modification was giving it the ability to override the column type, since you're going from numbers to strings. In production, you should move this into the project library so you can call it from anywhere in your project.

```python
def mapColumn(function, dataset, column, columnType=None):
	if isinstance(column, basestring):
		column = dataset.getColumnIndex(column)

	columnData = map(function, dataset.getColumnAsList(column))
	columnsToKeep = range(dataset.columnCount)
	columnsToKeep.pop(column)
	dsWithoutColumn = system.dataset.filterColumns(dataset, columnsToKeep)
	
	return system.dataset.addColumn(
		dsWithoutColumn, 
		column, 
		columnData, 
		dataset.getColumnName(column), 
		columnType or dataset.getColumnType(column)
	)

```

Then I'm defining a function to actually do the localization of a number into currency format in the provided locale. I'm leaning on Java's built in number formatting for this. Again, this should be defined in the project library, but for simplicity I put it all in one place. The locale you pass in should be a [language tag](https://en.wikipedia.org/wiki/IETF_language_tag) string, like `"de_DE"` for German (Germany) or `"en_US"` for English (United States), etc.

```python
from java.text import NumberFormat
from com.inductiveautomation.ignition.common.i18n import LocaleUtils

def localizeNumber(number, locale):
	locale = LocaleUtils.parseLocale(locale).orElseThrow()
	nf = NumberFormat.getCurrencyInstance(locale)
	return nf.format(number)

```

Finally, the piece that actually does anything with the report.  
The function definition is just used to provide a default value to the general purpose `localizeNumber` function. You could also use `functools.partial` or a `lambda` expression for this. The key is to take a function that accepts _two_ arguments and return a function that accepts _one_ argument, because that's what `mapColumn` is expecting.

Then, that new function is applied to the two numeric columns in the `static_data` dataset, turning them into strings. Then that new dataset is output as a new data key. The overall concept of what we're doing is covered [here](https://www.docs.inductiveautomation.com/docs/8.1/ignition-modules/reporting/report-data/scripting-data-source).

```python
def localizeToGerman(number):
	return localizeNumber(number, "de_DE")

ds = data["static_data"]
ds = mapColumn(localizeToGerman, ds, "Column2", columnType = str)
ds = mapColumn(localizeToGerman, ds, "Column3", columnType = str)

data["localized"] = ds

```

* * *

The end result is something like this, where the top table is the original data, and the bottom table is the localized presentation:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/4/247aff4afb9347e838e2b5e29e764b4902093814.png)

---

<div class="post-metadata">

**Author:** ![Olli\_Burr](https://avatars.discourse-cdn.com/v4/letter/o/9de0a6/32.png) [@Olli\_Burr](https://forum.inductiveautomation.com/u/Olli_Burr)\
**Post date:** [July 24, 2024, 5:07am UTC](https://forum.inductiveautomation.com/t/number-formatting-in-reports-ignition-8-1-2/93524/5 "2024-07-24T05:07:32Z")

</div>

Paul, you're the man! Thank you tons for providing such a detailed description!  
I'll give it a go and keep you posted!

Again, thanks a lot!

---

<div class="post-metadata">

**Author:** ![Olli\_Burr](https://avatars.discourse-cdn.com/v4/letter/o/9de0a6/32.png) [@Olli\_Burr](https://forum.inductiveautomation.com/u/Olli_Burr)\
**Post date:** [November 8, 2024, 6:48am UTC](https://forum.inductiveautomation.com/t/number-formatting-in-reports-ignition-8-1-2/93524/6 "2024-11-08T06:48:13Z")

</div>

By mere chance and playing around a bit I found a solution for my problem...

By simply adding/adjusting a Binding to the reports paramerters, with the following code, did the trick:

```python
def transform(self, value, quality, timestamp):
   	'''
   	The code replaces the comma and the period in multiple steps because replacing both 
   	simultaneously could lead to confusion of the characters.
   	'''
	# The value is converted to a floating-point number to ensure that mathematical operations can be performed.
	number = float(value)
    
	# Format the number with comma as thousand separator and dot as decimal separator
	formatted_number = '{:,.2f}'.format(number)
	
	# The comma is replaced by a temporary character. This is done to "store" the 
	# comma so that it won't be confused with the period later.
	formatted_number = formatted_number.replace(',', 'TEMP')
	
	# Replace the dot with a comma
	formatted_number = formatted_number.replace('.', ',')
	
	# Replace the temporary marker with a dot
	formatted_number = formatted_number.replace('TEMP', '.')
	
	# Return the formatted number
	return formatted_number

```

This is how it looks like after Report has been created.  
Instead of 51,105.00 € (US Format) it shows 51.105,00 € (GER Format):

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/d/9d3cac1daa685dc0e967d4a45623c864ea853fe3.png)

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 8, 2024, 9:31am UTC](https://forum.inductiveautomation.com/t/number-formatting-in-reports-ignition-8-1-2/93524/7 "2024-11-08T09:31:33Z")

</div>

Hi, Olli. I took out the "Perspective" in your title and the tag. The Reporting module is completely separate from the Perspective module so they were not relevant.

---

<div class="post-metadata">

**Author:** ![Olli\_Burr](https://avatars.discourse-cdn.com/v4/letter/o/9de0a6/32.png) [@Olli\_Burr](https://forum.inductiveautomation.com/u/Olli_Burr)\
**Post date:** [November 8, 2024, 9:33am UTC](https://forum.inductiveautomation.com/t/number-formatting-in-reports-ignition-8-1-2/93524/8 "2024-11-08T09:33:26Z")

</div>

My mistake, thanks for correcting!

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [November 8, 2024, 4:30pm UTC](https://forum.inductiveautomation.com/t/number-formatting-in-reports-ignition-8-1-2/93524/9 "2024-11-08T16:30:30Z")

</div>

If you're in Perspective and dealing with a single number to string operation at a time, you can lean on Java's tools to do it simpler and faster:

```python
from java.text import NumberFormat
from java.util import Locale

nf = NumberFormat.getCurrencyInstance(Locale.GERMANY)

print nf.format(123.45)
print nf.format(51106.01)

```

```python
>>> 
123,45 €
51.106,01 €

```

You can wrap that up in a project library script to save the cost of importing and I can guarantee it'll be much faster than the repetitive string replacement operations (not that it's really going to matter in this case) - but I would argue it's much, much nicer to _read_.

---

<div class="post-metadata">

**Author:** ![Olli\_Burr](https://avatars.discourse-cdn.com/v4/letter/o/9de0a6/32.png) [@Olli\_Burr](https://forum.inductiveautomation.com/u/Olli_Burr)\
**Post date:** [November 11, 2024, 7:16am UTC](https://forum.inductiveautomation.com/t/number-formatting-in-reports-ignition-8-1-2/93524/10 "2024-11-11T07:16:55Z")

</div>

> [@paul-griffith](#):
>
> ```python
> from java.util import Locale
> 
> nf = NumberFormat.getCurrencyInstance(Locale.GERMANY)
> 
> ```

True - your approach is much more professional, and most importantly it works perfectly! 🙂