Base and Calc database capabilities vs Excel

[crashmeister]

Hi. I want to try and use LO instead of buggy Excel for my large financial spreadsheet. It makes extensive use of DB queries, formulas, conditional formatting etc.

I have the following requirements and want to know if/how this can be done in Base and Calc.

± 30 Database Queries:

The results of each query displayed in tables in multiple sheets with column filters/sort options. Some sheets have multiple queries on them.

Some tables(queries) need to refreshed at regular intervals without user intervention (while document is open), say every 5 minutes. Refresh time needs to be set per query.

A ‘Refresh’ button or menu option easily available to refresh the data in a table on demand. Excel’s is on table’s right-click menu. There should also be a ‘Refresh All’ option.

I have set up access to my DB (Postgres) and created one query. I have no idea how this data gets into Calc and how to set refresh parameters etc.

Given the size of this task, I have no desire to spend lots of time learning LO if the capabilities are not there.

If this is all feasible, I would really appreciate specific help or URLs for good and relevant references/tutorials.

Thanks.

Edit: I’m guessing there is no way to import the queries and tables from Excel. Correct ?

[schiavinatto]

@crashmeister

Hi. I want to try and use LO instead of buggy Excel for my large financial spreadsheet.

O quanto é grande?
Tem exemplo de arquivo?
Sistema Operacional e Versão do LibO?
Vai usar Base e Calc e salvar no padrão do programa?

---

How big is it?
Do you have an example file?
Operating System and LibO Version?
Are you going to use Base and Calc and save to the program default?

[Villeroy]

On a scale from 0 to 10 on a database scale, Excel may be 3, Calc 2 and the relational databases connectable to Base/Access are 7 to 10.

@crashmeister

I have set up access to my DB (Postgres) and created one query. I have no idea how this data gets into Calc and how to set refresh parameters etc.

Before that, you connected a Base document to the PostgreDB, right?

• Open a Calc document
• hit Ctrl+Shift+F4 for the data source window
• in the left pane, browse your database and drag the query icon into the sheet.

If your database does not appear in the database window, register the database document at menu:Tools>Options>Base>Databases

[Wanderer]

@crashmeister

I have no idea how this data gets into Calc and how to set refresh parameters etc.

Insert Base query results into Calc

Hello, Data for registered databases is accessed through Data Sources (View->Data Sources). From there you can select the Query then drag the data to the sheet. Then you will have a named range.

@crashmeister

I have no desire to spend lots of time learning LO if the capabilities are not there.

When I read something like this I always think: Stay with the system you use now.
A lot of concepts ara quite different in Open/LibreOffice and you have to learn how to adapt your use-case…

@crashmeister

A ‘Refresh’ button or menu option easily available to refresh the data in a table on demand

Refresh is available in the menu “Data” and will refresh the area (db-range) where your cursor is in. I remember @Villeroy to have posted a refresh all macro…

@crashmeister

I’m guessing there is no way to import the queries and tables from Excel. Correct ?

Depends how this is done in Excel… But as I think tables belong to PostgreSQL they have to be imported from there not Excel… For datasources most queries are either stored in the Base .odb or accessed by macro. So no direct transition expected.

[sokol92]

Using a database to store, modify, and query data, and LO to display the data is the right solution.

@crashmeister

Some tables(queries) need to refreshed at regular intervals without user intervention (while document is open), say every 5 minutes. Refresh time needs to be set per query.

RefreshPeriod

@crashmeister

A ‘Refresh’ button or menu option easily available to refresh the data in a table on demand. Excel’s is on table’s right-click menu. There should also be a ‘Refresh All’ option.

In LO, you can easily customize all types of menus, toolbars, and hotkeys at both the document and application levels. You can also use buttons and other controls within documents.

@crashmeister

Edit: I’m guessing there is no way to import the queries and tables from Excel. Correct ?

The question needs clarification.
If we’re talking about SQL queries to the database, then these queries can be migrated.

[crashmeister]

Sigh !!!

I guess I’ll be sticking with Excel for now.

Thanks to all who tried to help. Much appreciated.

[Villeroy]

You don’t give us any clue about your actual requirements. Here is a sample with a simple relational database (Hyper SQL embedded in the Base document) and a Calc document, demonstrating everything Calc can do with relational databases without any macro code. Well, there is a one-line macro included.
How to set up:

1. Download the zip.
2. Tools>Options>Security, button [Macro security], choose the highest security level and add some directory for trusted documents allowed to call macros.
3. Extract the zip to that directory.
4. Tools>Options>Base>Databases, register the database document “PowerFilter.odb” as “PowerFilter”.
5. Open the spreadsheet document “PowerFilter.ods”.
   5.1. First sheet has a form to enter filter criteria and a subform grid providing the filtered and editable record set.
   5.2 Second sheet has a form to enter filter criteria (independently from 5.1.) and saving the criteria record dumps the resulting record set into a linked database range.
   5.3. The third sheet has a pivot table based on the full record set, to be aggregated and filtered directly on sheet.
   PowerFilter.zip (256.8 KB)

[iplaw67]

@crashmeister

Given the size of this task, I have no desire to spend lots of time learning LO if the capabilities are not there.

Then don’t, stick with what you know. Getting Base to do anything even moderately sophisticated takes time, energy, and a desire to learn a different way of doing things.

LO Calc doesn’t have PowerQuery, for example, so you are reliant instead on the relatively limited functionality of Named Database Ranges based on SQL Queries defined in your associated Base file, which you then have to refresh manually, or via macro.

[iplaw67]

@crashmeister

I’m guessing there is no way to import the queries and tables from Excel. Correct ?

Yes, you are absolutely correct, there is no import button / function in Calc for queries and tables from Excel, everything has to be migrated to the database file that you link to your Calc file, and from which you would use Named Ranges to retrieve and display the data.

See also tdf#158857