Hi Ferry and Andreas,
Andreas Saeger escribió:
Ferry Toth wrote:
[...]
2) Another thing database designers are not overly concerned about is
how to get the data out, in a meaningful way. This is demonstrated by
many low cost administrative packages based MSA as well. However, the
end user doesn't collect data just to record history, they want to do
something with it.
For instance we are developing a Time Track application that stores data
in a postgresql server, with some properties relating to project
management and billing.
One way to retrieve the data is through Base, into Calc using the data
pilot. I'm sure it is not evident to everybody how to do this, and it's
only one of the many things you can do with Base. A lot of other stuff
would require programming. For instance who know how to display a graph
in a Base form that is updated by the selection of the form (a more or
less simple thing to do in MSA).
Additional note: Datapilots created from datasource are not accessible
through the API. Macros don't see them.
3) Also, building this Calc sheet and the Base database we found short
comings that will probe usefull in further developing both applications.
(for those still interested:
Oh, yes. All my database reports are formatted spreadsheets and data pilots.
*Using form filters on boolean fields produce an error that an integer
can not be compared to a boolean. Funny, Base nows that the field in the
table is a boolean, because is dislays a checkbox, but when you want to
set the form filter on "Billable" it add the filter Billable = 1
Thank you for this information. This may save some hour of time since it
is one of those "what-am-I-doing-wrong?" issues.
*In the datapilot you want to generate a bill for a customer (a project)
over a certain period. Whoopie, I can select the project, tasks,
billable hours and get totals for everything I need. Unfortunately, I
can only select the time frame by manually excluding each date outside
of the period. I appears the datapilot handles data field as text
(showing each and allowing them to be excluded, instead of values that
can be included between a start and end date)
Select a date/time-cell in the pilot and call Data>Outline>Group (key
F12). You can split up the date/time into minutes, hours, ..., years.
(for those still interested):
* Time stamp fields from data sources are imported as empty data pilot
fields.
http://www.openoffice.org/issues/show_bug.cgi?id=81848
* In Calc you can set refresh periods for imported database ranges, but
not in the GUI.
* You can import from data source by SQL SELECT, but not in the GUI.
With a little bit of scripting you can build parameter queries from
spreadsheet cells.
Very quick and dirty proof of concept:
http://www.mediafire.com/download.php?ft4thvzoztx
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Concerning the FROM Base TO Calc topic, maybe the following can help you:
http://www.arielconstenlahaile.com.ar/ooo/docs/ARIEL_OOoBASIC_IMPORTAR_a_CALC_de_BASE.ods
as Andreas says, it is impossible to import with the DataPilot using OOo
API.
About Andreas excellent example, I developed a Calc Add-in that works to
import from Base to Calc using the so-called "database functions" of
Calc but adapted to work with a real database (not a so-called "database
range" in a spreadsheet). But about this I had my concerns: is it a good
idea to use a function to import data to a single cell? doesn't this
consume too much resources?
About a "database chart" functionality, at the beginning of this year I
started some experiments; with
css.frame.TransientDocumentsDocumentContentFactory, css.ucb.*,
css.embed.* I was able to do this:
create a new charts folder on a currently open odb, embed chart
documents inside this odb, store the settings for a simple pie chart to
get the data from the database in XML format (this using Java API); then
I could work the other way around: load the chart in a frame inside an
AWT top window, read the settings, create a simple pie chart...
ALL this means it is POSSIBLE to develop a database chart using OOo API.
I stopped my research, on one hand, waiting for the new chart
functionality (now in OOo 2.3); on the other hand, because I realized it
was too much work for me!!! A simple pie chart is very simple to build
adding some aggregated functions that will query the table, but a whole
complete implementation goes beyond my skills at this time!!!
I think a database chart embedded on the odb, just like forms and
reports, is POSSIBLE; and as M$Access has this functionality, will it be
able some day on OOoBase?
Bye
Ariel
--
Ariel Constenla-Haile
La Plata, Argentina
[EMAIL PROTECTED]
[EMAIL PROTECTED]
http://www.arielconstenlahaile.com.ar/ooo/
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
