Apache OpenOffice Community Forum

What is the step by step procedure to sum columns in a report?

OpenOffice / LibreOffice Base provides two different report writer options.

1. The default options ( if you have NOT downloaded as in Number 2 below ) report writer
2. Oracle Report Builder ( also known as ORB ) -- In my opinion the far superior report generation option

Please see the link below, with the POST by Sliderule

http://forum.openoffice.org/en/forum/vi ... 1&p=250958

From this link, Sliderule's post includes links to download and install the ORB extension, and, to download a SAMPLE Base file, that you can see it working.

The 'sample' reports . . . include . . .

1. WorldAreaReport - By Area ( Simple listing )
2. WorldAreaReport - By Continent - Area ( Report with subtotals and Grand Total )
3. WorldAreaReport - By Continent - Country Name ( same as above but different sort order )
4. HSQL_Database_Index_Information ( Database Index Documentation )
5. HSQL_Database_Table_Information ( Database Table Documentation )
6. HSQL_Database_Table_Information_Totals ( Database Table Documentation and Grand Totals )
7. Template - Report ( Sample Template you can drag and drop to your *.odb )

Also, just as an FYI ( For Your Information ) . . . for a TUTORIAL

1. Getting_Started_With_the_Report_Builder_Extension:
   
   http://wiki.openoffice.org/wiki/Documen ... _Extension
2. ORB Report Builder Documentation:
   
   http://wiki.openoffice.org/wiki/SUN_Rep ... umentation

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Oracle Report Builder would not load for 3.4.1 or I am doing something wrong. Not sure; also Report Builder Extension was a no go. I tried loading it but did not find it in the Extension Manager.

ACCS wrote:Oracle Report Builder would not load for 3.4.1 or I am doing something wrong.

How to install extensions:

http://extensions.services.openoffice.o ... to_install

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Sliderule,
Appreciate all your help unfortunately, It loads but gives this error message when you try to enable it. MySQL Connector 1.0.1 Error: The status of this extension is unknown

You said:

ACCS wrote:It loads but gives this error message when you try to enable it. MySQL Connector 1.0.1 Error: The status of this extension is unknown

I do NOT understand, the Oracle Report Builder has NOTHING to do with the MySQL Connector. Are you sure you have the correct extension, from the link below?

To download Oracle Report Builder, press the Get It icon at:

http://extensions.services.openoffice.o ... portdesign

Version: 1.2.1-rev2 Date: 2010-Dec-14 Size: 3.3 MB

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

I tried loading the Oracle Report Builder and it would not run. I then went to extension manager and found the MySQL Connector. Downloaded it to my computer and it loaded in the extension manager. When it finised it had two buttons one button had "Enable" on it which I click. When I click it, is showed it was loading and at the end of the load it gave the error message MySQL Connector 1.0.1 Error: The status of this extension is unknown. I could not open it or use it, it went the same for the ORB.

I will repeat, one last time, the ORB ( Oracle Report Builder ) has nothing to do with MySQL nor MySQL Connection extension.

I have no idea how / why you are talking about the MySQL Connection extension in this forum post, nor, why you downloaded it unless you are connecting to a MySQL database. But, even if so, it has nothing to do with the topic of this forum post / question.

Therefore, I obviously cannot help you, so, perhaps someone else can, just, not me.

Sliderule

The Ariel's report builder built with AOO 3.4.1downloadable from: http://people.apache.org/~arielch/exten ... rtbuilder/ is good for AOO 3.4.1 older versions can work or not.
Report builder not supported by AOO due to GPL lincense in underlaying pentaho files. One big problems with this report builder the broken chart support.
The mysql driver has same GPL related problem, use 1.1.0 version with AOO 3.4.1, it solves lot of bugs in the earlier versions: http://code.google.com/a/apache-extras. ... loads/list

Report builder not supported by AOO due to GPL lincense in underlaying pentaho files. One big problems with this report builder the broken chart support.

It does run OK in AOO 3.4.1 and is in the available list of extensions, but as r4zoli says the chart option does not work, so I continue to run it with OO 3.3.

The Ariel's report builder does not seem to be in AOO's available list of extensions. Is there a reason for this or is it already included as the report builder for AOO 3.4.1 and if so does it support charts?

When I had a problem with installing the Oracle Report Builder, I found that I fixed the problem by repairing/reinstalling OO.

Regards, Nocton

