How to change Pivot-table datasource when datasource is registered database?

In LibreOffice Calc: I cannot change the Pivottable datasource when the datasource is a table/query from a registered database. Any suggestions/workaround?

Problem Description:


  1. Open LibreOffice Calc.
  2. Build a Pivottable using a registered database as a source and save.

Problem (trying to change data source):

  1. Put cursor into Pivottable.
  2. Rightclick, choose Edit Layout; window Pivot Table Layout opens.
  3. Click on Source and Destination.
  4. Source is greyed out. The current source cannot be identified and it cannot be changed.

Any workarounds? Suggestions? Where do I find this setting in the code of the *.ods-file, so I could change it there?

Linux Mint 18.1, cinnamon 64 bit (based on Ubuntu 16.04 LTR)

Aside from creating a new pivot table based upon a different source, I don’t thing there are workarounds for this situation. The greyed out source is available when the source is from spreadsheet content. If you are using a registered DB as source, then changing that changes the entire pivot table.

Meanwhile, I found the solution. This solution should work on any operating system, i.e., on Windows, Linux, or any other.

For a solution we use the file on disk that contains the spreadsheet. It has the the extension *.ods. This file is actually a zip file, i.e. in the compressed file format “zip”.

  1. Unzip this file. (In Linux Mint I do it with the application FileRoller – German: Archivverwaltung –, but of course you can use any program that unzips.) The unzipped file contains several folders and files, one with the name “content.xml”.
  2. Open “content.xml” with a text editor. (I used either LibreOffice Writer or the Notepad-like xed.)
  3. With the Search&Replace function find the name of the old “Registered Database” and replace it with the new “Registered Database”. Do this for every Pivottable in your spreadsheet.
  4. Save and close.
  5. Zip the file again with a Zip utility.
  6. When you open your workbook now, it works with the new source.

This only works if the new source (the new registered database) has the same exact structure as the old one, of course, because the Pivottable relies on the database’s exact structure.