How do I link data from a LibreOffice Database (linked to a MySQL Server) to a LibreOffice Spreadsheet?

users
#1

Hi.

I'm on a Mac and trying to replicate a process I'm very familiar with
on Windows using MS Excel.

I have a database (a MySQL database) and I want to create a
spreadsheet that will allow users to quickly query the database and
get reports/counts/etc.

In Windows, I create views on the database and use MS Query / ODBC to
link between Excel and the database. All works exactly as you'd
expect. No drama, hassle or anything.

I'm no longer working in a Windows environment and I'm struggling with
what seems to be a fairly obvious use case.

I've been recommend LibreOffice, and for the bits and pieces I've used
so far, I'm impressed.

I've managed to create a database link and I can create views and
store them and, on the surface, it looks OK.

When I look at the spreadsheet, I can see no way to link to the
database. When I open the Navigator, I can see Database Ranges - but
that's not what it seems.

Now, I know I can cut'n'paste the data. This is NOT what I want.

The use case is it allow a user to open the spreadsheet and either
automatically or manually update the data from the database. No
complex cut'n'paste or 20 click madness.

Any real help would be greatly appreciated.

Thanks in advance.

Regards,

Richard.

#2

Hi :slight_smile:
Hopefully other people will have better answers later but it might be worth having a quick flick through likely chapters in the official documentation on
http://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Calc_Guide
Good luck and regards from
Tom :slight_smile:

#3

Hi,

Any real help would be greatly appreciated.

Did you have a look at
http://user.services.openoffice.org/en/forum/viewtopic.php?f=75&t=18511
?

Regards,
Nino

#4

First you link a so called "Base document" to the database. Then you can link office documents.

Database...

[X] Connect to existing database
Type: MySQL
[X] Register the database
Specify the connection details.
Save the "Base document" (it is a configuration file actually).
You may add some useful SELECT queries for your purposes.

In Calc hit F4 and drag your table/query into the sheet.
This creates a linked import range (see Data>Define...).

#5

Am 30.04.2012 21:20, Tom Davies wrote:

Hi :slight_smile:
Hopefully other people will have better answers later but it might be worth having a quick flick through likely chapters in the official documentation on
http://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Calc_Guide
Good luck and regards from
Tom :slight_smile:

Tom,
Please shut up if you don't know any answer other than a hyperlink to the (awful) LibreOffice documentation.
You spam this list with your MariaDB announcements but you can not answer the question how to connect a database to this office suite.

#6

Perfect. At least now I can get data into a spreadsheet.

Next I need to work out calling stored procedures and passing user
parameters to the SP as well as automated updating (on open and on
parameter changing).

These are probably macro based rather than UI.

Hopefully.

Thank you for your reply though. Very useful.

#7

Am 01.05.2012 00:35, Richard Quadling wrote:

Perfect. At least now I can get data into a spreadsheet.

Next I need to work out calling stored procedures and passing user
parameters to the SP as well as automated updating (on open and on
parameter changing).

These are probably macro based rather than UI.

Hopefully.

Thank you for your reply though. Very useful.

I know at least 3 methods.

1) Simple method:
  Add a parameter query to the "Base document":
SELECT * FROM "Table" WHERE "A" = :param_A AND "Date" BETWEEN :From_Date AND :Until_Date

Named parameters are unquoted alphanumeric and start with a colon.
Drag that query into your sheet.
When you access/refresh the parameter query you will be prompted for substitution.

2) Add a small filter table to the database, add a criteria input form to the spreadsheet and bind it to one particular record of the filter table.
The user enters the criteria into the form and refreshes the import range which is bound to:
SELECT "D".* FROM "Data" AS "D", "Filter"AS "F" WHERE "Filter"."RowID"=0 AND "D"."A" = "F"."INT_A" AND "D"."Date" BETWEEN "F"."D1" AND "F"."D2"

3) A generic macro of mine which binds an import range to a changing SQL string (not possible in the GUI). For simplicity and better customization the macro reads the SQL from a named cell.
http://user.services.openoffice.org/en/forum/viewtopic.php?t=1645&p=6847#p6847

#8

Hi :slight_smile:
If you feel you could produce good guides then i'm sure the docs team would be glad to welcome you in. 
Regards from
Tom :slight_smile:

#9

Am 01.05.2012 01:45, Tom Davies wrote:

Hi :slight_smile:
If you feel you could produce good guides then i'm sure the docs team would be glad to welcome you in.
Regards from
Tom :slight_smile:

No, they woudn't!

#10

Hi :slight_smile:
Lol, oh yes they would (well, they might not).
Regards from
Tom :slight_smile: