Formating numbers for Pivot Tables based on external datasources (external databases)

[belonesox]

The problem:

• Libreoffice calc on refreshing pivot tables data ignores number formating of «Pivot Table Value» «The number format of these cell styles cannot be changed in the cell style itself; you must format it in the data source». It looks ugly, but the issue is documented.

• The only known workaround is to apply right formatting to source data range in Calc Spreadsheets.

But I cannot see, what should I do, if I have to format number values PT from external datasource
(for example, some JDBC source registered in LibreOffice Base).

I failed to found how this can be made, without creating views in external database, or specific query with additional calculated fields (like «ROUND(value) as rounded_value» ) in LO Base.
It is working, but looks ugly and inefficient.

So, have anyone any other ideas and workarounds?

Thanks all in advance!

[RobertG]

Which database do you use? The code for changing a decimal value to a currency expression will (in most cases) set the value as string. So you couldn’t calculate with this value.

[sokol92]

@belonesox

have anyone any other ideas and workarounds

You can do custom formatting after each update of the pivot table, as is done in the example.

[belonesox]

@RobertG

Which database do you use? The code for changing a decimal value to a currency expression will (in most cases) set the value as string. So you couldn’t calculate with this value.

In my case I use Clickhouse with Float32/64 values, which I want to format as int/decimal.

In LO Base I cannot see UI to format fields.

Of course, in LO Base I can create query like “select *, ROUND(value1) as rounded_value1, … from my_table”, but it looks ugly.

Actually, I cannot get non-working Number Formatting from style «Pivot Table Value» is not a bug.

[RobertG]

FLOAT should give a number with decimal places. If I see the first screenshot it is $80.00 - right?

Here some examples for code:
MySQL

SELECT `test`.`tbl_Einnahmen`.*, 
CONCAT( '$', FORMAT( `Betrag`, 2 ) ) AS `Betrag_$`, 
CONCAT( REPLACE( FORMAT( `Betrag`, 2 ), '.', ',' ), ' €' ) AS `Betrag_€` 
FROM `test`.`tbl_Einnahmen`

“Betrag” is a decimal with 2 decimal places. With FORMAT("Betrag",2) it will set to 2 decimal places too. So it will work also with FLOAT. In Germany we use decimal separator ‘,’, so there is a second field, which will show it with comma and €.
Seems Clickhouse connects to different databases. Could be FORMAT doesn’t exist. Try to convert to DECIMAL with 2 decimal places. You could also set round("Betrag",2). So converting isn’t needed.

[Villeroy]

Any valid answer requires that you mention the type of database in use. OK, JDBC is part of the information. What is the info dispayed on the status bar of your Base window? See also menu:Edit>Database>Properties… May be your database engine provides some function for rounding or type conversion.

On the Calc side of the problem, you can manipulate the pivot table’s cell styles.

[belonesox]

You can do custom formatting after each update of the pivot table

Refreshing pivot table drops conditional formatting, so I have create new conditional format after each update (much longer than «Ctrl-A» → «Ctrl-Shift-1» (number format).

Also, condional format can assign styles to cells, but it cell styles, like “Pivot Table Value” already assigned by Calc! The problem that no style can perform “number formatting” in PT.

[belonesox]

@RobertG

FLOAT should give a number with decimal places. If I see the first screenshot it is $80.00

Sorry for misunderstanding. On screenshot I show «toy example»
bug-calc-number-formating-styles-not-working-for-pivot-tables.ods (15.3 KB) that demonstrates, that “PT styles” cannot perform number formatting (but can set font/colors) etc.

When I work with real data (ad-hoc analysis a lot of generated tables with hundreds of columns stored in Clickhouse), I wish to find fast way to perform number formatting, without

• creating a specific “formatting” views in Clickhouse database (an analyst should not have DDL access to DB, only selecting role)
• creating specific queries in LO Base (my current workaround), in which I should create queries like “select *, ROUND(value1) as rounded_value1, ROUND(value2) as rounded_value2 … from my_table” — it is better than views, but… it is not ideal solution (how to fast change formatting for a column, without recreating query, etc)

[mariosv]

One way is to change the format for ‘Pivot table value’ style.
Open the sidebar and select ‘Styles’
Click on a cell of the column on the PT where you want to apply the currency format.
The style is selected, modify it in numbers tab to select currency format.
The possible issue is that it applies to the whole pivot tables in the file,
and it applies only to that file.

[belonesox]

It is not working!

As I already wrote in start of the topic, we can change color/fonts for style «Pivot Table Value» — it worked. But LO Calc ignore “Number Formatting” in this style. See example:

https://ask.libreoffice.org/uploads/short-url/n3odtnrDWJuUFSfglc1D9NI0SRo.ods

The issue is even documented as “have good workaround”:
«The number format of these cell styles cannot be changed in the cell style itself; you must format it in the data source »

But this workaround will not work, if we have to use external sources for PT.

[mariosv]

Thanks for remembering me that limitation.