Base Subreports via writer?

English
#1

WIN 10 LO 7.1.2 HSQL 2.5.1

Hi,

Ok base can not handle sub reports unless one resorts to union query and labels as explaind in this post.

Done that once but its sort of agony…

Is there a way to export 3 queries or views to writer and assemble a 3 table report with totals, subtotals, averages etc.
For example the resulting report could look like

BMW Isetta Period from to

Regulatory

Rego 11111 from to with fee
Insurance ditto
Inspection(s) ditto
Total sum

Service & Maintenance

10000k blah
20000k blah
30000k blah
Tyre Replacement …
Total sum

Fuel crosstab by month

Auxillary Expenses
Tolls …
Fines …

Total

Total for vehicle = sum of all

Thank you for ideas

#2

Just an idea, but my aporoach would be to use calc.

You can easily drag a query or table to a location in a spreadsheet, where it can even gets updated.

Now you have all the power of a Spreadsheet availabe and maybee it will be sufficient to print from here.

If really necessary (for example to be integrated in a book) you can embedd the Calc-sheets in writer.

#3

@Wanderer

Thank you for your swift response and suggestion. Worthwhile to pursue for myself, however
not practical for the end-user since no CALC knowledge is assumed, they would be prompted for authentication for every table or query and if the spreadsheet were saved they also would need to know how to refresh the ranges.
As such I am looking for a way to push the data into writer and call the procedure from the report menu dialog.

#4 
Sub refreshDBRanges()
for each dbr in ThisComponent.DatabaseRanges
	dbr.refresh()
next
End Sub

assigned to the “Document open” event refreshes all database ranges and prompts for the password only once.

#7

Thank you Andreas, the refresh sub will come in handy on several projects

#5

I would prefer to create one view, which will show all this content in different columns. So you need a column for 10000km, 20000km and so on, also columns for the special “TotalSumKm” …

#6

In other words: a pivot table in Calc grouped by 10,000 km.

#8

Hmm, interesting thought thanks

#9

Hi!
It is posible using macros.
We can check the text in a cell and put a new table above It, so this new table has a new query as a data source, but depending to the text of cell we checqued previously.
However this could be a nightmare with ORB. It depends on the inicial desing.
A little example could be useful to study It.
Sorry because of may English!
Bye!

Ratslinger is terrible!
He made an enormous study!, However he did not include the old fashioned system which is working in OpenOffice.
It could be opened by Libre Office, but you have to start It with OpenOffice.
It is ‘easy’ to put charts and manipulate tablets by Code.

Here you have several examples…in Spanish!, Sorry.
Bye!

Border / "cell lines" in reports in Base
Libreoffice base create navigation tabs for subforms
#12

@ Longi
Thank you for your reply. Your English is is pretty good, its not my mother tongue either, these days I am still struggling with Spanish conjugations

Will come back a little later with a mockup once the design is at a more advanced stage.
Saludos

#20

Why would I include OpenOffice which requires macros to do what I have already done in LibreOffice without any code? Besides, the study was done years ago just to get an idea of the independent software one may use. It just happened to be of use here.

#21

Sorry!
I only said that because is only a diferent tool (not better).
In may case, (I am fool, I know It!), I prefer not use ORB, maybe because of my lacke of knowledge, but that is not important, I think!
I admire your knowledge and your dedication!
Bye!

#10

I don’t understand. Where is the agony after you have designed the report as shown in the example document? I think, this is a very elegant solution covering everything you need for a correct invoice.
And the best thing is: It works without a single line of stupid Basic code!

1 Like
#11

@ Villeroy
@ Ratslinger

Yes it is a very excellent and elegant solution if the db is not too complex. However depending on the structure multiple support queries, union and views are required which can drastically slow down form loading and report generation. In my previous application I had to cater for 4 subreports and 15 calculated controls in addition to the customer information. The form also included macros to enable switching calcs between Mwst inclusive, exclusive, change of discount as well as commission rates. The final report had functions to display a textbox displaying Paid, Overdue.
Have not worked out wether the speed degradation is due to LO or HSQL. Might try with Postgres next time.

