Report Design: Create a specific (custom) number format

Hi there, fellow KNIMEr’s :slight_smile:

my first KNIME workflow has grown over the past weeks and I am really proud what I achieved (reducing efforts from 2 to 3 hrs / mth to approx 10 mins using report automation).

Now the icing on the cake would be, if I even wouldn’t have to copy over my results into Powerpoint but let KNIME / BIRT create the presentation instead.

For this purpose I need a specific number format, which my management (audience for the resulting report) is used to:

e. g. a number like 1,300,000 EUR (one million three hundred thousand) becomes 1’300 kEUR.

Can you eventually answer these two questions:

  1. I would assume this number formatting is done in BIRT and not in KNIME with a certain node, right?
  2. How can I set up a custom number format like this?

I have done this previously in Excel and the formatting custom number format was ###’###." kEUR" for this but it seems this does not work 1 to 1 in KNIME / BIRT (which I didn’t expect)

Thank you in advance for your help!

This might actually be easier to do on the KNIME side, using a combination of Math Formula and String Manipulation nodes to create the desired outcome.

If you want to try in BIRT, the best method I found was something like the link below, where you use a short OnRender script. For example,

this.getStyle().numberFormat="#,###.## kEUR";

I still couldn’t get all the way there using this method, though. Maybe you can tweak it properly: https://stackoverflow.com/questions/22864183/number-format-a-decimal-in-birt-reports

1 Like

hmmm… I cannot find any source that explains how this script works, especially which of the signs (. and # and 0) does what.

It seems to be the right script, but I can’t figure it out.

I wonder how this could be done on the KNIME side with math formula and string manipulation???

This is a bit trickier than it first appeared - here’s how I ended up doing it. Ultimately it required a Java Snippet node to explicitly set the thousands separator.

2018-12-19%2013_43_47-KNIME%20Analytics%20Platform

ThousandsSeparatorJavaSnippetExample.knwf (10.4 KB)

2018-12-19%2013_43_38-Appended%20table%20-%203_43%20-%20String%20Manipulation%20(Combine%20with)

1 Like

Thank you @ScottF

Reading your valuable feedback a few more times, I have implemented it in a similar way, but without the Java Snippet (as I have no single clue about programming in Java).

The sequence I chose is

Math Formula [calc down to millions] --> Math Formula [round the % to 1 decimal] --> Number to String [convert the new columns into strings] --> String Manipulation [append the kEUR part] --> String Manipulation [change the “.” in the strings to " ’ "] --> String Manipulation [append the %sign to the percentages]

… see also my attached workflow.

The one question that remains open is if I could combine the several Math Formula and String Manipulation nodes into one?

kowisoft, try Column Expression to combine.

1 Like