- let's have a query where rows are already in correct order: SELECT "Categories"."Name" AS "Category", "Projects"."Name", "Projects"."Description" FROM "Projects", "Categories" WHERE "Projects"."CatID" = "Categories"."ID" ORDER BY "Categories"."ID" ASC - group the report form above query by "Category" --> the report can only be ordered alphabetically from "Category" which is wrong order.
If you want to order a report you have to group the report with the field you will have ordered. Your filed must be "Categories"."ID" - which must be a filed in the SQL-code. You could put the field "Category" in the group-area of "ID". Then you have ordered by "ID" and shown only the "Category". I have set the Importance to "enhancement", because it works in this way. And set the Status to "new", because it can only be ordered by the field of the group.
(In reply to comment #1) > You could put the field "Category" in the group-area of "ID". Then you have > ordered by "ID" and shown only the "Category". Thanks for the advice. But what to do, if theoretically there is an order, which can not be expressed by 'ascending' or 'descending' of an existing field? In that case, an enforced order IMO is a defect. > And set the Status to "new", because it can only be ordered by the field of the > group. I would say: ... because post/re-ordering by the report-builder can not be disabled.
How would you make a sort statement without a field, which must be sorted? You only have to show this field in the query or SQL-Code. Ist must not be used to display anything in the report. It must only be used for sorting. The only problem I see is, that you can not get a report with non-graded groups. But I don't know, if anybody would need such groups.
(In reply to comment #3) > How would you make a sort statement without a field, which must be sorted? Yes, you can't, but one could have a data source with no ordering field, but wants to create a report, ordered as is. Examples: - table with given order - imported/bind spread sheet - query form external db where user has no access to create additional query with ordering field Regardless of above, in any database operations, the "order by" facility is optional, why should it be mandatory in report builder? > The only problem I see is, that you can not get a report with non-graded > groups. But I don't know, if anybody would need such groups. See above + why should user create a "duplicate" query additionally to a still needed query without the ordering field?
Have you tested the sorting with "analyse sql: no"? https://bugs.freedesktop.org/show_bug.cgi?id=33240 Seems to be the same idea in another way.
This is a reasonable request from an "advanced user's" point of view, I've at times wished for a similar thing. I'm not sure how to present it UI-wise, though. 1) Separate "grouping" and "sorting" in different dialogs? I think that is a bad idea, too confusing for non-expert user, that one has to put the same field in sorting *and* in grouping. 2) A boolean (yes/no) option on each group/sort field: sort yes/no? In that way, one can do grouping on a field without requesting sort on that field, and the UI is relatively clear. But what would happen if one enters the following: GROUP ON field foo / no sort SORT BY field bar Does it do the same as: SORT BY field bar GROUP ON field foo / no sort because the whole query is sorted by "bar"? I'm not too eager, confusing. Does it first group by "foo" and *within* each group, it sorts by "bar"? Indeed a work-around now is to include the ID in the result query, but sim
This is a reasonable request from an "advanced user's" point of view, I've at times wished for a similar thing. I'm not sure how to present it UI-wise, though. 1) Separate "grouping" and "sorting" in different dialogs? I think that is a bad idea, too confusing for non-expert user, that one has to put the same field in sorting *and* in grouping. 2) A boolean (yes/no) option on each group/sort field: sort yes/no? In that way, one can do grouping on a field without requesting sort on that field, and the UI is relatively clear. But what would happen if one enters the following: GROUP ON field foo / no sort SORT BY field bar Does it do the same as: SORT BY field bar GROUP ON field foo / no sort because the whole query is sorted by "bar"? I'm not too eager, confusing. Does it first group by "foo" and *within* each group, it sorts by "bar"? IMHO, it seems natural from the user's POV, but it would be delicate to implement, unless LibO does the sorting itself; I don't think it would be possible to delegate the sort to the database engine in this scenario. 3) Global switch "do not sort, only group"? Not sure how to present it UI-wise. Feels too ad-hoc. Indeed a work-around now is (in the given example) to include the ID in the result query, not show the ID anywhere in the report, sort & group by ID, show the Category (name) in the "group by ID" header / footer / whatever.
Created attachment 64135 [details] Sorting reports in the GUI I have added some ideas where to change the GUI to get the wished behaviours. But the best way could for easy programming the sql-code could be: Put away "Analyze SQL command", make a temporary table, when "Content type" isn't "Table". Then you can add filter, sorting … It will make it easyer to enhance the report-builder for https://bugs.freedesktop.org/show_bug.cgi?id=51807
(In reply to comment #8) > Created attachment 64135 [details] > Sorting reports in the GUI > > Sorting should have three possibilities: Ascending, Descending and No. +1 Imagine following data source as corner case: Town | Name Town1 Name1 Town1 Name2 Town1 Name3 Town2 Name4 Town2 Name5 Town1 Name6 Town3 Name7 Town3 Name8 I would say, the report, unsortet grouped by Town, should then print: Town: Town1 Name: Name1 Name: Name2 Name: Name3 Town: Town2 Name: Name4 Name: Name5 Town: Town1 Name: Name6 Town: Town3 Name: Name7 Name: Name8
(In reply to comment #9) > (In reply to comment #8) > > Created attachment 64135 [details] > > Sorting reports in the GUI > > > > Sorting should have three possibilities: Ascending, Descending and No. > > +1 > > Imagine following data source as corner case: > > Town | Name > Town1 Name1 > Town1 Name2 > Town1 Name3 > Town2 Name4 > Town2 Name5 > Town1 Name6 > Town3 Name7 > Town3 Name8 > > I would say, the report, unsortet grouped by Town, should then print: > > Town: Town1 > Name: Name1 > Name: Name2 > Name: Name3 Name: Name6 When you group a table every field with the same value will be hold together. "Town1" couldn't appear in 2 groups. > Town: Town2 > Name: Name4 > Name: Name5 > Town: Town3 > Name: Name7 > Name: Name8 Town1 must not be the first, wenn it will be ordered by "Town" - but all "Name" of "Town1" must be shown in one group. That is, what SQL- GROUP BY means.
(In reply to comment #10) > When you group a table every field with the same value will be hold together. > "Town1" couldn't appear in 2 groups. This suggestion was born by the by the assumption, that the report builder would serially process the incoming records from a stream. If it would first buffer all records before grouping, yes, then it's possible, the pricessing could happen as you say. Actually to me it doesn't matter. It was just mentioned to theoretically legitimate such output from the report builder, if it doesn't buffer internally. > Town1 must not be the first, wenn it will be ordered by "Town" - but all "Name" > of "Town1" must be shown in one group. That is, what SQL- GROUP BY means. I would say "... when it will be grouped by ...", right? I was not sure, if the report builder internally processes a SQL- GROUP BY command.
Adding self to CC if not already on
Hello This is somehow related to this: https://bugs.documentfoundation.org/show_bug.cgi?id=64377 mh