Ask Your Question
0

How to change Pivottable datasource when datasource is registered database?

asked 2017-06-13 12:39:55 +0200

xenon1 gravatar image

updated 2018-02-07 21:28:33 +0200

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:

Prerequisites:

  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?

System: Linux Mint 18.1, cinnamon 64 bit (based on Ubuntu 16.04 LTR) LibreOffice 5.1.6.2

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2018-02-07 21:21:48 +0200

xenon1 gravatar image

updated 2018-02-07 21:27:03 +0200

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". So:

  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.

edit flag offensive delete link more
0

answered 2017-06-14 20:59:35 +0200

Ratslinger gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-06-13 12:39:55 +0200

Seen: 317 times

Last updated: Feb 07 '18