[Solved] Changing source table/query of report

[hman2]

I can't. The query is not there. The tab with queries is empty.

[Villeroy]

Delete the report.
Create a new one.

[UnklDonald418]

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)

[Villeroy]

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.

[hman2]

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

[hman2]

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

[hman2]

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

[Villeroy]

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.

[hman2]

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

[Villeroy]

Yes, it is weird and it is deprecated. Delete it and use the new report builder, preferably with LibreOffice.

[UnklDonald418]

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.

[hman2]

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.

[hman2]

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