[Tutorial] Using registered datasources in Calc

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Tutorial] Using registered datasources in Calc

Post by Villeroy »

Create a link to the records of a query or table :
In Calc hit F4 (LibreOffice Calc: Ctrl+Shift+F4), browse your database and drag the table (or a query for the same purpose) from the left pane onto a cell.
If you don't find your database in the left pane of the datasource window: Right-click the left pane and call "Registered databases...", add your database and do the drag&drop. Notice that you could also load the database document from this place. The registration tool is also accessible from menu:Tools>Options...OOo Base>Databases.

Refresh the imported range: Click a single cell in the imported range, call menu:Data>Refresh...

You can also copy record sets, just in case you don't want a link:
Pick a table or query from the left pane, wait a moment and grab the whole record set at the empty grey box on top of the row selectors, left of the first header and drag it onto a cell. You can also drag&drop a single value, a record or a selection of multiple records from the right pane onto a cell. Strangely, the same does not work with columns. Use a query to copy set of columns.
Drag&drop of row sets onto sheets also works with any grid-view of a loaded database document.
You can copy a table or query out of the container. Select an object in the main window of the database, copy and paste in Calc.
Side note: In Writer you get a mail merge field when you drop a column from the right pane.

Release a link: menu:Data>Define..., pick "Import1" or what ever refers to the imported range, delete the named thingy.

Properties of linked import ranges in a spreadsheet:
menu:Data>Define..., pick your range hit button [More]
- Contains column labels [always true for imported ranges]
- Insert or delete cells [on refresh adjust adjacent formulas to the new size of an imported range. Should be set by default but isn't]
- Keep formatting [... of the spreadsheet cells on refresh. Should be set by default but isn't]
- Don't save imported data [save space and enforce a manual refresh after loading]
After changing a property, hit [Modify], then [OK]
 Edit: Since AOO 4.1 "Keep formatting" and "Insert or delete cells" are checked by default. This means that import ranges do work out of the data source box. 
Names of database ranges can be used in formulas just like ordinary named ranges.

If you want names for your import ranges other than Import1 etc: Create a single-cell database range manually, drag the table/query onto that cell, confirm to replace the contents.

For the records, not related to linked import ranges:
You can create data pilots (aka pivot tables, aka cross tables) from registered data sources. menu:Data>Pilot>Start...
You can drag&drop a form's record set after pushing the last button of toolbar "Form Navigation", which shows a beamer window showing a grid of the currently focussed form's content. Drag the grey top-left handle or selected records.

And yes, you can create import forms on Calc sheets just like you would do manually on a stand-alone Writer document or embedded database form (which is a Writer document too).
Last edited by Villeroy on Mon Feb 27, 2023 9:40 pm, edited 9 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] Using registered datasources in Calc

Post by Villeroy »

2011-07-20: Found an additional feature, a highly experimental one though.

The resulting import range has a hidden property "RefreshPeriod" which is missing in the GUI.
A macro can activate this time interval for automatic refreshes.
Tools>Macros>Organize>Basic...
Create a new module anywhere in "My Macros" and paste this code:

Code: Select all

Sub setRefreshIntervals
	for each dbr in ThisComponent.DatabaseRanges
		s = dbr.getName()
		i = dbr.RefreshPeriod
		sInt = InputBox("Refresh interval in seconds for database range '"& s &"'", _
			"macro:setRefreshIntervals", i)
		if len(sInt)>0 then
			dbr.RefreshPeriod = cInt(sInt)
		endif
	next
End Sub
This prompts you to enter a refresh interval for each database range in the current document. Enter an integer amount of seconds, enter 0 to turn it off, hit cancel to keep the current value.

After you have run that macro you need to save and close your spreadsheet. After reloading the document, data will be refreshed according to the specified interval. The above macro is no longer needed until you want to change the interval.

Warning: Until now I have no long term experience with this intersting hidden feature. The time interval should be as long as possible and as short as needed. It must not be shorter than the full time to refresh all the ranges. The whole office is locked during the update.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
raywales
Posts: 3
Joined: Mon Jun 09, 2014 3:45 am

Re: [Tutorial] Using registered datasources in Calc

Post by raywales »

Thank you for this EXTREMELY HELPFUL tutorial !! Solves a lot of problems with report generation in Base.
OpenOffice 4.1.0 on Windows Vista
elcraked
Posts: 3
Joined: Sun Jun 14, 2015 11:18 am

Re: [Tutorial] Using registered datasources in Calc

Post by elcraked »

I am told that the listener function is I have to use . Thank you very much for everything. I will consider the issue of obase
Apache OpenOffice 4.1.1 Windows 7
papijo
Posts: 90
Joined: Sat Nov 08, 2014 5:46 pm
Location: Brittany, West of France

Re: [Tutorial] Using registered datasources in Calc

Post by papijo »

Using LibreOffice 5.1.5.2 when I hit F4 key in a Calc document, I get error message: "No references found."
And yet I do have registered databases in my LO system, which I can access with F4 from a Text document.
Why is that?
LO: LibreOffice 6.4.0.3 (x64) on Windows 10 64bits. Split database HSQL 2.3.4.
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Tutorial] Using registered datasources in Calc

Post by RPG »

Hello

In newer version of LO the key is changed from F4 to Ctrl+Shift +F4.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
papijo
Posts: 90
Joined: Sat Nov 08, 2014 5:46 pm
Location: Brittany, West of France

Re: [Tutorial] Using registered datasources in Calc

Post by papijo »

Thanks, RPG. It is indeed Ctrl+Shift +F4 in Calc, but still F4 only in a Text document.
LO: LibreOffice 6.4.0.3 (x64) on Windows 10 64bits. Split database HSQL 2.3.4.
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] Using registered datasources in Calc

Post by Villeroy »

Example combined with a filtering form: viewtopic.php?t=88516&p=416210#p416210
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply