Base and new Report Builder in 3.5

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.

On 11/03/2012 20:48, tops wrote: 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.


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Hi :)
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 :)


--- On Mon, 12/3/12, Ian Whitfield <[hidden email]> wrote:

From: Ian Whitfield <[hidden email]>
Subject: Re: [libreoffice-users] Base and new Report Builder in 3.5
To: [hidden email]
Date: Monday, 12 March, 2012, 8:10

On 11/03/2012 20:48, tops wrote: 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.


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

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


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

  Andreas , We came to the same conclusion: use Calc for reporting.
We recently solved to export resultsets to calc (doimport) thanks to
Villeroy :-)
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




--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

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


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Hi :)
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 :)


--- On Tue, 13/3/12, Andreas Säger <[hidden email]> wrote:

From: Andreas Säger <[hidden email]>
Subject: [libreoffice-users] Re: Base and new Report Builder in 3.5
To: [hidden email]
Date: Tuesday, 13 March, 2012, 11:43

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


-- For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

  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






--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Am 13.03.2012 18:05, Fernand Vanrie wrote:
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.



--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

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



--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

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.


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Ian Whitfield wrote via personal mail:
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


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

I would suggest replacing the bastard (are we really talking like
this? :-) ) 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...



--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Am 17.03.2012 21:20, Mark Stanton wrote:
> I would suggest replacing the bastard (are we really talking like
> this? :-) ) 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.


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Hi :)
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 :)


--- On Sat, 17/3/12, Andreas Säger <[hidden email]> wrote:

From: Andreas Säger <[hidden email]>
Subject: [libreoffice-users] Re: Urgent Base problem
To: [hidden email]
Date: Saturday, 17 March, 2012, 21:14

Am 17.03.2012 21:20, Mark Stanton wrote:
> I would suggest replacing the bastard (are we really talking like
> this? :-) ) 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.


-- For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

On Sat, 2012-03-17 at 20:20 +0000, Mark Stanton wrote:
     From my experience this evening, I had to create a
"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




--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

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


Le 17/03/2012 11:58, Andreas Säger a écrit :
--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

> 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...



--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Hi :)
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 :)


--- On Sun, 18/3/12, Mark Stanton <[hidden email]> wrote:

From: Mark Stanton <[hidden email]>
Subject: Re: [libreoffice-users] Urgent Base problem
To: [hidden email]
Date: Sunday, 18 March, 2012, 7:56

> 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...



--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

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...



--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted