[Solved] Changing source table/query of report

Discuss the database features

Re: [Solved] Changing source table/query of report

Postby hman2 » Mon Nov 30, 2020 6:05 pm

I can't. The query is not there. The tab with queries is empty.
OpenOffice 4.18 on Linux Mint 18
hman2
 
Posts: 63
Joined: Sun Nov 22, 2020 11:51 pm

Re: [Solved] Changing source table/query of report

Postby Villeroy » Mon Nov 30, 2020 7:13 pm

Delete the report.
Create a new one.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30096
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Changing source table/query of report

Postby UnklDonald418 » Mon Nov 30, 2020 7:26 pm

In the Reports area of the main Base window
Right click on your report and select Edit to open the report in the Design Mode.
Select the Form Navigator (its on the Form Design tool bar, usually near the bottom left of the window)
On the Form Navigator dialog right click on ReportSource
select Properties to open the Form Properties dialog.
select the Data tab
The Content type property will display what you are using for a data source, Table, Query of SQL command.
The Content property shows the name of the Table or Query, or an editable SQL command (Query)
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.8 & LibreOffice 6.4.7.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1405
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] Changing source table/query of report

Postby Villeroy » Mon Nov 30, 2020 7:48 pm

As already described in detail in this topic and in 2008 by Drew Jensen in a linked topic. Editing an old style report is rather complicated which is why I recommend to create a new one from scratch unless you did extensive layout work. And yes, you get better results with new style reports.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30096
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Changing source table/query of report

Postby hman2 » Mon Nov 30, 2020 7:56 pm

Thanks. Now we are getting closer. This toolbar was deactivated. Upon reactivation, I did find a form navigator, and in it Properties of Report Source. Content type was seemingly set to SQL command, but this dropdown menu always displays which of the three settings (SQL, query or table) was displayed the last time. I was astonished that each of three do not show anything in "content".
Attachments
Bildschirmfoto vom 2020-11-30 18-50-37.png
Properties of form (in German localization)
Bildschirmfoto vom 2020-11-30 18-50-37.png (27.48 KiB) Viewed 1916 times
OpenOffice 4.18 on Linux Mint 18
hman2
 
Posts: 63
Joined: Sun Nov 22, 2020 11:51 pm

Re: [Solved] Changing source table/query of report

Postby hman2 » Mon Nov 30, 2020 8:00 pm

Ah, when I go into the properties of Command (and not of Report Source) then I DO find well-known SQL syntax. A proper SELECT, at last. Now lets see whether that is true for sorting, too...
OpenOffice 4.18 on Linux Mint 18
hman2
 
Posts: 63
Joined: Sun Nov 22, 2020 11:51 pm

Re: [Solved] Changing source table/query of report

Postby hman2 » Mon Nov 30, 2020 8:02 pm

In Properties of Sorting it's no longer standard SQL syntax (or a breed I haven't encountered yet :-). But it is kind of intuitive, listing field names separated by semicolons, each having ASC appended with a colon. Looks like kind of ascending/descending toggle switch...
OpenOffice 4.18 on Linux Mint 18
hman2
 
Posts: 63
Joined: Sun Nov 22, 2020 11:51 pm

Re: [Solved] Changing source table/query of report

Postby Villeroy » Mon Nov 30, 2020 8:19 pm

What you have is a Writer document with a form and the skeleton of a table to be filled with data.
The form is NOT connected to the data source. The form is just a container of hidden controls. Hidden controls store Name=Value tokens for macro programming and for these old style reports. You find them in the form navigator (Toolbar "Form Design", 5th button).

 Edit: Old style reports with sorting do not work with AOO. The loaded report appears with the dummy record as shown in design mode. This is just another bug that has been fixed in LO. 
Attachments
Bildschirmfoto von 2020-11-30 19-18-16.png
Form navigator of an old style report showing the hidden controls that configure the report
Bildschirmfoto von 2020-11-30 19-18-16.png (17.1 KiB) Viewed 1903 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30096
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Changing source table/query of report

Postby hman2 » Mon Nov 30, 2020 9:13 pm

Hm, my report has worked perfectly. I selected eight fields from the table and had two sorting options, which were correctly executed. Now I want to add a third level of sort. But first I will make a backup. Better safe than sorry :-)

Btw, I think it is a rather odd concept of fetching data with a proper SELECT, but then do the sorting with macros instead of this giving this task to the SQL engine. SQL engines do sorting better than anything, because they take care of things like collation etc...
OpenOffice 4.18 on Linux Mint 18
hman2
 
Posts: 63
Joined: Sun Nov 22, 2020 11:51 pm

Re: [Solved] Changing source table/query of report

Postby Villeroy » Mon Nov 30, 2020 9:45 pm

Yes, it is weird and it is deprecated. Delete it and use the new report builder, preferably with LibreOffice.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30096
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Changing source table/query of report

Postby UnklDonald418 » Mon Nov 30, 2020 10:44 pm

It appears that when you designed the report you relied upon the Report Writer to do the sorting rather than a pre-designed Query.

Many years ago (soon after Sun purchased the software) the developers found that that report writer was lacking in a number of ways and designed the Sun Report Builder extension (renamed to the Oracle Report Builder ~2008). I suppose for legacy considerations they left the report writer in place expecting to replace it when a major update was completed. But when Oracle purchased Sun, development stopped and the software was transferred to the Apache Foundation. Apparently, AF could find no volunteers to work on the Base module, so it has remained unchanged for over a decade and unsuspecting newcomers still attempt to use the report writer, often rediscovering its shortcomings.

The developers at The Document Foundation have made some updates to the LO Base module, and one of the first was to ditch the old report writer.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.8 & LibreOffice 6.4.7.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1405
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] Changing source table/query of report

Postby hman2 » Mon Nov 30, 2020 11:28 pm

Uppsa. I tried to append the missing field name in the value of "Sorting". No effect. Then I wrote something just weird in there, also no effect. Analysis shows: The value isn't used at all. Surprise!

Actually the SELECT in Command DOES the sorting!! The displayed length was only too short and I didn't scroll it to the end.
In fact, sorting IS done by the SQL engine. The full SQL command DOES read

SELECT mydatase.myfieldname [x N] FROM mydatabase ORDER BY mysortingfieldname1 .. mysortingfieldname M

As it should be... It did not even specify sorting direction, because my two sorts were both ascending, which is the default in most SQL dialects...

It seems that the Sorting key isn't used. Or hast just informational character.
OpenOffice 4.18 on Linux Mint 18
hman2
 
Posts: 63
Joined: Sun Nov 22, 2020 11:51 pm

Re: [Solved] Changing source table/query of report

Postby hman2 » Mon Nov 30, 2020 11:38 pm

Yepp, and that's the solution. All that was necessary was to add another clause of "mydatabase"."mysortingfieldname3" to the end of the value of Command in the properties. And not make any typos, with typos (of course) the SQL engine rightfully complains. Quick and easy corrected.

I think it was actually quite intuitive, once I learned where these skeletons were buried :-) Of course, this would not be a method for complete newbies, but with a little bit of SQL knowledge a solveable problem...

Thanks for helping me on the trail.

hman2
OpenOffice 4.18 on Linux Mint 18
hman2
 
Posts: 63
Joined: Sun Nov 22, 2020 11:51 pm

Previous

Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests