Newbie questions

Getting your data onto paper - or the web - Discussing the reports features of Base

Newbie questions

Postby RhinoCan » Mon Jan 25, 2016 7:12 am

I'm rather new to Base but I'm quite familiar with databases in general.

1. I've got a rather simple report based on a query that includes two run-time variables. When I run the query directly, it prompts for the variable values and the query executes successfully. However, when I base a report on that same query and then run the report, it's very hit or miss whether the report will prompt for the variables. I've got one report based on the query where it always prompts for the variables but the other reports based on the same query NEVER prompt for the variables, they just show the default report with the Latin text. What am I doing wrong?

2. When I use an ORDER BY AGENCY, ORDER_DATE clause in my query, the report does NOT show the rows such that all the rows of a given Agency appear together. I get a few rows of the first agency, then a few rows of the second agency, then the remaining rows of the first agency, then the remaining rows of the second agency. I've tried omitting the ORDER BY and controlling the sequence entirely from within the report but that never gives the desired result either.

3. When I've created a report via the wizard and saved it, how do I change the things that I set within the wizard after I've completed the save? For instance, if I want to change the sort order after I've saved, how do I do that? Right-clicking on the report and choosing Edit lets me do things regarding the format of the report but I'm darned if I can find a way to change sort sequence or any of the others things I chose in the wizard. How can I do this?

4. How can I add totals to my report? The query I have lists the details of sales activity pretty well for the most part but how do I total all the commissions for each agency and count the number of sales for each agency? I don't see any way to do that?
OpenOffice 4.1.2 and Libre Office 5.2.3.3 on Windows 10
User avatar
RhinoCan
 
Posts: 80
Joined: Fri Jun 10, 2011 11:36 pm

Re: Newbie questions

Postby Villeroy » Mon Jan 25, 2016 2:57 pm

There are 2 types of Base reports. The old style OpenOffice reports with plain Writer tables and the advanced reports created with Oracel Report Builder extension. The latter supports charts, pictures, multi-level grouping and sub-reports. LibreOffice comes with ORB preinstalled, does not offer the creation of old style reports but still opens old style reports.
When the old style report does not start, you should open the underlying query and check the "direct SQL" option (menu:Edit>"Run SQL directly"), save the query and re-run the report.
If you want to include totals, you can compose a UNION SELECT query in direct SQL mode merging the detail records and the totals.

I prefer to use Calc as alternative type of report engine for interactive reports: [Tutorial] Using registered datasources in Calc

You may be interested in a macro template of mine which produces a flat table report in a stand-alone Writer document: [Writer] Stand-Alone Database Reports similar to the old style reports but with manual setup and outside the Base document.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26632
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Newbie questions

Postby longi » Mon Jan 25, 2016 5:20 pm

Hi!
Villeroy is right, and I have also to say that reports in the old fashioned way have several tricks to deal. However, if you use macros, you can do a lot of things.
In this thread you can get lots of particular solutions to particular problems:

https://forum.openoffice.org/es/forum/viewtopic.php?f=70&t=11605.

It's a pitty that all explanations are in Spanish, but my English is so bad that it could be better if you know somebody to translate it. If I translate it, you'll never understand anything.
All your questions are answered in a report called "Consideraciones iniciales", which is a kind of help to the database user.
If you want, I can try to translate it, but I don't promise any good result.

Good luck! :D
OpenOffice 4.1.5 on Windows 10
LibreOffice 5.1 on Windows 7
LibreOffice 6.0.1 on Windows10
longi
 
Posts: 107
Joined: Mon Jul 15, 2013 5:04 pm


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest