Base and new Report Builder in 3.5

users
#1

Hi all,

I have been using OO and LibreOffice prior to 3.5 with Base and Reports. Now the Reports in 3.5 can not be used any further, instead I had to try (despair) and create new Reports using the Oracle Reports Tool. IMHO this is a major leap backwards… It is very, very slow on my Mac (2.13 GHz Core2Duo), it does somehow not save my new generates Reports. If I choose the Table Model it will write the Column Heading repeatedly (I did not find a way to prevent this) on each Row. To Adjust the Columns and Rows is REALLY Painful. It is no longer possible to use Fields i.e. Page Numbers. Despite setting the Format to A4 Landscape the GUI will limit placing Labels further than 200mm of a Portrait A4 Page.

I loved the general improvement on 3.5 but this really forced me to downgrade to 3.4. I hope this gets reverted in future versions, because sincerely I do not think anybody will use this Editor.

If there is a way of reusing (or transforming) Pre 3.5 Reports in 3.5 onwards please let me know.

Kind regards
Andreas.

#2

a BIG +1 from me on this as well!!!!!

The repeating headings are a real pain (you know where) and as Andreas says editing is almost impossible.

Unfortunately I CAN'T revert as the earlier LOs will not run my Database.

Any ideas anyone (please!!!???)

Ian Whitfield
Pretoria.

#3

Hi :slight_smile:
Errr has the 3.5.1 fixed the problem?  Is it possible to post a bug-report
http://wiki.documentfoundation.org/BugReport
https://www.libreoffice.org/get-help/bug/
Regards from
Tom :slight_smile:

#4

Am 11.03.2012 19:48, tops wrote:

Hi all,

I have been using OO and LibreOffice prior to 3.5 with Base and Reports. Now the Reports in 3.5 can not be used any further, instead I had to try (despair) and create new Reports using the Oracle Reports Tool. IMHO this is a major leap backwards… It is very, very slow on my Mac (2.13 GHz Core2Duo), it does somehow not save my new generates Reports. If I choose the Table Model it will write the Column Heading repeatedly (I did not find a way to prevent this) on each Row. To Adjust the Columns and Rows is REALLY Painful. It is no longer possible to use Fields i.e. Page Numbers. Despite setting the Format to A4 Landscape the GUI will limit placing Labels further than 200mm of a Portrait A4 Page.

I prefer Calc as a simplified report engine. It supports more math functions than anything else. It provides the "natural" surrounding for charts. It supports dynamically linked, (conditionally) formattable import ranges, pivot tables, secondary filters and sort orders. It is the only report format which can be loaded from any form with a click on a simple hyperlink. All you have to take care of is the proper page layout.
[Tutorial] Using registered datasources in Calc:

#5

Andreas ,

Am 11.03.2012 19:48, tops wrote:

Hi all,

I have been using OO and LibreOffice prior to 3.5 with Base and Reports. Now the Reports in 3.5 can not be used any further, instead I had to try (despair) and create new Reports using the Oracle Reports Tool. IMHO this is a major leap backwards… It is very, very slow on my Mac (2.13 GHz Core2Duo), it does somehow not save my new generates Reports. If I choose the Table Model it will write the Column Heading repeatedly (I did not find a way to prevent this) on each Row. To Adjust the Columns and Rows is REALLY Painful. It is no longer possible to use Fields i.e. Page Numbers. Despite setting the Format to A4 Landscape the GUI will limit placing Labels further than 200mm of a Portrait A4 Page.

I prefer Calc as a simplified report engine. It supports more math functions than anything else. It provides the "natural" surrounding for charts. It supports dynamically linked, (conditionally) formattable import ranges, pivot tables, secondary filters and sort orders. It is the only report format which can be loaded from any form with a click on a simple hyperlink. All you have to take care of is the proper page layout.
[Tutorial] Using registered datasources in Calc:

http://user.services.openoffice.org/en/forum/viewtopic.php?f=75&t=18511

We came to the same conclusion: use Calc for reporting.
We recently solved to export resultsets to calc (doimport) thanks to Villeroy :slight_smile:
It would be handy to see some examples of how you make reports with calc. We have questions on how to Make headers , footers and grouping somes results ?

Greetz

Fernand

#6

Am 13.03.2012 12:11, Fernand Vanrie wrote:

It would be handy to see some examples of how you make reports with
calc. We have questions on how to Make headers , footers and grouping
somes results ?

Greetz

Fernand

Page headers and footers can not have database content unless you write a macro. But even with a macro the header/footer content will be the same on every page.
Of course you can have column headers and column footers with additional formulas.
Grouping can be done by the database software (SELECT ... GROUP BY ... ORDER BY ...) or by a pivot table (aka data pilot). Pivot tables are database reports. They follow the exact same logic as a SELECT ... GROUP BY ... query.
In addition, a pivot table can utilize the horizontal dimension by means of column fields.
http://wiki.services.openoffice.org/wiki/Documentation/OOo3_User_Guides/Calc_Guide/DataPilot

