[Tutorial] Using registered datasources in Calc

Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.

[Tutorial] Using registered datasources in Calc

Postby Villeroy » Thu Oct 02, 2008 12:40 pm

Create a link to the records of a query or table :
In Calc hit 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 Fri Aug 08, 2014 10:12 pm, edited 8 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 14.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 22277
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] Using registered datasources in Calc

Postby Villeroy » Wed Jul 20, 2011 10:45 am

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   Expand viewCollapse view
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 14.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 22277
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] Using registered datasources in Calc

Postby raywales » Tue Jun 10, 2014 12:51 am

Thank you for this EXTREMELY HELPFUL tutorial !! Solves a lot of problems with report generation in Base.
OpenOffice 4.1.0 on Windows Vista
raywales
 
Posts: 3
Joined: Mon Jun 09, 2014 3:45 am

Re: [Tutorial] Using registered datasources in Calc

Postby elcraked » Mon Jun 15, 2015 9:39 pm

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
elcraked
 
Posts: 3
Joined: Sun Jun 14, 2015 11:18 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 2 guests