Almost everything you can do with the help of the report builder can be done with Base+Calc, except for reporting picture fields.
Calc makes it very easy to aggregate and to calculate columns and rows likewise. Charts are just a matter of course.

That may be the case, but you surely can't make nice reports linked to Base forms, menus, macros and tables as you can completely within Base. Even if you can, it must be much more complicated to do and one then has more files in the 'database' application. For non-technical users I like to provide a straightforward menu with choices of things to do - data entry form, reports, emailing. When they ask for a report, they don't expect to end up in spreadsheet and then have to do something else to print a report.

I am happy with Base as an application, but don't like unexpected things like finding that an important extension like the Oracle/Sun report builder suddenly doesn't work properly. A decent report builder is essential and should be 'built-in' because the reports are the essential output for most database applications. [As an aside, running AOO 3.4.1 I have found that an embedded database, synced over 3 computers via Dropbox has given no problems/crashes/loss of data over 6 months. I converted it to embedded from server mode, because of difficulties in setting up server mode on one PC and to simplify set-up for non-technical users.]

Regards, Nocton

Nocton wrote:That may be the case, but you surely can't make nice reports linked to Base forms, menus, macros and tables as you can completely within Base.

Of course you can.

Even if you can, it must be much more complicated to do and one then has more files in the 'database' application.

OpenOffice has no such thing as a "database application". It's all about linking table data to office documents and Calc is by far less complicated and more scriptable than any other component.

Villeroy, I am not very familiar with Calc, but I have written many large spreadsheet applications with MS Excel, including forms and VBA code. Unless I have missed something, I do not see how Calc/Excel can produce easily reports like Base/Access with their sorting and grouping options and linked to queries. I have done similar in Excel, but only by using extra VBA code. Of course one can also make up worksheets organising the data how one wants. But how much more work than a line of SQL/a query. Since you've got to have a relational database to link all the tables and make the queries, it seems counter intuitive to me and unnecessary extra complication to then involve a spreadsheet to do some of the work unless it is absolutely necessary. Thus Writer links to Base to produce mail-merge letters. It's 'horses for courses' and if I need a relational database then I'd prefer to do everything in Base or Access - although Base is clearly not as fully-featured as Access.

Since we have moved right off the original topic, probably best to leave it there as we perhaps have different ideas of a user-friendly application, however it is achieved. When my users click on a menu item in a (Base) form they expect to go straight to a report ready to print, perhaps via the option of setting limits like dates on the way.

Regards, Nocton

Get a Calc sheet
Hit F4
Drag your query or table icon from the left pane onto a cell.
(if your db is not registered: Tools>OPtions>Base>Databases...)

Now you have a linked import range named "Import1".
Let's tweak this a little bit:
Data>Define...
[More Options]
Check all the extra options.
[Modify], [OK]
Append calculated fields adjacent to the right and aggregations adjacent to the bottom.
Add (conditional) formattings, charts and anything Calc has to offer.
Apply an appropriate page style for the right page layout.

When the database content has changed:
Click any single cell in "Import1"
Data>Refresh

##########################################################

The second method is most powerful and not availlable in most database applications:
Data>Pivot Table>Create
Choose a query or table of your registered datasource.
Create a draggable "rubber table" with aggregations for all category fields in rows and columns.
http://wiki.services.openoffice.org/wik ... /DataPilot

P.S: This is what a pivot table (aka data pilot) does in SQL terms: This type of report is interactive. You can drag around all the grey field boxes directly on the sheet, you can modify the WHERE filter and the secondary HAVING filter.

Thanks, Villeroy, I have done most of those suggestions in the past for specific purposes, e.g. to produce a spreadsheet for mass mailing. But I don't see them as very user-friendly / easy for non-technical users. As mentioned they want a one-click result. Even having to refresh data is something extra which users may not remember.

This is as user friendly or unfriendly as a "normal" report.
1) You open a well prepared spreadsheet with the linked but unsaved import range. Could be a template as well.
2) You confirm the prompt: "This document contains links to unsaved data. Update?" [Yes | No]
3) Without further intervention you get a printable report with additional aggregations, calculated fields, charts, statistics and well defined print ranges.

Instead of 2), a most trivial macro can update all import ranges and pivot tables fully automatically on open or on sheet activation. So you open the Calc document, wait a few seconds and have the printable record with no further intervention.

That whole stack of Java, Pentaho, Writer and Base is so severely underdeveloped, disintegrated, unstable and complicated to set up that I gave up on it in favour of Calc as a more powerful but less good lookin' report engine.