#7

Hi :slight_smile:
It is possible to have page numbers that will obviously be different on each page but i'm not sure that is particularly helpful as it just counts up by 1 so i'm not sure that a macro could really take advantage of that. 
Regards from
Tom :slight_smile:

#8

Andreas ,

Thanks for your interest
With "grouping" i meaned having the content of the grouped column on top or at the bottom off the items off the same group.
We where thinking on a macro who use a resulstet to fill the spreadsheet cell by cell with some counters who are making a groupheader or footer when the content of a column changed to a different vallue.
I look also a bit closer to the pivot tables

Greetz

Fernand

#9

Am 13.03.2012 18:05, Fernand Vanrie wrote:

Andreas ,

Thanks for your interest
With "grouping" i meaned having the content of the grouped column on top
or at the bottom off the items off the same group.
We where thinking on a macro who use a resulstet to fill the spreadsheet
cell by cell with some counters who are making a groupheader or footer
when the content of a column changed to a different vallue.
I look also a bit closer to the pivot tables

Greetz

Fernand

Pivot tables group like this:
CategoryA A1
    A2
    A3
CategoryB B1
    B2
    B3
    B4
CategoryC C1
    C2
    
A conditional format can do the same for flat import ranges. Apply number format "";"";"";"" when this value is the same as the previous value.

#10

Hi All

I hope someone can assist me please.......

While entering new data into my Database two days ago the system "crashed" with a "Module not responding" error.

After restarting it now will no longer open my DB. Whichever way I go, (Open the db file or start Base and try to conect), I get a box that says "Filter Selection" and presents me with a list of 20 or 30 options - none of which are anything to do with LibreOPffice Base!!

What "filter" must I selct or what must I do to get my Database going again??

I'm using LO 3.5.0 rc3 (the latest) on PClinuxOS 2011.09

Thanks a lot guys!!

Ian Whitfield
Pretoria

#11

Am 17.03.2012 11:41, Ian Whitfield wrote:

What "filter" must I selct or what must I do to get my Database going
again??

You need to restore your database from a backup. It is lost. This would not have happened with a true database connection.
The database wrapped in a single document (embedded HSQLDB) is just fine for drafts and demo purposes. If you really want to work with this bastard you should save a backup copy whenever you take a break. Close the file, wait a moment and make copy of it before you reopen the file.

#12

Ian Whitfield wrote via personal mail:

*Hi Andreas*

Thanks for your reply!! Appreciated - although I don't "like" the answer!!

I thought to ask you this rather 'off-list' in case.

You say I must use a "Real Database Connection" if I want to work with this
bastard!! By "Bastard" I guess you mean HSQLDB. OK - I'm fine with that. I do
like the front-end of Base and the ease of working with it but am happy to
change the DB Engine. (or anything else but would prefer a WYSIWYG development
screen like Base has)

But my problem is not knowing how to do this!! Or which is the best way to go.
What do you suggest I do and where can I get detailed instructions please?

My Database is _VERY_ important to me and I use it every day and all day. It
contains about 2500+ records of the Members of my Group including two small
photos on each record. Total about 70 fields per record and it is single user -
just me!!

Thanks very much for your kind help.

Hi Ian,
HSQLDB is an excellent, extensible, cross-platform, stable and fast database engine. Base is a set of tools to connect this office suite to databases which is best used when you avoid most of its most flashy and obvious "features".
The HSQL database wrapped into the Base document is the "bastard" which deserves to be handled with a maximum of caution.

[Tutorial] Avoiding data loss with built in HSQLDB

http://user.services.openoffice.org/en/forum/viewtopic.php?f=83&t=17567

Extracting an embedded HSQLDB manually under Linux:

http://user.services.openoffice.org/en/forum/viewtopic.php?f=61&t=46324

I'm sure this helps,
Andreas Säger

#13

I would suggest replacing the bastard (are we really talking like
this? :slight_smile: ) with MySQL or perhaps even better MariaDb (mebbe even
Postgres).

You can do this on Windows or Linux or even Mac (I imagine). Also
install phpMyAdmin (which also works with Maria I think) to give you
ease of access and control.

To make data security even better then perhaps something like
MySQLDumper to make regular backups would be good.

