Page 1 of 1

[Tutorial] Using registered datasources in Calc

Posted: Thu Oct 02, 2008 12:40 pm
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).

Re: [Tutorial] Using registered datasources in Calc

Posted: Wed Jul 20, 2011 10:45 am
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.

Re: [Tutorial] Using registered datasources in Calc

Posted: Tue Jun 10, 2014 12:51 am
by raywales
Thank you for this EXTREMELY HELPFUL tutorial !! Solves a lot of problems with report generation in Base.

Re: [Tutorial] Using registered datasources in Calc

Posted: Mon Jun 15, 2015 9:39 pm
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

Re: [Tutorial] Using registered datasources in Calc

Posted: Thu Oct 27, 2016 12:23 pm
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?

Re: [Tutorial] Using registered datasources in Calc

Posted: Thu Oct 27, 2016 12:59 pm
by RPG
Hello

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

Romke

Re: [Tutorial] Using registered datasources in Calc

Posted: Thu Oct 27, 2016 3:56 pm
by papijo
Thanks, RPG. It is indeed Ctrl+Shift +F4 in Calc, but still F4 only in a Text document.

Re: [Tutorial] Using registered datasources in Calc

Posted: Sun May 14, 2017 5:03 pm
by Villeroy
Example combined with a filtering form: viewtopic.php?t=88516&p=416210#p416210