I have been using LibreOffice Base now for several years, but I have not been able to locate a good source for extended trainging beyond the basics in North America. I am starting to think I may know than most, but I would love to chat with someone who uses BASE more regularly and is taking advantage of more of its features. Please reply.
You have seen Base Guide?
If you need more help: I’m German and will understand a little bit English. Enough for writing comments her in ask.libreoffice.org and writing many bug descriptions for LibreOffice Base.
1 Like
If you are familiar with relational databases (data types, indices, normalization), if you understand the difference between parsed queries and direct queries and if you grasp the concept of forms, sub-forms and list boxes in the context of primary keys and foreign keys, there are no advanced nor hidden features nor any Easter eggs. OK, sub-forms based on parameter queries are not that obvious but very powerful.
The Report Builder is like a separate component within the Base component. When struggling with report design, I never get the difference between feature and bug.
1 Like
There is no comprehensive literature as there was for Accces + VBA. The way to deal is:
- What you would like to do/have for your DB(?)
- Look in the Guide or ask here.
See e.g. the amazing code in Grid control creation
1 Like
There may be a time that I may need that. Thanks for the offer.
Okay, thanks for the quick review. I guess I have experience with all that you mentioned in BASE, and I guess I was thinking of a way to make things a bit more fancey and have a control dashboard, as well as labeling the tables, queries, forms and reports in a more meaningful way. Plus, as there are always changes it seems like one change in the table creates a need to create new queries, forms and reports. The report design seems to have a few limitations but I guess I am getting the job done.
I think one thing I would like to have is more videos as to how others are designing and working with their databases to achieve significant results, kind of learn some different ways of maximizing the tools available.
Yes. I think there are no these things beyond the very basic, or the Forms - Subforms design.
If I remember well, there is something here about a dashboard.
But I think nothing like the Access Menus or the AutoExec.
I store user forms as stand-alone documents. This allows dashboards made of plain hyperlinks or hyperlink buttons as well as desktop links opening the form documents without the Base window.
Most of my reports are spreadsheets with pivot tables and database ranges, both linked to meaningful queries. Calc is well enough suited for pretty printing and reporting.
1 Like
That is an interesting comment, not sure how I would be able to use that in my situation, because I am updating the information in BASE to generate my reports and a template would not meet my needs, unless I am missing something. Do you have an example or step-by-step how exactly your are using BASE data to get into the spreadsheet to then generate a report.
Thanks for the information.
The information seems to be designed more for WINDOWS that for MAC. And text only is also a bit of a challenge.
Having said that, there were enough clues that I was able to look up additional resources and get things to transfer data to the spreadsheet and refresh as needed, so as not to have to go back into the registered database.
I am now thinking I should put something together for those using MacOS Tahoe, or at least the MAC environment that may be helpful to others that may wish to take advantage of this solution. I still would need to then take and design a report form the data, sorting etc. Or am I missing something there as well?
If you consider a report as a human-readable and printable database excerpt, there is no need for additional database reports. IMHO, Calc is good enough for this purpose. Furthermore, it is a lot easier to do calculations, charts and conditional formatting with Calc.
Regarding sorting and filtering:
- Your query can do the sorting and filtering. Then every refresh of the database range will be sorted and filtered according to the query.
- You may also do the sorting and filtering in Calc. Calc’s sorting and filtering options will be stored with the database range as well. However, in this case you may change the sorting and filtering directly on the report.
P.S.: I use to define sorting and filtering in SQL because it’s easier. A quick test reveals that Calc’s sorting overrides the SQL sort order. An SQL filter (WHERE clause) limits the records that can be overlaid with an additional Calc filter.
A template with predefined cell styles I use to use for database reports:
DBReport…ots (22.2 KB)
Pivot tables are the most database-ish feature in Calc. You can use a simple, unaggregated database query (or table) as source of a pivot table and let the pivot table do the aggregations (sum, min, max,… for each category).
A pivot table does something like:
SELECT <row fields>, <column fields>, Functions(<data fields>)
FROM Query
GROUP BY <row fields>, <column fields>
With column fields you get a cross table, having the row labels on the left, column labels on top and calculated data in the crossing cells.
I have one spreadheet report merging results from 3 different databases.
A toolbar button does the following:
Sub Main
for each dbr in ThisComponent.DatabaseRanges
dbr.refresh()
next dbr
for each sh in ThisComponent.getSheets()
for each dp in sh.DataPilotTables
dp.refresh()
next dp
next sh
End Sub
I never have to edit anything in this document. All the source ranges, formulas, charts and pivot tables are updated automatically from the data sources.
Bug report regarding the extra options of linked database ranges: #163192
I agree a grid is good enough for most general reporting. It is disappointing the query is so crippled by not saving formatting: 86315 – EDITING: Formatting of queries should be saved by base
Thanks for the information, but I think you are now out of my range of knowledge, and will need to do some more experimenting. As my current need is not for a lot of calculations, but a well formatted and good looking report. I may get to the level your are sharing, but it will take more work and understanding. I appreciate the replies.