Enter "standalone" Forms and Reports. There's a distinction between "standalone" and "embedded" Forms and Reports. We normally work with embedded Forms and Reports in Base, which are essentially Writer documents stored within the Base container file (.odb). Opening an embedded document requires that we open Base, thereby exposing the Base GUI. But standalone Forms or Reports bypass the GUI, effectively hiding this interface from the end-user. Base still plays a role because a "registered" .odb file is necessary to connect to the data source. But this connection duty is performed transparently for standalone documents. There are some limitations. While Base Forms may be exported as standalone files or created anew in Writer (.odt) or Calc (.ods), Base Reports have no standalone equivalent. But we do have some standalone reporting options available.
Some background:
- The official Switchboard Extension (manual) for Base uses the default "embedded database" configuration to provide push-button access to Forms and Reports stored in any number of "registered" Base files (.odb). In my experience this product works well, even when using the Switchboard Extension with external databases such as HSQLDB 2.x, H2, MySQL, PostgreSQL, etc. But unfortunately, this approach exposes the Base GUI to the end-user. "DB Doc Shortcut" is designed to remedy this shortcoming by opening embedded Forms and Reports from desktop shortcuts, but this feature doesn't seem to function properly (?) with newer versions of Base.
Otherwise, macros expose the Base GUI, defeating our purposes here. But that hasn't stopped developers from using macros to open embedded Forms and Reports using Push Buttons or even a List Box. They also use macros to open Forms filtered to a specific record(s), but that's a little off-topic here. And one point of caution: macros can inadvertently circumvent database connection requirements, so care must be taken especially when opening Forms using the 'Open Document' event at Base startup.
- We can avoid macros, extensions, and exposing the Base interface -- all together -- when using stand-alone Office documents to host Forms, Reports, or a switchboard. You'll find Writer and Calc documents particularly well-suited in these roles -- perhaps best when viewed full screen. Writer is a natural choice for Forms because we can export our existing Base Forms as standalone Writer files (.odt). Calc takes it to the next level with a tabbed interface supporting both Form Controls and certain tools useful in generating Reports. Tying it all together, we can open any Writer or Calc file directly (perhaps using shortcut files), to include a central switchboard document. Database access is handled seamlessly by "registered" Base .odb files (one file per database) serving as universal "data-sources" across your AOO/LibO installation.
Now, while Forms retain their full-function as standalone documents (actually gain access to multiple data sources from one document), standalone Reports exhibit some limitations. Actually, the definition of a Report is difficult to nail-down in Base. Reports can involve lists (Base), charting (Calc) and mail-merge functions (Writer). In addition, Form Controls such as a Table Control (table-grid) can be used to display/print interactive Reports. Lets examine each of these Reporting options in light of our objectives.- You're probably aware that embedded Base Reports are adept at lists, and even grouped lists when using the Report Builder extension. Unfortunately, standalone Reports don't support these lists or the Report Builder extension. Some workarounds include:
- A standalone Writer Form with a Table Control (grid) and other interactive Form Controls. Most Form Controls can be set to non-printable using their "Printable" property. This can be useful when making an interactive Report suitable for printing.
- Perhaps try-out or modify DB Doc Shortcut to access embedded Reports (which do support lists), without exposing the Base GUI.
- Or simply open/expose the Base GUI as necessary to access these list-oriented Reports embedded within a Base .odb file. We can open a Base .odb file directly using a Push Button on a standalone document or switchboard using the button's built-in Action: Open document property while also adding the (.odb) document path to the button's URL property.
- Otherwise, standalone Writer documents are limited to mail-merge functions (form-letters, mailing labels, business cards, envelopes, etc.). But once a mail-merge template is setup, it can be opened anytime and printed with the latest data.
- Calc spreadsheets offer advanced computations, charted ranges and pivot-tables from various data-sources (seamlessly through Base .odb files).
Lastly, I should clarify that Base provides both seamless and managed access to various data sources. 'Seamless access' here infers an automated process which includes loading the database driver/engine automatically in the background for a completely seamless end-user experience. Seamless data-sources include: CSV files, spreadsheets, dBase flat-files, and selected relational databases such as HSQLDB, H2, Derby and SQLite. The default "embedded database" configuration is also seamless, but this configuration risks data corruption, so it's best to convert these files to a proper 'split database' configuration, which can remain seamless. This 'seamless' experience contrasts with 'managed' access such as we encounter with a full-scale database engine. These full-scale engines require separate start-up and shutdown -- although access is seamless for the end-user once the database-server (RDBMS) is started. Base provides managed access to several full-scale databases engines including: PostgreSQL, MySQL, MariaDB, Firebird, Oracle, MS SQL Server, etc. - You're probably aware that embedded Base Reports are adept at lists, and even grouped lists when using the Report Builder extension. Unfortunately, standalone Reports don't support these lists or the Report Builder extension. Some workarounds include:
Begin by saving your current database Forms as standalone Writer documents:
- NOTE: Villeroy has developed a handy macro that automates Form extraction...among other things.
(2) Open each new (.odt) Writer Form and select View > Toolbars > Form Design.
(3) Turn "on" Design Mode using the Form Design toolbar (second icon from the left).
(4) Turn "on" Form Navigator (fifth icon from the left).
(5) In the popup 'Form Navigator' window, right-click each Form (MainForms and SubForms) and click Properties.- Note: The Form Navigator window reveals the 'Forms' structure which may include any number of "MainForms" and their associated "SubForms." Each MainForm and SubForm can be linked to a separate Data source (.odb file registered within OOo/LibO), Content type (Table, Query, or SQL command), and Content (selected by Table/Query name or by manual SQL entry).
Note: You can register your Base files (.odb) in OOo/LibO by clicking: Tools > Options... > Base > Databases
- Note: don't miss Villeroy's sub-steps (6-1 thru 6-3) in the next post below which will save you some time and frustration when re-establishing the Data source when your Content consists of an existing SQL command. In fact, you'll want to use the SQL-command option over the Query option because SQL-commands are uniquely saved in the Form which enhances the portability of standalone Forms. There's also a nice tip for utilizing un-registered databases (Base .odb files), but it's generally better to utilize database registration in OOo/LibO because Data source links are hard-coded (non-relative links) in all cases (not good programming), so it's probably easier to manage the link centrally rather than for each MainForm/SubForm, as Villeroy also points-out.
(8 ) When you're satisfied with each standalone Writer Form, click File > Save- Note: there's no need to save each Form "read-only" when using a Push Button to open the Form due to the associated "read-only" option of the URL. However, this option still allows the user to enter 'Design Mode' using the appropriate toolbar, so it may be best to save a more permanent "read-only" version. File > Save As... > Save with password (checkbox) > More Options... (button) > Open file read-only (checkbox). Do not specify a password. To regain the ability to edit the Writer-Form document after saving "read-only" simply Save As... another copy without the "read-only" attribute.
- (A) In OOo/LibO, select File > New > Text Document
(B) View > Web Layout
(C) Format > Page... > Background tab > As > select a Color or Graphic as desired
(D) View > Toolbars > Form Controls
(E) On the Form Controls toolbar, select Push Button and drag-create a Push Button on the document.
(F) Right-click the new Push Button > Anchor > To Page as necessary
(G) Right-click the new Push Button > Control... opens the Properties: Push Button dialog box
(H) Scroll down to Action > Open document/web page
(J) Now select URL > [...] (browse button) and locate the desired Writer-Form document (.odt) > Read-only (checkbox) > Open (button)- Note: don't miss the "Read-only" checkbox option while "Opening" this URL link.
Note: this will create a "hard link" (including full path) to your selected Writer-Form document. To create a more flexible "relative link" to your Form document, simply edit the URL directly in the URL property text box. For instance, if all your Writer-Form documents will be saved in the same folder with your switchboard document, then delete the entire path to the (.odt) Form leaving only the Writer-Form document name. A "relative link" allows the Writer-Forms and switchboard document to be moved together (as a folder) and retain function without re-linking each document. Thus, the only "hard link" associated with your switchboard is the central 'Data source' registration (path to your .odb file) in OOo/LibO.
(L) Close the dialog box and Turn "off" Design Mode (second button on the Form Controls or Form Design toolbar), and test the new Push Button making sure the associated Form opens "read-only" for immediate use.
(M) File > Save to preserve your work so far
(N) Repeat steps E thru M as necessary to add additional Push Buttons
(O) And finally, File > Save As... > Save with password (checkbox) > Save (button, opens the Set Password dialog box, but skip to...) > More Options... (button) > Open file read-only (checkbox).- Note: Do not specify a password to open, but after clicking More Options... you'll see the option to Enter password to allow editing which can be useful (if you as the developer don't forget the password!).
Note: To regain the ability to edit the Writer-Form document after saving "read-only" simply Save As... another copy without selecting the "read-only" attribute.
- Note: don't miss the "Read-only" checkbox option while "Opening" this URL link.