My current project will also include 2 crosstabs as well as a chart in addition to other subreports. As such I thought of using writer instead of the base report builder which tends to crash literally dozens of times during design time.
Not keen on calc as pivot functionalty is somewhat limited and does not even retain the formatting on refresh.

#13

Charts in reports are broken. Don’t work at all. I think you need another frontend.

#14

@ Villeroy
Well, Robert Grosskopf has very nice chart examples for base forms which I believe could easily be embedded in a Writer doc report.

#15

Either you deal with what is available in ReportBuilder/Writer/Calc or obtain a different report generator. Do not see any great enhancements on the horizon. Of the ones I have tried, none of the no cost ones handles sub-reports. You can get a commercial product (Jasper Reports for one) which will handle that and cost you a considerable amount of money!

Some I have tested:

Oracle Report Builder

The Oracle Report Builder in Base is definitely the easiest to start using. This is mainly due to the fact no connection to a database is needed since it is already connected. It is also the only one which can generate reports from an embedded HSQL database. Only one input table/view/query is allowed at a time for report generation. There are many limitations regarding the format of the body of the report itself.

Data Vision

DataVision is the only tested product which allowed multiple input tables/views. It does not support queries from base. It was tested using an HSQL split database. It does not support embedded databases. The builder is fairly simple using a drag and drop method. Formatting is limited. The product is quite outdated and hasn’t been updated since 2007.

Pentaho Report Designer

Pentaho Report Designer has a graphical interface using drag/drop method for fields. Connection was more difficult.
You can use SQL in the Data Source interface to generate queries. It worked with all the queries copied from Base. The report has a variety of outputs including: PDF, Excel, CSV and others. It includes many sample reports which is helpful seeing how to construct something. It also supports styles (although I didn’t try this). Complex reports and charts can be created.

Dynamic Reports

Dynamic Reports is based upon the Jasper Reports engine. It is Java based and must be run in another product such as Eclipse (used for test). This was the most difficult to get first results. There is a step by step process to create a new report (fairly helpful) but more importantly not much on how to get to the point of actually trying this. For one reason, not knowing Java and also you must learn the interface (Eclipse). There is no graphical interface for report creation – insert code and view result. Change and retest. Connections are made within the code.

There are many example reports included but it takes a great deal of effort to begin to use even the simplest of features. Each step is a challenge. Since there is no drag and drop feature, you must either remember how to do something, refer to the documentation or get the code from a sample. Difficult to use but good results.

After working with this product for two days (the first spent mostly getting connections to work), I am finding this easier to work with than those generators with a drag and drop interface. Lining up fields is no longer a problem and column widths are checked before display of the report. It would be nice if there was some type of preview but really not a big deal.

Now I believe the tougher part of this is finding what code does what. It took about a half hour just to find out how to page break after a group change. In other report writers this is simply a setting in properties. This is code and not found in the documentation, on-line search, or their forum. I found it in a sample report.

Jasper Studio Community

This is a very well rounded package. You set up data connections as in other products. It has all the abilities of Dynamic Reports with the added drag & drop GUI. There is also a report server available. The report server runs through a browser and utilizes PostgreSQL for report storage. You can create/update reports in Studio and then post to the server from there. The Server must also have data connections set up for the reports. A change in a Studio data connection requires a change in the Server data connection. The server is then accessed by the user to generate reports based upon current data.
Of note, running both Studio and Report Server together will consume over 2.5mb of memory.
Have not yet seen a way to run reports directly from a user application. This may require an upgrade.

Border / "cell lines" in reports in Base
Report Header above page Header
#16

Have been creating charts from scratch in Base forms for years. Have also done this is Writer but there it presents an issue if the chart needs updating. Currently, and almost all in Base, my personal charts combine the method by Robert, my method and storing all necessary SQL in a table to be read in and processed in macros.

How and where you create charts will determine the complexity involved. Easily is a term I would use cautiously here.

#17

@ Ratslinger

Hi,

Thank you for the comprehensive report generator details. Had a play with Jasper in the recent past, looked promising.

#18

It was mainly to show there is no easy way to do what you want even with other resources.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.