Then tell Base to talk to the database you've installed. Get your
data out of your backups and put it into the new database. Base may
make that really easy as, I think, you can have two Base databases,
one the old one the new, and drag tables across from the old to the
new (... but don't quote me on that).

From the "using Base" end of things practically nothing will have

changed. Your forms, queries, reports, &tc will work just the same
way (once you've hooked them up to the new data in the new database),
but you'll have your data in a much more robust environment that you
can easily, regularly back up.

For this much installation and use you'll have very little new to
learn (phpMyAdmin makes it really simple, as does MySQLDumper).
There may be other things you'd like to delve into, but that's an
added bonus.

Regards
Mark Stanton
One small step for mankind...

#14

Am 17.03.2012 21:20, Mark Stanton wrote:

I would suggest replacing the bastard (are we really talking like
this? :slight_smile: ) with MySQL or perhaps even better MariaDb (mebbe even
Postgres).

Replacing one excellent databse engine with another excellent (much bigger) database engine will not solve any problem.

I run a HSQLDB server and it is the most reliable and fast little software on that Windows machine. On my Linux laptop I run the exact same Java software in cached mode, just like the embedded one but as an independent process with database files outside the Base document.

The "embedded HSQLDB" works like an extension. Every time you access the package data, the database gets installed to <office temp>. When you close the last connection to the installed database, the database is repackaged into the .odb "document". As the database grows, this thread consumes more and more time and during that time there must not happen anything serious to the office suite.
IMHO HSQLDB is a well thought choice for a multi-platform database. However, databases should be installed as extension packages so the user data won't be shuffled this way.

#15

Hi :slight_smile:
I think the point Mark was making was to switch away from using the embedded back-end to pretty much any external one.  Which is what you often suggest too. 
Regards from
Tom :slight_smile:

#16

I would suggest replacing the bastard (are we really talking like
this? :slight_smile: ) with MySQL or perhaps even better MariaDb (mebbe even
Postgres).

You can do this on Windows or Linux or even Mac (I imagine). Also
install phpMyAdmin (which also works with Maria I think) to give you
ease of access and control.

To make data security even better then perhaps something like
MySQLDumper to make regular backups would be good.

Then tell Base to talk to the database you've installed. Get your
data out of your backups and put it into the new database. Base may
make that really easy as, I think, you can have two Base databases,
one the old one the new, and drag tables across from the old to the
new (... but don't quote me on that).

"schema" (database) first. Then I gave myself as a user complete access
to the database (authority to execute all commands to it). After that, I
could drag and drop tables from an embedded database (Base) to the odb
file used to access MYSQL. Finally, when naming the table, it took the
format: "database"."table". Queries copied easily, but they needed to be
modified using SQL: all the FROM clauses required the same format for
the table names. Reports copied easily since they were based on queries
whose names I did not need to modify. Forms would not copy between
databases at all.
     I use Ubuntu 11.10 which has MYSQL Administrator in its
repositories to handle administrative duties.

>From the "using Base" end of things practically nothing will have
changed. Your forms, queries, reports, &tc will work just the same
way (once you've hooked them up to the new data in the new database),
but you'll have your data in a much more robust environment that you
can easily, regularly back up.

    One observation: Using Base as the front-end greatly increased the
speed of changing records in the forms! This is nice!

--Dan

#17

Interesting to know!

For my part, by precaution, all my data are kept in separate dbf tables, linked to an odb file.

Why do I use dbf? Standard format making it portable to all softs, notably GIS softwares. Old archives can also be read even if you changed your soft.

There is not much information on long term use of databases, let's say more than 10 years (e.g.: keeping adresses or bibliographic data).
The file can get corrupt. But worser, some data inside the file can get corrupt, without changing the structure of the dbf file,and you just notice it when you query these data.

I heard once that MS-access is not stable enough in long term use. And, sometimes, odb file can get problems (for ex. shutdown of windows while some forms are open in the odb file).
But, when the tables are kept in separate files, recovery is easy, using a backup copy of the odb file.

Using LibreOffice 3.3.2 on windows Vista.

NB: I came to openoffice several years ago mainly because of its good integration with databases (standalone use).

I wonder if my way of using Base is optimal, trying to find the way among the many possibilities offered by Base and Calc.

Many thanks to the developers,

Paul

#18

I think the point Mark was making was to switch away from using the
embedded back-end to pretty much any external one. Which is what
you often suggest too.

Yes indeed.
Can HSQLDB be used as an external engine? I thought not, which is why I didn't
include it.

Mark Stanton
One small step for mankind...

#19

Hi :slight_smile:
Yes, apparently HSqlDb is a really decent light and fast back-end
http://hsqldb.org/
It's just the tweaks in the embedded version that make it go wonky.  Of course it is written in Java so it might be worth avoiding on that basis. 
Regards from
Tom :slight_smile:

#20

The problem with using dbf files is that you no longer have a
relational database. Because Base is then dealing with files not a
database engine it will only use one file at a time and will not
relate them.

Mark Stanton
One small step for mankind...